1

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!

riley3131
  • 288
  • 3
  • 21

2 Answers2

3

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.

Tom Collins
  • 4,069
  • 2
  • 20
  • 36
  • I like this method, it is quick and easy! However, I am not sure how I could modify it to only show certain ones. Like, if I only wanted Temp and Density, how would I prevent Mass from showing up? Using a WHERE clause to say WHERE AnalysisID = 1 OR AnalysisID = 2? And, can I specify an order, rather than alphabetical? People are used to seeing them in a certain order and changing it may be difficult to adapt. Thanks so much, I have never really messed with PIVOT tables before, I can see how they could be very handy. – riley3131 Jun 07 '13 at 13:51
  • Exactly, add WHERE criteria to the subquery. – Hart CO Jun 07 '13 at 14:14
  • Thanks! Any help on organizing the fields based on AnalysisID rather than AnalysisName? They are showing up left-to-right alphabetically, rather than numerically. I want Temp in the 3rd column, Density 4th, and Mass 5th. – riley3131 Jun 07 '13 at 14:58
  • What happens when you ORDER BY Analysis ID? After GROUP BY, before PIVOT, just spit-balling here. – Hart CO Jun 07 '13 at 15:00
  • 2
    It was an interesting question, so I spent some time looking for an answer. Looks like the only way to do it is by adding in("Temp","Density","Mass",...) on the end of the PIVOT statement. I'd just make a query on the fly with VBA to include this info. – Tom Collins Jun 07 '13 at 19:03
  • Got it! I added 'in' and that worked! Like this... `PIVOT Analysis.AnalysisName in ("Temp","Density","Mass")` This will be easy to generate on the fly and will allow me to export perfectly! Thanks! I really appreciate it! – riley3131 Jun 07 '13 at 20:15
  • Your welcome. Seems like there should be something built in, but it's Microsoft, so what are you going to do? :-) – Tom Collins Jun 07 '13 at 20:28
2

Not sure about Access-specific answer, but you can probably use CASE statements, simplified without all your joins, something like:

SELECT SampleID, DateCreated
       ,MAX(CASE WHEN AnalysisID = 1 THEN Value ELSE '' END) as Temp
       ,MAX(CASE WHEN AnalysisID = 2 THEN Value ELSE '' END) as Density
       ,MAX(CASE WHEN AnalysisID = 3 THEN Value ELSE '' END) as Mass
FROM Tables/Joins, Etc.
GROUP BY SampleID, DateCreated

You could also look into PIVOT function, but if you have only a handful of Analysis types then this is a simple way to do it.

Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • Thanks so much for your suggestion. I currently have 53 analysis types with 125,000 rows in the values tables. I have never used a setup like this, but an up for giving it a try... does it seem likely to work for so many rows? I am going to estimate we will have 300,000+ rows in values each year. – riley3131 Jun 07 '13 at 02:13
  • Well, it might be cumbersome to make 53 MAX/CASE statements, but it will work just fine. – Hart CO Jun 07 '13 at 03:21
  • So 'CASE WHEN' is T-SQL, Access does not support it, but I have got SWITCH to work halfway. I can get the values, but they are not grouped by SampleID, and if I include this, I have to include all the 'SWITCH' elements as part of an aggregate. I don't know how to do this and keep the data integrity. – riley3131 Jun 07 '13 at 14:13
  • Sounds like PIVOT might be the way to go in Access then. – Hart CO Jun 07 '13 at 14:15