Cannot use UNION operator in subquery?

[EvanRosenlieb]

Howdy folks,



I am working on a project in which year by year data on plants is kept in separate tables. Each plant has data that does not change year to year as well as its Individual ID stored in a separate "Individuals" table. What I am trying to do is create a query that will return the new individuals for each year (for example, one that will return the plants in the Measurements2002 table whose ID's were not found in either the Measurements2001 table or the Measurements2000 table) (2000 was the first year of the study).

To me, this seems like it should be rather simple, here is the code that I tried to use:

SELECT ID FROM Individuals WHERE Individuals.ID NOT IN
(SELECT IndividualID FROM Measurements2000
UNION
SELECT IndividualD FROM Measurements2001);

However when I try this Access tells me that "This operation is not allowed in subqueries"

Are union Queries really not allowed in subqueries or am I using improper syntax? Or is there a work around?

Your help is greatly appreciated
-Evan

[June7]

Instead of nesting, try saving the UNION query separately then use saved query as source for the outer query. Try the Find Unmatched query wizard.

[EvanRosenlieb]

June-
Thanks, this is indeed what I have figured out to do since I made the thread. It works, but I would rather it not have to include the extra unnecessary step, so I'll leave this as unsolved in case anyone else knows a single-step answer to the question
-Evan

[June7]

Show the sql statements for the two queries.

Are you trying to run the query in VBA? I had problem running code with UNION. No matter where the UNION was, code would not work. Not even if the query was a saved Access object or if was nested or used by a SELECT saved Access query. If UNION was anywhere in the sequence, code would not work. Had to redesign my procedure to not use UNION.

[pbaldy]

I would rather it not have to include the extra unnecessary step

The "unnecessary step" is necessary because your data is not normalized. The UNION query is normalizing the data, so you'll probably be needing it for other things anyway. The data should be in a single table with a date field or at least a field for the year, not individual tables.

[EvanRosenlieb]

Show the sql statements for the two queries.

This is the SQL for the Union query:

SELECT IndividualID FROM Measurements2000
UNION
SELECT IndividualID FROM Measurements2001
UNION
SELECT IndividualID FROM Measurements2002
UNION
SELECT IndividualID FROM Measurements2003
UNION
SELECT IndividualID FROM Measurements2004
UNION
SELECT IndividualID FROM Measurements2005
UNION
SELECT IndividualID FROM Measurements2006
UNION
SELECT IndividualID FROM Measurements2007
UNION
SELECT IndividualID FROM Measurements2008
UNION 
SELECT IndividualID FROM Measurements2009;

Heres the SQL for the second step:

SELECT Plots.SiteID, 
Individuals.PlotID, 
Individuals.ID, 
Individuals.Tag, 
Individuals.Spp, 
Measurements2009.Class AS 2009Class,
 Measurements2009.Fr AS 2009Fr, 
Measurements2009.Fl AS 2009Fl, 
Measurements2009.FlStLn1 AS 2009FlStLn1, 
Measurements2009.FlStLn2 AS 2009FlStLn2, 
Measurements2009.FlStLn3 AS 2009FlStLn3, 
Measurements2009.FlStLn4 AS 2009FlStLn4, 
Measurements2009.FlStLn5 AS 2009FlStLn5, 
Measurements2009.FlStCount AS 2009FlStCount,
 Measurements2009.NFStLn1 AS 2009NFStLn1, 
Measurements2009.NFStLn2 AS 2009NFStLn2,
 Measurements2009.NFStLn3 AS 2009NFStLn3,
 Measurements2009.NFStLn4 AS 2009NFStLn4, 
Measurements2009.NFStLn5 AS 2009NFStLn5, 
Measurements2009.NFStCount AS 2009NFStCount, 
Measurements2009.[+] AS [2009+], 
Measurements2009.Per AS 2009Per,
 Measurements2010.Class AS 2010Class, 
Measurements2010.Fr AS 2010Fr, 
Measurements2010.Fl AS 2010Fl, 
Measurements2010.Notes AS 2010Notes,
 Measurements2010.FlStLn1 AS 2010FlStLn1,
 Measurements2010.FlStLn2 AS 2010FlStLn2,
 Measurements2010.FlStLn3 AS 2010FlStLn3, 
Measurements2010.FlStLn4 AS 2010FlStLn4,
 Measurements2010.FlStLn5 AS 2010FlStLn5, 
Measurements2010.FlStCount AS 2010FlStCount, 
Measurements2010.NFStLn1 AS 2010NFStLn1,
 Measurements2010.NFStLn2 AS 2010NFStLn2,
 Measurements2010.NFStLn3 AS 2010NFStLn3,
 Measurements2010.NFStLn4 AS 2010NFStLn4, 
Measurements2010.NFStLn5 AS 2010NFStLn5,
 Measurements2010.NFStCount AS 2010NFStCount, 
Measurements2010.[+] AS [2010+], 
Measurements2010.Per AS 2010Per
FROM Measurements2010 RIGHT JOIN ((Plots INNER JOIN Individuals ON Plots.ID=Individuals.PlotID) 
INNER JOIN Measurements2009 ON Individuals.ID=Measurements2009.IndividualID) ON Measurements2010.IndividualID=Measurements2009.IndividualID;

I am not using VBA anywhere around this particular procedure :-)

The "unnecessary step" is necessary because your data is not normalized. The UNION query is normalizing the data, so you'll probably be needing it for other things anyway. The data should be in a single table with a date field or at least a field for the year, not individual tables.

This is 100% true. However, the data that has been collected has changed almost every year, i.e. sometimes average stem lengths have been measured, sometimes total, sometimes theres a a total count of stems sometimes just the flowering ones. I was brought onto this project this summer in order to organize a decades worth of data that has been stored in excel files. The professor was more comfortable with keeping the tables in a form that was similar to how they were kept before, instead of trying to disaggregate and recalculate in order to get the best database architecture possible. Had a database been set up from the beginning (and had she known it would be longer than a three year project) then the structure would look very different, but we think this is best solution for the circumstance.

Thanks for the help

[June7]

Which of the queries is name for the UNION query?