Solved

# How to write a query which puts last test results of different fields in one line

Posted on 2011-05-05
263 Views
I have a table with test results where different aspects were tested on different dates, each test having one of two outcomes. Each test has a date column and a result column.
Now I need a query which will combine the last result in each field in one line (row).
I do not seem to manage it with working with the  "Totals" tool.
I attach the very small database to explain.
0
Question by:Fritz Paul

LVL 42

Accepted Solution

Is this what you mean?
``````Select V1.*, GFLDate, GFLRes , GVADate, GVARes, GVBDate, GVBRes from
((((
SELECT T1.NR, T1.Block, T1.LRDate, T1.LRRes  from  tblPResults T1
inner join
(SELECT NR, Block, Max(LRDate) as MaxLRDate from tblPResults
group by NR, Block) as T2
on T1.NR = T2.NR and T1.Block = T2.Block and T1.LRDate = T2.MaxLRDate
)  as V1 )
inner join
(
SELECT T1.NR, T1.Block, T1.GFLDate, T1.GFLRes  from  tblPResults T1
inner join
(SELECT NR, Block, Max(GFLDate) as MaxGFLDate from tblPResults
group by NR, Block) as T2
on T1.NR = T2.NR and T1.Block = T2.Block and T1.GFLDate = T2.MaxGFLDate
)  as V2   on V1.NR = V2.NR and V1.Block = v2.block)

inner join
(
SELECT T1.NR, T1.Block, T1.GVADate, T1.GVARes  from  tblPResults T1
inner join
group by NR, Block) as T2
on T1.NR = T2.NR and T1.Block = T2.Block and T1.GVADate = T2.MaxGVADate
)  as V3  on V1.NR = V3.NR and V1.Block = v3.block)

inner join
(
SELECT T1.NR, T1.Block, T1.GVBDate, T1.GVBRes  from  tblPResults T1
inner join
(SELECT NR, Block, Max(GVBDate) as MaxGVBDate from tblPResults
group by NR, Block) as T2
on T1.NR = T2.NR and T1.Block = T2.Block and T1.GVBDate = T2.MaxGVBDate
)  as V4  on V1.NR = V4.NR and V1.Block = v4.block
``````
0

Author Closing Comment

This is amazing! So elegant.
Does that come from an excellent knowledge of SQL or what am I missing?
Should I just study SQL?
0

LVL 42

Expert Comment

Thanks for the compliment.

I do have lots of experience with SQL, but truth-be-told, the real trick is getting it to work in Access which is overly fussy about parenthesis.

0

## Featured Post

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…