Re: query to combine values if true

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Aug 18, 11:21 am, "Jeff Boyce" <nonse...@xxxxxxxxxxxx> wrote:
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" <mcour...@xxxxxxxxxxxxxx> wrote in message

news:u885lv5$IHA.4756@xxxxxxxxxxxxxxxxxxxxxxx

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" <nonse...@xxxxxxxxxxxx> wrote in message
news:O26sKnu$IHA.4536@xxxxxxxxxxxxxxxxxxxxxxx
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" <mcour...@xxxxxxxxxxxxxx> wrote in message
news:O6TCPIu$IHA.984@xxxxxxxxxxxxxxxxxxxxxxx
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.


"normalization" and "relational database design" are not familiar terms

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.

.

Quantcast