Show current report date and prior report date in same report

[mogll]

I have a dbase to track internal audits by facility. In each report, I want
to show the current audit date and the prior audit date for that facility.
My SQL skills have little to be desired, but here's what I have so far:

SELECT tblAudits.intSiteID, tblAudits.dtmClose, tblAudits.lngAuditID,
tblAudits.txtAuditType
FROM tblAudits
WHERE (((tblAudits.txtAuditType)="HSEQ-IMS")),
(SELECT Max(tblAudits.dtmClose) FROM tblAudits as A
WHERE A.intSiteID=B.intSiteID and A.dtmClose < B.dtmClose) as PriorAudit
FROM tblAudits as B;

I keep getting a syntax error. Help. Thanks.

 

[John Spencer (MVP)]

SELECT tblAudits.intSiteID
, tblAudits.dtmClose
, tblAudits.lngAuditID
, tblAudits.txtAuditType
, (SELECT Max(tblAudits.dtmClose)
FROM tblAudits as A
WHERE A.intSiteID=B.intSiteID
and A.dtmClose < B.dtmClose
AND A.txtAuditType = "HSEQ-IMS") as PriorAudit
FROM tblAudits
WHERE tblAudits.txtAuditType="HSEQ-IMS"

If you need the prior audit to be the same type you need to include that
criteria into the correlated subquery. If you want any prior audit then drop
AND A.txtAuditType = "HSEQ-IMS"
from the where clause of the subquery.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[mogll]

John, thank you for taking the time to assist with this. When I run the query
as is, paramter values B.intSiteID and B.dtmClose are not recognized. So to
define B, I added "as B" to the last "FROM tblAudits," then it really crashed.

 

[John Spencer (MVP)]

Sorry - I didn't get all the aliasing done correctly. Try the following

SELECT B.intSiteID
, B.dtmClose
, B.lngAuditID
, B.txtAuditType
, (SELECT Max(A.dtmClose)
FROM tblAudits as A
WHERE A.intSiteID=B.intSiteID
and A.dtmClose < B.dtmClose
AND A.txtAuditType = "HSEQ-IMS") as PriorAudit
FROM tblAudits AS B
WHERE B.txtAuditType="HSEQ-IMS"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[mogll]

Awesome my friend. I owe you one.

Sorry - I didn't get all the aliasing done correctly. Try the following

SELECT B.intSiteID
, B.dtmClose
, B.lngAuditID
, B.txtAuditType
, (SELECT Max(A.dtmClose)
FROM tblAudits as A
WHERE A.intSiteID=B.intSiteID
and A.dtmClose < B.dtmClose
AND A.txtAuditType = "HSEQ-IMS") as PriorAudit
FROM tblAudits AS B
WHERE B.txtAuditType="HSEQ-IMS"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County