Simple Query Idiot User!!!

[Dano]

You guys are probably gonna laugh me out of this forum, but I need help! I haven't used Access in over 5 years and I used to think I was pretty savy, but no more. Anyway, here's the scenario.

I have a CUST table and an INVENTORY table

They both contain CUSTID fields. The CUSTID in the CUST table is unique obviously, but in the INVENTORY table the CUSTID can be used more than once (basically one for each Item the cust bought).

I want to do a query that will pull all INVENTORY->CODE for people that own items A or B (or A & B) but I want to exclude anyone that owns Item C. I used INVENTORY->CODE="A" And Not INVENTORY->CODE="C," for just the A customers which eliminated all the C's, but Some of the remaining CUSTIDs that showed the cust owned product A were found to own C's too? I want to eliminate anyone that owns a C. I didn't want any A's to come up if they owned a C as well.

I linked the CUST table to the INVENTORY table by CUSTID, so I don't know what I did wrong? Any help would be greatly appreciated.

Dan

 

[Pat Hartman]

You need to break this into smaller queries.

Query1 - select all the customers with "a" or "b" products:
Select ...
From ...
Where YourCode = "A" Or "B";

query2 - select all the customers with "c" products:
Select ...
From ...
Where YourCode = "C";

query3 - select rows where query1 has no match in query2:
Select query1.fld1, query1.fld2 ...
From query1 Left Join query2 ON query1.CustID = query2.CustID
Where query2.CustID Is Null;

 

[Dano]

Hmmm.

O.K. I get the smaller queries idea, but I'm lost at query three. I understand joining them at the CUSTID, but as far as the criteria that's where I get lost do I use the query builder? What would be the commands that mean that I want rows from Query 1 excluding any CUSTIDs that match query 2?

Thanks so much for your help Pat!

 

[Dano]

Nevermind

I got it...I was just confused by the way it was worded. I do feel even more stupid now. That was too easy. I guess I was trying to make things a little more complicated than they needed to be.

Thanks again Pat!