easy access question

[andrew29]

Hi everyone,

I have a single table that contains customer numbers, and account types in a format simialar to below.

0001 accountA
0001 accountB
0001 accountC
0001 accountD
0002 accountB
0003 accountD
0004 accountF
0005 accountA
0005 accountB
0005 accountF

Now what I need to do is

find out how many individual customers have accountA only without having accountB and AccountC.

find out ahow many individual customers have both accountA and accountB but no accountC.

Find out how many individual customers have accountA, AccountB and AccountC.

I've added the one table 3 times and the query returns what I want except for the fact that it doesn't return unique records. It returns something like.

0001 AccountA AccountB AccountC
0001 AccountA AccountB AccountC
0001 AccountA AccountB AccountC

In this instance I want it to return just unique customer numbers.

I've added the 'total' row to the query but it doesn't work when I have a query that is select customer number, and account type where AccountA but not accountB or AccountC.

The problem seems to be with adding the not (<>) clause. I can get it to work without that.

Can anyone help?

I'm sure its easy.

 

[Marbles]

Are there too many Account types to make each type a separate field. It would be easy to make a search form that way.

Might help

 

[Norie]

Can you post the SQL for the existing query?

Are you only using 1 query?