Query Question

[Edward]

Hi Everybody,
To explaian my question i need to give a simple example : I have a query
with three fields first name,last name and code
in code field we can have 4 different codes (A,B,C,D)
in the final query I might have a specific person (e.g. John Brown ) with
multiple entries with different codes
John Brown D
...
John Brown B
....
John Brown C
....
John Brown B
....
John Brown B

my goal is to show all the people who dont have code "A" in any of the
records. In my example John Brown wouldd be a qqualified person because he
doesnt have code "A" in any of the records. I just need to show his name once
not multiple times.
so my desired out put should be
JOhn Brown <--- just once

As a secodn question lets assume John brown has code "A" in one of the
records , how should I write the query to not show his name in my output ?
Thanks,

 

[Dorian]

I'd select all the people who do have an 'A' (SELECT DISTINCT[Name] WHERE

 = 'A') and then exclude those people (SELECT [Name] WHERE [Name] NOT
IN (query1)).
I'm sure there are other ways to do it.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[John Spencer]

A query like the following should do what you want.
SELECT DISTINCT [First Name], [Last Name]
FROM [SomeTable]
WHERE [First Name] & "/" & [Last Name] NOT IN
([First Name] & "/" & [Last Name]
FROM [SomeTable]
WHERE Code = "A")

If that is too slow, then post back for alternatives.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Edward]

I used John's suggestion
SELECT DISTINCT [First_Name], [Last_Name]
FROM [NeedsIntro-NotAttended]
WHERE [First_Name] & "/" & [Last_Name] NOT IN ([First_Name] & "/" &
[Last_Name] FROM [NeedsIntro-NotAttended] WHERE [Attend_Code]="A");
and it gives me an error message
Syntax error missing operator.
Any thoughts?
--
Best regards,
Edward

A query like the following should do what you want.
SELECT DISTINCT [First Name], [Last Name]
FROM [SomeTable]
WHERE [First Name] & "/" & [Last Name] NOT IN
([First Name] & "/" & [Last Name]
FROM [SomeTable]
WHERE Code = "A")

If that is too slow, then post back for alternatives.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi Everybody,
To explaian my question i need to give a simple example : I have a query
with three fields first name,last name and code
in code field we can have 4 different codes (A,B,C,D)
in the final query I might have a specific person (e.g. John Brown ) with
multiple entries with different codes
John Brown D
..
John Brown B
...
John Brown C
...
John Brown B
...
John Brown B

my goal is to show all the people who dont have code "A" in any of the
records. In my example John Brown wouldd be a qqualified person because he
doesnt have code "A" in any of the records. I just need to show his name once
not multiple times.
so my desired out put should be
JOhn Brown <--- just once

As a secodn question lets assume John brown has code "A" in one of the
records , how should I write the query to not show his name in my output ?
Thanks,

 

[John Spencer]

My error. Left out the SELECT in the subquery.

I do apologize.

SELECT DISTINCT [First_Name], [Last_Name]
FROM [NeedsIntro-NotAttended]
WHERE [First_Name] & "/" & [Last_Name] NOT IN
(SELECT [First_Name] & "/" & [Last_Name]
FROM [NeedsIntro-NotAttended] WHERE [Attend_Code]="A");

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================