If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Search by product & alsoshow other products purchased by same cust
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!! -- EB |
#2
|
|||
|
|||
Search by product & alsoshow other products purchased by same cust
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. "sebruce" wrote: 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!! -- EB |
#3
|
|||
|
|||
Search by product & alsoshow other products purchased by same
Worked Great! Thanks for your help!!
-- EB "KARL DEWEY" wrote: 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. "sebruce" wrote: 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!! -- EB |
Thread Tools | |
Display Modes | |
|
|