identifying records with two or more specfic attributes

[gorgon777]

Hello,

I have two tables as below:

Customer
CID non-unique
Name
Shape

Purchases
PID
Item
CID Foreign Key

I would like to know how i can produce a query that can show all customers who do not have a specific Item related to it. How can I achieve this in Access?

For Example say I had the below data:

Customer
1 John Triangle
2 Peter Square
3 Kim Circle

Purchases
1 Mouse 1
2 Choc 1
3 Pin 2
4 Tub 1
5 Glass 3
6 Glass1

I want the query to show the following people who havent purchased "Glass":

Query(CID, Name, Item)
2 Peter

...as this person did not but Glass according to the Purchase Table.
With Thanks
Gorgon

 

[vbaInet]

You can start of using a DCount() function. Then we can discuss the faster more complex solutions.

http://www.techonthenet.com/access/functions/domain/dcount.php

 

[plog]

You want an aggregate query: http://www.599cd.com/tips/access/aggregate-query/

How come CID is non-unique in Customer table?

 

[vbaInet]

I would have thought the OP wanted an editable recordset hence the DCount() but I could be wrong.

 

[gorgon777]

You can start of using a DCount() function. Then we can discuss the faster more complex solutions.

I have tried a DCount, but it doesnt seem to count the number of unique attributes related to the Customer. It is counting how many there are in total. So in the example, only Peter should remain as he didnt buy Glass.

Similarly, I have tried aggregated queries, but it has the same effect as above.

 

[vbaInet]

Aggregate queries like plog suggested works. Show us the SQL statement of the query.

DCount() also works. You just need to use the third argument of the function to tie it to the current record. Let's see what you wrote.

 

[gorgon777]

PurchasesPIDItemCID1Glass12Bear13Mouse24Cream35Cola16Cheese477Up48Cheese1
CustomerCIDNameShape1JohnSquare2PeterCircle3KimTriangle4GozLine
Above are my two tables:

Sorry about this, but the requirement has changed slightly. I now would like to only show those people who havent bought "Cheese". So thats 'Peter' and 'Kim'.
Below is the code i have, but this shows all records that arent with "Cheese". I just want the Customers themselves who havent bought it.

SELECT Customer.CID, Customer.Name, Purchases.Item
FROM Customer LEFT JOIN Purchases ON Customer.CID = Purchases.CID
GROUP BY Customer.CID, Customer.Name, Purchases.Item
HAVING (((Purchases.Item) Not Like "Cheese"));

Thank you for your help.

 

[gorgon777]

Apologies, but my requirement has changed slightly.

I now would like to show only people who have purchased "Cheese". My tables are as below:
Customer
1 John Square
2 Peter Triangle
3 Kim Circle
4 Goz Line

Purchases
1 Hair 1
2 Pin 1
3 Card 2
4 Cheese 1
5 Choc 3
6 Cheese 3
7 Mouse 2

So I would expect just 'Peter' and his customer record to show up in the query. I have the code below so far, which is showing ALL customer records that arent related to "Cheese"

SELECT Customer.CID, Customer.Name, Purchases.Item
FROM Customer LEFT JOIN Purchases ON Customer.CID = Purchases.CID
GROUP BY Customer.CID, Customer.Name, Purchases.Item
HAVING (((Purchases.Item) Not Like "Cheese"));

Thank you and appreciate your help.

 

[plog]

You've redefined what you want, so its not as simple as an aggregate query, and not really simple at all. Using just the 2 tables you have provided this is how you would build a query to find out all the items not purchased by customers.

First create a sub-query called 'CustomersItems':

SELECT Purchases.Item, Customer.CID
FROM Purchases, Customer
GROUP BY Purchases.Item, Customer.CID;

This is called a Cartesian Product (http://en.wikipedia.org/wiki/Cartesian_product) which means you are using at least 2 data sources in a query, but not linking them. This query will give you every item in your database and associate it with every CID in your database.

Using that query we can now find out which associations (Item-CID) have been purchased and exclude them leaving just the ones that don't appear in your database:

 SELECT Customer.CID, Customer.Name, CustomersItems.Item
FROM (CustomersItems LEFT JOIN Purchases ON (CustomersItems.CID = Purchases.CID) AND (CustomersItems.Item = Purchases.Item)) INNER JOIN Customer ON CustomersItems.CID = Customer.CID
WHERE (((IIf(IsNull([Purchases].[Item]) And IsNull([Purchases].[CID]),1,0))=1));

When you run that query you will be left with all the Customers and the items that they did not purchase. The problem with Cartesian products is that with a ton of records in their underlying sources they can take awhile to run. The best part of all of this is that if you want to just run this quickly once, then I would go with Vba's initial suggestion of a DCount.

 

[gorgon777]

You've redefined what you want, so its not as simple as an aggregate query, and not really simple at all. Using just the 2 tables you have provided this is how you would build a query to find out all the items not purchased by customers.

First create a sub-query called 'CustomersItems':

SELECT Purchases.Item, Customer.CID
FROM Purchases, Customer
GROUP BY Purchases.Item, Customer.CID;

This is called a Cartesian Product (http://en.wikipedia.org/wiki/Cartesian_product) which means you are using at least 2 data sources in a query, but not linking them. This query will give you every item in your database and associate it with every CID in your database.

Using that query we can now find out which associations (Item-CID) have been purchased and exclude them leaving just the ones that don't appear in your database:

 SELECT Customer.CID, Customer.Name, CustomersItems.Item
FROM (CustomersItems LEFT JOIN Purchases ON (CustomersItems.CID = Purchases.CID) AND (CustomersItems.Item = Purchases.Item)) INNER JOIN Customer ON CustomersItems.CID = Customer.CID
WHERE (((IIf(IsNull([Purchases].[Item]) And IsNull([Purchases].[CID]),1,0))=1));

When you run that query you will be left with all the Customers and the items that they did not purchase. The problem with Cartesian products is that with a ton of records in their underlying sources they can take awhile to run. The best part of all of this is that if you want to just run this quickly once, then I would go with Vba's initial suggestion of a DCount.

Thank you, this solution worked perfectly. What I required was the following part of the query:

IIf(IsNull([Purchases].[Item]) And IsNull([Purchases].[CID]),1,0))=1

This allowed me to produce the records i wanted to filter out from my original table.