Query Based On ID

[zyus]

My table consist of the following fields & records

CustID Product Amount
001 A 100.00
003 B 50.00
001 B 150.00
005 A 111.00

I want to know customer who is having product A and B. In this example cust
001 will match the criteria.

 

[Douglas J. Steele]

SELECT CustID
FROM MyTable
WHERE Product IN ("A", "B")
HAVING Count(*) = 2

 

[John W. Vinson]

My table consist of the following fields & records

CustID Product Amount
001 A 100.00
003 B 50.00
001 B 150.00
005 A 111.00

I want to know customer who is having product A and B. In this example cust
001 will match the criteria.

If the same customer can have multiple records for Product A, Doug's otherwise
excellent query will not work. It may be inefficient but this should be
robust:

SELECT CustID FROM table
WHERE EXISTS(SELECT CustID FROM table AS A WHERE A.CustID = table.CustID AND
A.Product = "A")
AND EXISTS (SELECT CustID FROM table AS B WHERE A.CustID = table.CustID AND
B.Product = "B");

 

[zyus]

I tried but got this error msg

"you tried to execute.......does not include IDNO as part of aggregate
function"

 

[zyus]

Thanks John..It work great...

Just to add one more Q. What if i want to know which customer who has more
than one of product A..Tried to use your earlier query but failed.

Thanks

 

[John W. Vinson]

Thanks John..It work great...

Just to add one more Q. What if i want to know which customer who has more
than one of product A..Tried to use your earlier query but failed.

Thanks

Just do a Totals query, with a criterion of "A" on Product; Group By CustID,
count records, and use a criterion of >= 2.