Query which does not show records which appear in another query

[robincross224]

Hi there,
I'm a bit of a novice when it comes to Microsoft Access, and I have a problem which has had me confused all day.



I have a database which pairs customers' records with the services they receive- there are three tables to do this:

TCustomer
TService
TServicebyCustomer

The TServicebyCustomer table has two fields, the key from TCustomer and the key from TService. Here is some Example data:

1, A
1, B
2, B
3, A
4, B
4, A

I need to create a list of people who haven't received Service A but have received Service B. The problem is with a query, If I filter records to show those with Service B it will show me those with service B but not If they have Service A or not.... for example- it would filter out customers 1, 2 and 4 but because each link is made in a different record, I would have no idea if that person had A, as it is made in a separate record.

I have made a query which shows those who have received service A, could I use the results of that query to filter out records from another Query???

I have no idea where to start, please help!!

I

[June7]

How many service codes are possible?

Create a crosstab query with the Query Wizard. The TServicebyCustomer table will need a unique ID field, an Autonumber will serve.
TRANSFORM Count([ID]) AS CountOfID
SELECT [Customer], Count([ID]) AS [Total Of ID]
FROM TServicebyCustomer
WHERE [Service])="A" Or [Service]="B"
GROUP BY [Customer]
PIVOT [Service];

This will create columns for Service codes. Now query the crosstab for records that don't have count in Service A but do have Service B.