Query Based On ID

  • Thread starter Thread starter zyus
  • Start date Start date
Z

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.
 
SELECT CustID
FROM MyTable
WHERE Product IN ("A", "B")
HAVING Count(*) = 2
 
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.
Click to expand...

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");
 
I tried but got this error msg

"you tried to execute.......does not include IDNO as part of aggregate
function"
 
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
 
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
Click to expand...

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