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

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.
Please give advice.  QueryProblem.mdb
Fritz PaulAsked:
Who is Participating?
 
dqmqConnect With a Mentor Commented:
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
(SELECT NR, Block, Max(GVADate) as MaxGVADate from tblPResults
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

Open in new window

0
 
Fritz PaulAuthor Commented:
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
 
dqmqCommented:
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
All Courses

From novice to tech pro — start learning today.