Query for (set A) MINUS (set B)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a single table that list all the years (2001, 2002, 2003) that a
person (Susan, Paul, Sharon) has been a member of my club. (Each record in
the table is unique and each row would have at least 2 attributes (name,
year); e.g.,
Paul 2001;
Paul 2002;
Susan 2001;
Susan 2002;
Susan 2003;
Sharon 2002;
Sharon 2003;
and I need a query that will select all distinct persons who were members in
2002 BUT NOT in 2003. So in my example above, it would return 'Paul'.

I have a solution to do this but it involves multiple queries. I would like
to have a single query, if possible.
 
The sql you should use will be
Select MyName From MyTable Where MyYear=2002

Or you van create a query that will prompt you with a message to select a Year
Select MyName From MyTable Where MyYear=[Enter Year Here:]

If the Name and the Year on the same Field then you should use Like
Select MyName From MyTable Where MyYear Like "*" & [Enter Year Here:] & "*"
or
Select MyName From MyTable Where MyYear Like "*2002*"
 
Try (all ***untested***):

1. Using frustrated Outer Join:

****
SELECT Main.[Name]
FROM Table1 AS Main
LEFT JOIN
(
SELECT [Name]
FROM Table1 AS Sub
WHERE [Year] = 2003
) AS SQ
ON Main.[Name] = SQ.[Name]
WHERE ( Main.[Year] = 2002 ) AND ( SQ.[Name] Is Null )
****


2. Using Not In Operator:

****
SELECT Main.[Name]
FROM Table1 AS Main
WHERE
( Main.[Year] = 2002 ) AND
( Main.[Name] Not IN
(
SELECT Sub.[Name]
FROM Table1 AS Sub
WHERE ( Sub.[Year] = 2003 )
)
)
****

3. Using Not Exists clause:

****
SELECT Main.[Name]
FROM Table1 AS Main
WHERE
( Main.[Year] = 2002 ) AND
( Not EXISTS
(
SELECT Sub.*
FROM Table1 AS Sub
WHERE ( Sub.[Name] = Main.[Name] ) And ( Sub.[Year] = 2003 )
)
)
****

I expect the SQL String 1 is faster then SQL String 2 then SQL String 3 so
that is my order of preference. But hey, it one works, that should be
enough.

BTW, just in case you actually use [Name] and [Year] as Field names in your
database, both are reserved word. [Name] is the worst since every object in
Access has the Property Name. Suggest you change both Field Names.

--
HTH
Van T. Dinh
MVP (Access)


"(e-mail address removed)"
 
Back
Top