Query Question

K

KS31

I am working with queries pulling from an SQL linked table. I'm pulling
orders within a given time period with the below fields. Each order line
item appears on
a separate line.

Customer Name Order Number Date Item Type New Start
Joe Smith 12345 3/3/08 Core
Y
Joe Smith 12345 3/3/08 Comp Y
Pete Smith 33224 3/4/08 Comp Y

I need to do a query to pull customers who only have Comp items only within
the time period I pull, so in this example I only want to see Pete Smith.
How can I do this without also pulling Joe Smith?
 
K

KS31

I tried that but it also pulls Joe Smith. I need to pull customers with only
Comp Item Type.

Access User KS31
 
J

Jerry Whittle

SELECT KS31.[Customer Name],
KS31.[Order Number],
KS31.Date, KS31.[Item Type],
KS31.[New Start]
FROM KS31
WHERE KS31.[Customer Name]
NOT IN (SELECT KS31.[Customer Name]
FROM KS31
WHERE KS31.[Item Type]<>"Comp");

Change all the KS31's with the real table name.
 

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