• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

Extract most recent test data from table into multiple fields.

Could someone help me out please. This is too complicated for me and I have spent many hours trying.

Help me make a query or more than one if necessary.

I Have a table in my database which holds test results.
Tests are carried out for various virusses.
Tests are entered consecutively against the Nr and Block.
Each Nr may have various Blocks.
Blocks may not have names (May be empty).
I am only interested in the most recent test result for specific virusses. They are

PD
PR
AM
CL
SG
LR
GFL
GVA
GVB

I need these latest results for each combination of Nr and Block in one record of a query.
Each record in the query should consist of The following fields.


Nr            
Block            
Virus            
PDDate            Most recent date for virus "PD"
PDRes                  Test result on the most recent test for "PD"
PDCom            Test Comment for the most recent test for "PD"
PRDate            Most recent date for virus "PR"
PRRes                  Test result on the most recent test for "PR"
PRCom            Test Comment for the most recent test for "PR"
AMDate            Most recent date for virus "AM"
AMRes            Test result on the most recent test for "AM"
AMCom            Test Comment for the most recent test for "AM"
CLDate            Most recent date for virus "CL"
CLRes                  Test result on the most recent test for "CL"
CLCom            Test Comment for the most recent test for "CL"
SGDate            Most recent date for virus "SG"
SGRes                  Test result on the most recent test for "SG"
SGCom            Test Comment for the most recent test for "SG"
LRDate            Most recent date for virus "LR"
LRRes                  Test result on the most recent test for "LR"
LRCom            Test Comment for the most recent test for "LR"
GFLDate            Most recent date for virus "GF"
GFLRes            Test result on the most recent test for "GF"
GFLCom            Test Comment for the most recent test for "GF"
GVADate            Most recent date for virus "GV"
GVARes            Test result on the most recent test for "GV"
GVACom            Test Comment for the most recent test for "GV"
GVBDate            Most recent date for virus "GV"
GVBRes            Test result on the most recent test for "GV"
GVBCom            Test Comment for the most recent test for "GV"

I attach an Access 2003 database with the table.

TestResults.mdb
0
Fritz Paul
Asked:
Fritz Paul
  • 3
  • 2
1 Solution
 
DockieBoyCommented:
Hi there,

Try this and let me know if it's what you are trying to achieve.


SELECT TblPathResults.Nr, TblPathResults.Block, TblPathResults.Virus, First(TblPathResults.TestDate) AS FirstOfTestDate, TblPathResults.TestResult, TblPathResults.TestComment
FROM TblPathResults
GROUP BY TblPathResults.Nr, TblPathResults.Block, TblPathResults.Virus, TblPathResults.TestResult, TblPathResults.TestComment
ORDER BY TblPathResults.Nr, TblPathResults.Block, TblPathResults.Virus, First(TblPathResults.TestDate);
0
 
Fritz PaulAuthor Commented:
Thanks for your help.

Your code solves the first part of the problem,
except because I need the most recent results,
one should use FirstOfTestDate and not LastOfTestDate.

The next part where I get totally stuck is that I now only want one line for each combination of Nr and Block where there are seperate fields per each virus for  | Last TestDate | TestResult | TestComment |  .

The fields in the end result should then be

(
Remember the Virusses which I want to have columns for are PD
PR
AM
CL
SG
LR
GFL
GVA
GVB  )

Nr            
Block            
PDDate            Most recent date for virus "PD"
PDRes                 Test result on the most recent test for "PD"
PDCom            Test Comment for the most recent test for "PD"
PRDate            Most recent date for virus "PR"
PRRes                  Test result on the most recent test for "PR"
PRCom            Test Comment for the most recent test for "PR"
AMDate            Most recent date for virus "AM"
AMRes            Test result on the most recent test for "AM"
AMCom            Test Comment for the most recent test for "AM"
CLDate            Most recent date for virus "CL"
CLRes                  Test result on the most recent test for "CL"
CLCom            Test Comment for the most recent test for "CL"
SGDate            Most recent date for virus "SG"
SGRes                  Test result on the most recent test for "SG"
SGCom            Test Comment for the most recent test for "SG"
LRDate            Most recent date for virus "LR"
LRRes                  Test result on the most recent test for "LR"
LRCom            Test Comment for the most recent test for "LR"
GFLDate            Most recent date for virus "GF"
GFLRes            Test result on the most recent test for "GF"
GFLCom            Test Comment for the most recent test for "GF"
GVADate            Most recent date for virus "GV"
GVARes            Test result on the most recent test for "GV"
GVACom            Test Comment for the most recent test for "GV"
GVBDate            Most recent date for virus "GV"
GVBRes            Test result on the most recent test for "GV"
GVBCom            Test Comment for the most recent test for "GV"

Horizontally it will look like this
Nr Block       PDDate  PDRes PDCom PRDate      PRRes      PRCom      AMDate      AMRes      AMCom      CLDate      CLRes      CLCom      SGDate      SGRes      SGCom      LRDate      LRRes      LRCom      GFLDate      GFLRes      GFLCom      GVADate      GVARes      GVACom      GVBDate      GVBRes      GVBCom

I think if you try this SQL you will get a better idea of the lay out that I need.

SELECT TblPathResults.Nr, TblPathResults.Block, TblPathResults.TestResult, Max(IIf([Virus]="PD",[TestDate],"")) AS PDDate, TblPathResults.TestResult AS PDRes, Max(IIf([Virus]="PR",[TestDate],"")) AS PRDate, TblPathResults.TestResult AS PRRes, Max(IIf([Virus]="AM",[TestDate],"")) AS AMDate, TblPathResults.TestResult AS AMRes, Max(IIf([Virus]="CL",[TestDate],"")) AS CLDate, TblPathResults.TestResult AS CLRes, Max(IIf([Virus]="SG",[TestDate],"")) AS SGDate, TblPathResults.TestResult AS SGRes, Max(IIf([Virus]="LR",[TestDate],"")) AS LRDate, [TestResult] & "" AS LRRes, Max(IIf([Virus]="GFL",[TestDate],"")) AS GFLDate, TblPathResults.TestResult AS GFLRes, Max(IIf([Virus]="GVA",[TestDate],"")) AS GVADate, TblPathResults.TestResult AS GVARes, Max(IIf([Virus]="GVB",[TestDate],"")) AS GVBDate, TblPathResults.TestResult AS GVBRes
FROM TblPathResults
GROUP BY TblPathResults.Nr, TblPathResults.Block, TblPathResults.Virus, TblPathResults.TestDate, TblPathResults.TestResult
HAVING (((TblPathResults.Nr)<>"") AND ((TblPathResults.Virus)<>"") AND ((TblPathResults.TestDate)>0))
ORDER BY TblPathResults.Nr, TblPathResults.Block, TblPathResults.Virus, TblPathResults.TestDate;


I hope you can help.


   
0
 
Fritz PaulAuthor Commented:
I managed to get a solution for myself.
I attch the file. See qryTestResults
I realize it could be more elegant, but it works well.
Thanks for everyone who spent time on this.

TestResults.mdb
0
 
DockieBoyCommented:
No problem, sorry I couldn't have been of more help, but I had trouble understanding exactly what you were trying to achieve.  

Just as a foot note, I think you need to make some changes to the structure of the data storage, from what I can gather I think you might need multiple tables to really normalize that data.

:)
0
 
Fritz PaulAuthor Commented:
I solved it myself. Thanks.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now