Exclude Records In A Query

[rcorbett]

This seems easy to do but I am drawing a big mental block!

This query is based on a single table. There are certain conditions that must be true - such as a certain blood types, not charged for testing, patient, donor or other. I will display the persons name who meets these criteria. The catch that is slowing me down is that if a person is both a donor and other (these are check boxes) I do not want them to appear.

Any help would be appreciated.

 

[lespaul]

You don't really give much information about your table or what you are trying to accomplish! Is donor a check box as well? Are these all logical fields in the database? Are they set to be Yes/No; True/False?

You have a form with a bunch of check boxes on it? If the user selects some boxes you want run a query that shows all the people who meet that criteria but aren't donors? What if donors is checked? Do you want to find donors then?

Is each blood type a checkbox?

such as a certain blood types

So your table structure for the blood type is set up like this:
TypeA - T/F
TypeB - T/F
TypeAB - T/F
TypeO - T/F

With a little more information about your tables, I could see about helping.

Leslie

 

[rcorbett]

These are all True/False fields.


HLA A/B - Needs to be true (criteria)
Patient - Needs to be true (criteria)
Charged - Needs to be false. (looking for those not charged yet).
Donor - this would be on the Or criteria row and needs to be true.

There is a field called Other - which is a true false field.

If Donor and Other are both checked for a person then I can't have them listed in the results.

Hope this helps.

 

[lespaul]

So if you know all your criteria, what do the check boxes have to do with anything?

based on your information

HLA A/B - Needs to be true (criteria)
Patient - Needs to be true (criteria)
Charged - Needs to be false. (looking for those not charged yet).
Donor - this would be on the Or criteria row and needs to be true.

DONOR = TRUE (last condition)

OR

HLA A/B = TRUE AND
Patient = TRUE AND
Charged = FALSE

if we use this criteria ALL donors will be returned THEN everyone who has HLA A/B AND PATIENT AND NOT CHARGED.

DONOR OTHER HLA A/B Patient Charged
T T T T F
T F F T T
F T T F F
T T F T F
F F T F T

So out of the pretend records above, which ones do you want to have? Or if these don't make any sense, please include your own.

Leslie

 

[rcorbett]

I want to include the Patients or Donors - you can't be both.

If you are a Donor and Other, then I need to exclude them from the query.

So, I want to list Patients or Donors minus Donors who also have Other checked.

Sorry If I am making this complicated...thanks for the help.

 

[lespaul]

SELECT * FROM TABLENAME WHERE PKIDFIELD NOT IN (SELECT PKIDFIELD FROM TABLENAME WHERE DONOR = TRUE AND OTHER = TRUE)

you'll need to change the TABLENAME and PKIDFIELD with your tablename and the primary key field of the table.

Does this get us closer?

les

(not making it complicated, it's sometimes hard to express clearly!)