Prior Date

[mogll]

I have a dbase to track internal audits by facility. In the audit report, I
have the audit date field shown, but also want to add a field to display the
prior audit date for that specific facility. I have tried doing this with a
query, but no luck. My data looks like this:
AuditID SiteID AuditDate
30 10 1/8/09
23 10 3/6/08
15 10 2/5/07
29 11 1/2/09
17 11 2/6/08
14 11 6/8/07
10 11 9/7/06
Any suggestions will be greatly appreciated.

 

[John Spencer]

The SQL for a query to do this would look like:

SELECT SiteID, AuditID, AuditDate
, (SELECT Max(AuditID) FROM TableName as A WHERE A.SiteID = B.SiteID and
A.AduitDate < B.AuditDate) as PriorAudit
FROM TableName as B



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

 

[Duane Hookom]

The query might look like:
SELECT AuditID, SiteID, AuditDate,
(SELECT Max(AuditDate)
FROM dbase db
WHERE db.SiteID = dbase.SiteID and db.AuditDate < dbase.AuditDate )
As PrevDate
FROM dbase;

 

[mogll]

Duane, thank you for the quick response. My SQL skills have little to be
desired. I can't get it through a syntax error:

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;