making a query

[Guest]

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

 

[Joseph Meehan]

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

It sounds like a poor table design is going to make it more difficult.

Assuming each "cardiac diagnosis" is nothing more than repeats of the
same thing (a diagnosis) and not part or a usual series like 1= first visit
2= after standard test 3= after a second set of standard test etc, then you
should have two related tables.

One table would be your patients. It would include things like name and
address. The second related table would be diagnosis. The second table
would list things related to any diagnoses and a patient may have none or
may have 20 or 30.

It appears you may have an issue with cardiac diagnosis vs other types
of diagnosis as well, but you did not ask about that yet.

Making related tables like this is call normalizing the table and it
makes for a faster more versatile database. It also generally makes
answering your question easier.

Do you know how your table(s) are designed?

 

[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]