Search by product & alsoshow other products purchased by same cust

[sebruce]

Asking for help!! I searched all day yesterday in the forums with no luck.

I am trying to create a query to display customers that purchased a
particular product. This is the easy part since I put in the Criteria [Enter
Product #] .

I also want the result to include the other products purchased by the same
customer. The easiest subfield to search by would be the Customer # or Tax
ID#.

My Table is setup as follows:

Customer # Product # Tax ID# Customer Name
001 100001 111-11-1111 Jose Smith
001 100002 111-11-1111 Jose Smith
001 100005 111-11-1111 Jose Smith
002 100001 222-22-2222 Sue Jones
002 100003 222-22-2222 Sue Jones
003 100004 333-33-3333 Don Miller
003 100005 333-33-3333 Don Miller
004 100001 444-44-4444 Linda Holly
005 100002 555-55-5555 Rich Fields
005 100003 555-55-5555 Rich Fields
006 100004 666-66-6666 Bella Meade

Example:

It I search Product # 100001, I want the results to be following information:

001 100001 111-11-1111 Jose Smith
001 100002 111-11-1111 Jose Smith
001 100005 111-11-1111 Jose Smith
002 100001 222-22-2222 Sue Jones
002 100003 222-22-2222 Sue Jones
004 100001 444-44-4444 Linda Holly



Any information received will be greatly appreciated!!

 

[KARL DEWEY]

Try this --
qryProdCust --
SELECT [Customer #], [Product #], [Tax ID#], [Customer Name]
FROM YourTable
WHERE [Product #] = [Enter Product #];

SELECT [Customer #], [Product #], [Tax ID#], [Customer Name]
FROM YourTable, qryProdCust
WHERE [YourTable].[Customer #] = [qryProdCust ].[Customer #]
ORDER BY [YourTable].[Customer #], [YourTable].[Product #];


OR this ---
SELECT [Customer #], [Product #], [Tax ID#], [Customer Name]
FROM YourTable
WHERE [YourTable].[Customer #] IN (SELECT [XX].Customer #] FROM YourTable AS
[XX] WHERE [XX].[Product #] = [Enter Product #])
ORDER BY [YourTable].[Customer #], [YourTable].[Product #];

 

[sebruce]

Worked Great! Thanks for your help!!

--
EB

Try this --
qryProdCust --
SELECT [Customer #], [Product #], [Tax ID#], [Customer Name]
FROM YourTable
WHERE [Product #] = [Enter Product #];

SELECT [Customer #], [Product #], [Tax ID#], [Customer Name]
FROM YourTable, qryProdCust
WHERE [YourTable].[Customer #] = [qryProdCust ].[Customer #]
ORDER BY [YourTable].[Customer #], [YourTable].[Product #];


OR this ---
SELECT [Customer #], [Product #], [Tax ID#], [Customer Name]
FROM YourTable
WHERE [YourTable].[Customer #] IN (SELECT [XX].Customer #] FROM YourTable AS
[XX] WHERE [XX].[Product #] = [Enter Product #])
ORDER BY [YourTable].[Customer #], [YourTable].[Product #];

--
Build a little, test a little.

Asking for help!! I searched all day yesterday in the forums with no luck.

I am trying to create a query to display customers that purchased a
particular product. This is the easy part since I put in the Criteria [Enter
Product #] .

I also want the result to include the other products purchased by the same
customer. The easiest subfield to search by would be the Customer # or Tax
ID#.

My Table is setup as follows:

Customer # Product # Tax ID# Customer Name
001 100001 111-11-1111 Jose Smith
001 100002 111-11-1111 Jose Smith
001 100005 111-11-1111 Jose Smith
002 100001 222-22-2222 Sue Jones
002 100003 222-22-2222 Sue Jones
003 100004 333-33-3333 Don Miller
003 100005 333-33-3333 Don Miller
004 100001 444-44-4444 Linda Holly
005 100002 555-55-5555 Rich Fields
005 100003 555-55-5555 Rich Fields
006 100004 666-66-6666 Bella Meade

Example:

It I search Product # 100001, I want the results to be following information:

001 100001 111-11-1111 Jose Smith
001 100002 111-11-1111 Jose Smith
001 100005 111-11-1111 Jose Smith
002 100001 222-22-2222 Sue Jones
002 100003 222-22-2222 Sue Jones
004 100001 444-44-4444 Linda Holly



Any information received will be greatly appreciated!!