making a query

C

cubgirl77

ok this is my problem -

i work at a doctors office and we have a database of about 1000 patients in
the practice. we have fields called "cardiac diagnosis 1" , "cardiac
diagnosis 2", and "cardiac diagnosis 3". we want to search in these 3 fields
for people who do not have "hyperlipidemia" "CAD" and several other diagnoses
that are values for those fields. how do i make a query like that? thanks a
jillion
 
J

John Vinson

ok this is my problem -

i work at a doctors office and we have a database of about 1000 patients in
the practice. we have fields called "cardiac diagnosis 1" , "cardiac
diagnosis 2", and "cardiac diagnosis 3".

Then you have an incorrectly normalized database. Repeating fields
like this are ALWAYS a bad idea.

A better structure would have *three* tables: Patients, with a
PatientID as a primary key (names will *not* work, they're not
unique); Diagnoses, with a DiagnosisID such as a standard insurance
diagnosis code, and a diagnosis text ("ventricular arrythmia" or
whatever); and a third table resolving the many to many relationship,
with fields for the PatientID, DiagnosisID, and perhaps additional
fields concerning this diagnosis for this patient (e.g. severity, date
of onset, etc.)
we want to search in these 3 fields
for people who do not have "hyperlipidemia" "CAD" and several other diagnoses
that are values for those fields. how do i make a query like that? thanks a
jillion

If you're stuck with this table structure, you can use a criterion of

NOT IN ("hyperlipidemia", "CAD", "atherosclerosis")

on the Criteria line under *each* of the fields, all on the same row
of the criteria grid.

In the normalized table structure, you'ld need a NOT EXISTS clause
instead but you can easily get the same result.

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top