Query Calculation

[matthewR]

Not sure this can be done. I am trying to find what group numbers do not have drug. I have a group number field. There is a Product Type Code and a Component type Code. 800 is the product Type Code and 800 is the component type code for drug. If a group doesn't have a product type code of 800 or a component type code of 800 then it doesn't have drug. A group can have more than one Component Type code. I just can't say <> 800. The 800 can't be in either field for it not to have drug. The component type code for a group can have say 1500 and 800. If so it have drug. If it just had 1500 component type code and the Product type code didn't say 800 then it wouldn't have drug. How would I set this up in a query?

[HansV]

Yes, it is possible, but I'd like to have a bit more information.

What is the name of the table?
Does the table have a primary key field, and if so what is its name?

[HansV]

By the way, is this an Access question or an Excel question?

[matthewR]

It is an Access question - too early and I am not fully awake. I figured out what to do. I made a table of everything that had a 800 for Product Type Code or a Component type code of 800. I removed the 800s and made a table of everything. I brought both tables into another query and did an outer join on the drug table. I brought the group number from the drug table into the query and gave it a criteria of null - removing those from the all table. What was left was the groups without drug.

[HansV]

OK - I'll move this thread from the Excel forum to the Access forum.