Unmatched Query

[stipica]

Hello and please help
I have two tables: TYPES and NAME_TYPE.

 TYPES:
type
A
B
C
D
E
 
 NAME_TYPE
name     surname      type
John     Doe     C
John     Doe     B
Jack     Daniels     D
Will     Smith     C
Brad     Pitt     A
George     Dumb     E
 

and I want to display all types that don't belong to some person.
For example if person is George Dumb, i want query return result:
A
B
C
D
or if person is John Doe, query result need to be:
A
D
E

How should i do that ? What is the right query?

[cjbrx3115]

Hello and please help
I have two tables: TYPES and NAME_TYPE.

 TYPES:
type
A
B
C
D
E
 
 NAME_TYPE
name     surname      type
John     Doe     C
John     Doe     B
Jack     Daniels     D
Will     Smith     C
Brad     Pitt     A
George     Dumb     E
 

and I want to display all types that don't belong to some person.
For example if person is George Dumb, i want query return result:
A
B
C
D
or if person is John Doe, query result need to be:
A
D
E

How should i do that ? What is the right query?

Hmm...I'm not a real "Access Expert", but that would probably have to do with Relationships.

[nico5038]

This can be solved using the IN() predicate in the WHERE clause.
Inside the IN() you can use a subquery, so in your case try:
select ... from tblX a
WHERE Not a.Type IN(select Type from tblX b where b.Name=a.Name)

The a and b are qualifiers for the tables to keep the fields "unique" for Access.

Getting the idea ?

Nic;o)

[NeoPa]

The "Find Unmatched Query Wizard" will create something for you. It works by using a LEFT JOIN and including Is Null on the linked to field in the WHERE clause.

[stipica]

I try with IN operator. This is right query:

SELECT type from TYPES WHERE type not in
(SELECT type FROM name_type WHERE name="John")

thank you.

[nico5038]

Looks OK to me, but the nice thing of queries is the fact that you can run them to prove they're right :-)

Nic;o)

[NeoPa]

I try with IN operator. This is right query:

SELECT type from TYPES WHERE type not in
(SELECT type FROM name_type WHERE name="John")

thank you.

You should probably include the surname check too :

SELECT [Type]
FROM [TYPES]
WHERE [Type] NOT In
(SELECT [Type]
 FROM Name_Type
 WHERE [Name]='John' AND [Surname]='Doe')