Non matching records with many to many relationship

[szhjcn]

Access Ver 2003 and Windows XP.

I have one table with 3 columns:

CustomerNo
Type
Name

The type can have 6 different values. One of them let's call it Z needs to be used check it exists in one of the other Types for that customer number.

What I have done is to create 2 queries, to split the records. One where the Type is Z the other where the type is not Z.

I have then created a query with and inner join on CustomerNo which calls the 2 queries (which are basically views I guess).

This is where I am stuck. Because for a given CustomerNo with Type Z, there can be 2 names.

My results come back with multiple mis-matches most of which are pairs that actually match the result next to it.

i.e.

CustNo Type Name CustNo Type Name
1 Z N1 1 X N2
1 Z N2 1 Y N1

Cheers in advance

Mr Rusty SQL :)
Any ideas on this? Does it make sense what I am trying to do?

[ChipR]

What exactly do you want to see from your query? There must be a simpler way to get the results you want than to create 2 queries to divide the records, then just join them back together.

[szhjcn]

Hi,

What I want to be able to see is for each account number there is one or more type Z's Each type has a name associated with it.

I need to check if the names in against the type Z's exist in the other types for that account, if not list the account.

There are 5 other possible types.

As I said my SQL is rusty and probably was never upto expert level.

Thanks again for your offer to help.

[szhjcn]

I guess for me the issue is that there can be 2 entries/names with type Z for a given account.

[ChipR]

Maybe something like:
SELECT AccountNumber, Name FROM MyTable as Table1 WHERE Type = 'Z' AND NOT EXISTS (SELECT Name, AccountNumber FROM MyTable as Table2 WHERE Table1.AccountNumber = Table2.AccountNumber AND Table1.Name = Table2.Name AND Table2.Type <> 'Z')

That would give you the name and account number from Z type accounts where there is no record with the same account number and name but a type other than Z.

If this isn't quite what you want, let me know exactly what the fields in your table are and we'll come up with something.