(Msg. 1) Posted: Fri Aug 15, 2008 10:20 am
Post subject: query to combine values if true Archived from groups: microsoft>public>access>gettingstarted (more info?)
i have a table that contains yes/no fields for race. in my query i what
only one column for race. an inividual may be multi-racial. so i need a
query to combine all of the yes/no field checked true. i will come up with
some abbreviations so the column need not be rediculously wide.
can someone point me in the right direction for the query?
BTW - changing the table structure in not an option.
tia,
mcnewsxp
(Msg. 2) Posted: Fri Aug 15, 2008 10:20 am
Post subject: RE: query to combine values if true [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Too bad you can't change an incorrectly designed data structure where there
should be a race table that would be a child table to the individual table,
but since you can't, use a calculated feild in your query:
Using
Iif([Green]
is the name as using
Iif([Green] = True
That is because Iif and If both are looking for a Boolean condition. If you
test a Yes/No (Boolean) data type it will return only True or False, so using
= True is redundant. And just to be complete, that means
Iif(Not [Green]
is the same as
Iif([Green] = False
--
Dave Hargis, Microsoft Access MVP
"mcnewsxp" wrote:
> i have a table that contains yes/no fields for race. in my query i what
> only one column for race. an inividual may be multi-racial. so i need a
> query to combine all of the yes/no field checked true. i will come up with
> some abbreviations so the column need not be rediculously wide.
> can someone point me in the right direction for the query?
> BTW - changing the table structure in not an option.
> tia,
> mcnewsxp
>
>
>
(Msg. 3) Posted: Fri Aug 15, 2008 10:20 am
Post subject: Re: query to combine values if true [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Dave has provided a way to work with what you've described.
Would you care to elaborate on why the table structure is immutable? Folks
may be able to offer alternate approaches if they understood better the
constraints you are facing...
Regards
Jeff Boyce
Microsoft Office/Access MVP
"mcnewsxp" <mcourter.TakeThisOut@mindspring.com> wrote in message
news:O6TCPIu$IHA.984@TK2MSFTNGP06.phx.gbl...
>i have a table that contains yes/no fields for race. in my query i what
>only one column for race. an inividual may be multi-racial. so i need a
>query to combine all of the yes/no field checked true. i will come up with
>some abbreviations so the column need not be rediculously wide.
> can someone point me in the right direction for the query?
> BTW - changing the table structure in not an option.
> tia,
> mcnewsxp
>
(Msg. 4) Posted: Sat Aug 16, 2008 8:30 am
Post subject: Re: query to combine values if true [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
the query i need is just for one instance of display.
the table is designed to match exactly a data entry form.
once the data is collected it is analyzed by epidemiologists who use SAS.
they insist upon flattened data.
i am open to suggestions of a better design.
once again - i inherited the existing backend, but i have leeway to makes
things better if the end product is not hampered.
"Jeff Boyce" <nonsense.TakeThisOut@nonsense.com> wrote in message
news:O26sKnu$IHA.4536@TK2MSFTNGP05.phx.gbl...
> Dave has provided a way to work with what you've described.
>
> Would you care to elaborate on why the table structure is immutable?
> Folks may be able to offer alternate approaches if they understood better
> the constraints you are facing...
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "mcnewsxp" <mcourter.TakeThisOut@mindspring.com> wrote in message
> news:O6TCPIu$IHA.984@TK2MSFTNGP06.phx.gbl...
>>i have a table that contains yes/no fields for race. in my query i what
>>only one column for race. an inividual may be multi-racial. so i need a
>>query to combine all of the yes/no field checked true. i will come up
>>with some abbreviations so the column need not be rediculously wide.
>> can someone point me in the right direction for the query?
>> BTW - changing the table structure in not an option.
>> tia,
>> mcnewsxp
>>
>
>
(Msg. 5) Posted: Mon Aug 18, 2008 8:21 am
Post subject: Re: query to combine values if true [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I've not run across many "data entry forms" or spreadsheets that are
well-normalized. If you want to get the best use of Access'
relationally-oriented features/functions, you need to provide data in the
structure it expects. Even though the folks are entering the data via a
form, you are not limited to that structure.
If "normalization" and "relational database design" are not familiar terms,
you'll need to spend some time brushing up on them before taking your
database/application to the next level...
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
"mcnewsxp" <mcourter.DeleteThis@mindspring.com> wrote in message
news:u885lv5$IHA.4756@TK2MSFTNGP03.phx.gbl...
> the query i need is just for one instance of display.
> the table is designed to match exactly a data entry form.
> once the data is collected it is analyzed by epidemiologists who use SAS.
> they insist upon flattened data.
> i am open to suggestions of a better design.
> once again - i inherited the existing backend, but i have leeway to makes
> things better if the end product is not hampered.
>
>
> "Jeff Boyce" <nonsense.DeleteThis@nonsense.com> wrote in message
> news:O26sKnu$IHA.4536@TK2MSFTNGP05.phx.gbl...
>> Dave has provided a way to work with what you've described.
>>
>> Would you care to elaborate on why the table structure is immutable?
>> Folks may be able to offer alternate approaches if they understood better
>> the constraints you are facing...
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "mcnewsxp" <mcourter.DeleteThis@mindspring.com> wrote in message
>> news:O6TCPIu$IHA.984@TK2MSFTNGP06.phx.gbl...
>>>i have a table that contains yes/no fields for race. in my query i what
>>>only one column for race. an inividual may be multi-racial. so i need a
>>>query to combine all of the yes/no field checked true. i will come up
>>>with some abbreviations so the column need not be rediculously wide.
>>> can someone point me in the right direction for the query?
>>> BTW - changing the table structure in not an option.
>>> tia,
>>> mcnewsxp
>>>
>>
>>
>
>
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office Other