Query Results

Question

Here's my code:           

Me.PatientID = "65511"
Me.ProdCode = "36853"

strSQL = "SELECT PatientID, ProdCode, Serial, TransDate From tblTrans WHERE TransDate=(SELECT MAX(TransDate) FROM tblTrans WHERE PatientID = '" & Me.PatientID & "' AND ProdCode = '" & Me.ProdCode & "')"

CurrentDb.QueryDefs("qryDefTrans").SQL = strSQL

Here are my results:

Patient ID  ProdCode  Serial   TransDate
======  ======  ====   =======
65511 36853 12345   04/22/2019
65511 11311 35712   04/22/2019

The good news is that it did return the max TransDate for ProdCode 36853. 

However, why did it return 11311?  (TransDate was correct.)

I just need to know the Max TransDate of 36853.

Thanks!

Ken

Answer 1

Because your query is first pulling the max date where the PatientID & ProdCode values match.  Then it performs a Select pulling the records where the TransDate equal that max date.  The SELECT itself isn't being filtered in any way by the PatientID or ProdCode.

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

Daniel,

That's what I was thinking.  Is it possible to do all I want to do with one query or do I need to do two?

Ken

Answer 3

Perhaps something like:

strSQL = "SELECT PatientID, ProdCode, Serial, TransDate From tblTrans WHERE TransDate=(SELECT MAX(TransDate) FROM tblTrans WHERE PatientID = '" & Me.PatientID & "' AND ProdCode = '" & Me.ProdCode & "') AND PatientID = '" & Me.PatientID & "' AND ProdCode = '" & Me.ProdCode & "'"

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net