query to combine values if true

  • Thread starter Thread starter mcnewsxp
  • Start date Start date
M

mcnewsxp

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
 
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:

Race: IIf([Green],"G",Null) & IIf([Brown],"B",Null) & IIf([Purple],"P",Null)

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 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
 
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.
 
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
 
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

those terms are very familiar to me. i've been working with RDBs for
over 15 years. Mainframe, AS400, MS SQL, MySQL, Oracle etc. i am
familiar with the 8 normal forms of normalization - i am not bound by
them, however. even tho i have been trained to do things the *right*
way i have found that *right* is always the most practical way.
the main thing to realize when creating data entry forms is that many
times the data entry employees are short term hires who must be
trained very briefly. the forms need to be designed for 100% hands on
operation and should avoid any unneeded mouse clicks.
the data collected in my case will be analyzed by epidemiologists who
as i said insist that their world is flat. they like to run their own
queries and don't want to look for numbers that link tables to their
data. they want their data.
so depending on the amount of data to be collected - i let them have
their way or not....

when i first had this app dumped on me i was asked to fix a few bugs.
after doing that i mentioned to the POC that it was designed for
crap. i split the data from the client mdb and normalized the
backend. then after futzing with the forms i de-normalized much of it
to facilitate that way all of the actors will be using it.
after thinking about it some moere, tho - i may need to rework the
race bit in my app, however.
 
Sounds like you have the experience to handle this, so...

What if you keep your data tables in a well-normalized form (to help Access
better work its magic), then provide the users a query that "flattens" the
data? That way, you can give them a form and a way to query and they'd be
none the wiser how the data's stored.

I'm with you, if the users can't use it, it's useless!

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top