How to develop a table in multiple columns with a query

advertisements

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.

Sample

 SampleID     DateCreated
 1            6/1/2013
 2            6/2/2013
 3            6/3/2013
 4            6/3/2013

Analysis

AnalysisID    AnalysisName
1             Temp
2             Density
3             Mass

Values

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.