Trying to query 2 records in a query with same name

[Kirsti]

Hi, I have a query, and the results are:



Facility_Name Audit_Date Audit_Due_Date

Facility1 01-Jan-2012 01-Apr-2012
Facility1 05-Apr-2012 05-Jul-2012

So, an audit is due every 3 months, but for Facility1, the 2nd Audit was due on 01-Apr, but didn't happen until 05-Apr, so therefore it was 4 days overdue.

This is what I want to calculate, but I just can't figure out how to do it!

Any help much appreciated.

[June7]

You want to calculate how many days the actual audit date was past the first of the month?

[Audit_Date] - CDate(Month([Audit_Date]) & "/1/" & Year([Audit_Date]))

[Kirsti]

Hi June,

I want to calculate how many days the actual audit date was past the audit due date (not necessarily due on the 1st of the month).

The issue is that the data is on 2 different rows in the query results. So, [Audit_Date] on row 2 of the query result minus [Audit_Due_Datae] on row 1 of the query result.

[June7]

You want to pull value from another record. Need a subquery. One method is shown in http://allenbrowne.com/subquery-01.html

[Kirsti]

Ha! Perfect, thanks June, you've solved my problem again!