Unique query problem

[sougata666]

Suppose there are two tables - customer and product. Also available is customerproduct (linked table containing customers and the products they bought).i want to design a query which will list products NOT bought by a customer i.e. each customer will be listed with products not bought by him.
Example
Customers
Tom
Jim
Harry

Product
Shampoo
Brush
Shoe
Box

Customerproduct
Jim box
Jim brush
Tom brush
Harry shampoo

So, my query should display:
Jim shampoo
Jim shoe
Tom shampoo
Tom shoe
...

 

[JHB]

Use an Unmatch query.
Create a query that select all product a customer can buy, (for each customer), use the query wizard to create an unmatch query against what each customer actually have bought.

 

[sougata666]

Ok.
Query designed as follows:

SELECT  Table1.Customer, Table2.Product, Table3.ID
FROM Table1,Table2,Table3;

table3 has two fields: customer and product (both are lookup fields)


Now, if I select this query and the linked table (table3) in the Unmatched Query wizard (with the link being the ID field) I get a blank result.

SQL of the unmatched query is (generated by the wizard):

SELECT Table3.Customer, Table3.Product
FROM Table3 LEFT JOIN Query1 ON Table3.[ID] = Query1.[ID]
WHERE (((Query1.ID) Is Null));

 

[JHB]

Could you post your database with some sample data, (zip it)?

 

[sougata666]

The file has been uploaded.

 

[moke123]

I would recommend you get rid of the lookup fields in your tables before trying to tackle this problem.

 

[sougata666]

That's not possible. This is just a sample db to simulate the problem. My actual db is larger and more complex. I can't do away with lookup fields where new names, products can be added. Involves a proper front end with forms & vb codes.

 

[JHB]

Sorry but I agree with moke123.
I you've had a table like below, it would be easy to find with product a customer hasn't bought.
With the lookup fields you've to find a way, in which you get the name of the customer and the name of the product.

The query:

SELECT Query1.Customer, Query1.Product
FROM CustomerAndProduct RIGHT JOIN Query1 ON (CustomerAndProduct.Product = Query1.Product) AND (CustomerAndProduct.[Customer] = Query1.[Customer])
WHERE (((CustomerAndProduct.Product) Is Null))
ORDER BY Query1.Customer DESC;

 

[sougata666]

So I have to feed that Table (CustomerandProduct) manually? Kind of makes Access useless for me that way.
Surely there must be a way out of this. Some advanced query perhaps?
This is like the Access equivalent of brute force.
Thank you for your answers though.
How about using NOT EXISTS, NOT IN or LEFT JOIN to figure out a way in SQL? Obviously query SQL has to be programmed.
Any suggestions?

 

[JHB]

So I have to feed that Table (CustomerandProduct) manually?..

No not exactly - it was only created to show how it could be done if you not have used lookup fields.
As I mention, you've to find a way, in which you get the name of the customer by the ID, and the name of the product by the ID.
One method could be the query below:

SELECT Table1.Customer, Table2.Product
FROM Table2 INNER JOIN (Table1 INNER JOIN Table3 ON Table1.ID = Table3.Customer) ON Table2.ID = Table3.Product;