Duplicate Customers in Query

[stevenb82]

I have a table of customers, and a table of products. I also have a table of orders. Each time a customer orders a product the details of the customer and the order are added to the orders table. Pretty standard.

I have a query that shows customers who bought product 1 and 2, but none of the other products I have on sale, or that other customers have bought (just those who have bought product 1 and 2).

But, with the way I have the tables set up, each order for both of these products shows up in the query, which means if CustomerA bought product 1 five times then the customer shows up the query five times..

I want to run a promotion, where any customer who has bough both product 1 and 2 gets mailed a coupon, so I need the query to list the customers who bought both of these items (but list them only once!) so that I can mail merge a coupon and post it to them..

So far I have tried in the criteria for products in the query I have set up.. 1 Or 2.. but this displays customers who have bought either of the products but not both.. and I have tried 1 And 2, but this returns nothing.

Any thoughts? Hope this makes sense..

Thanks in advance

 

[recyan]

Till some one comes along, just check out if below gives some guidelines :

http://www.accessforums.net/queries/query-2-objects-25885.html

Thanks

 

[PeterF]

You need to link your order table twice and put in the critera you need, someting like:

SELECT tblCustomer.Name, tblCustomer.Adres, tblOrder.Item, tblOrder_1.Item
FROM (tblCustomer INNER JOIN tblOrder ON tblCustomer.CustomerId = tblOrder.CustomerId) INNER JOIN tblOrder AS tblOrder_1 ON tblCustomer.CustomerId = tblOrder_1.CustomerId
WHERE (((tblOrder.Item)<>[tblOrder_1].[item] And (tblOrder.Item)= 1) AND ((tblOrder_1.Item)= 2));

 

[stevenb82]

I'll give those things a try now lads, thanks for that!

 

[stevenb82]

You need to link your order table twice and put in the critera you need, someting like:

SELECT tblCustomer.Name, tblCustomer.Adres, tblOrder.Item, tblOrder_1.Item
FROM (tblCustomer INNER JOIN tblOrder ON tblCustomer.CustomerId = tblOrder.CustomerId) INNER JOIN tblOrder AS tblOrder_1 ON tblCustomer.CustomerId = tblOrder_1.CustomerId
WHERE (((tblOrder.Item)<>[tblOrder_1].[item] And (tblOrder.Item)= 1) AND ((tblOrder_1.Item)= 2));

So in this code you have essentially create a new table that duplicates the info of tblOrder and tblOrder_1?

 

[stevenb82]

So, I couldnt get either of those suggestions to work properly for me, no doubt they were both excellent suggestions, so thank you very much for that..

Heres where I am going.

I have created two queries, one that shows me customers who have purchased product 1, and one that shows me those who have purchased product 2. What I want now is to create a query that shows me the customers who show up in both of those queries..

Any ideas? I have tried a select statement that pseudo reads as;

select queryProd1.custID, queryProd2.custID
from thequeries
where queryProd1.custID = queryProd2.custID;

does that make sense?

 

[stevenb82]

Sorted

http://office.microsoft.com/en-au/a...-and-find-only-matching-data-HA010205130.aspx