Show the sql statements for the two queries.
This is the SQL for the Union query:
Code:
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:
Code:
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