To answer my question, I will simplify my data structure. I have three tables, Sample, Analysis, Values. Sample has a Pk autonumber field, SampleID, and a DateCreated field. Analysis has a PK autonumber field AnalysisID, and a AnalysisName field. Values has three fields, SampleID, AnalysisID, Value. A generic layout of the data is below.
SampleID DateCreated 1 6/1/2013 2 6/2/2013 3 6/3/2013 4 6/3/2013
AnalysisID AnalysisName 1 Temp 2 Density 3 Mass
SampleID AnalysisID Value 1 1 65 1 2 1.1 2 1 68 2 2 1.0 2 3 57 3 2 1.2 4 2 0.9 4 3 54
Now, I want someone to be able to design a query on the fly... and I think I can figure that out, IF I can figure out how to get this data into the following format. For instance, if the user wanted all samples and analysis, the query needs to generate a table like this...
SampleID DateCreated Temp Denisty Mass 1 6/1/2013 65 1.1 2 6/2/2013 68 1.0 57 3 6/3/2013 1.2 4 6/3/2013 0.9 54
What will the query look like to get this result? I can figure out how to create it on the fly with VBA, as long as I can understand what the end SQL result needs to look like. Thanks!
Here's a PIVOT table query that will do it for you.
The tables 1-3 are in the order you listed them. I also renamed Value to Value1 because its a reserved word.
TRANSFORM First(Table3.Value1) AS FirstOfValue1 SELECT Table1.SampleID, Table1.DateCreated FROM Table2 INNER JOIN (Table1 INNER JOIN Table3 ON Table1.SampleID = Table3.SampleID) ON Table2.AnalysisID = Table3.AnalysisID GROUP BY Table1.SampleID, Table1.DateCreated PIVOT Table2.AnalysisName;
Paste this into the SQL part of your query builder, then click the design button to see how it's done.