As-at date filter

[Geordie2008]

Good afternoon all,

I have the following challenge (!)

I have 200 employee's data which is historised for changes.

When a change is made to an employee (using a form) the change is date stamped and held in an audit table.

I have been challenged to do the following:

Run as-at reports.

In order to do this I believe I need a query which will filter on the "as-at" date chosen by the user.

If the user picks the date: 01 Jun 08 I want to be able to bring back the following:

For each employee (I have a ID autonumber for each employee), the row of data that is the MAX of all data where the "Record Date" is equal to or lower than the "Filter by" report date.

e.g.



REFER TABLE 1 of attached doc


If the report is run as-at: 10 Jun, I would like to return:

REFER TABLE 2 of attached doc

If it is run as at 18th Jul, I would like to return:

REFER TABLE 3 of attached doc


The filter date is on a form called [Forms]![frmAsAt]![FilterDate]

Can someone please help me with the syntax I need to put within the "criteria" section of my query?

Thank you,
Mandy

 

[pbaldy]

Why would Smith's 01-Jun-08 record not be included in table 3?

 

[Geordie2008]

Ah... I only have 2x employees in this Table, Mandy Smith changes her name to Mandy Jones....

Hence I need to use the Emp ID as a "group by" when doing the MAX caluc.

So ForEach (EmpID) take the Max of (Date Changed) where (Date Changed) is <= (Filter Date)

Thanks for your help,
Mandy

 

[pbaldy]

Ah, didn't even notice that. Try 2 queries, the first:

SELECT EmpID, Max(DateChanged)
FROM Table1
WHERE DateChanged <= [Forms]![frmAsAt]![FilterDate]
GROUP BY EmpID

Then a second that joins that query to the table on those 2 fields, enabling you to return all the info from that record.

 

[Geordie2008]

Mmmmm, almost....

I realised that my DateChanged format looks like this:

19/06/2008 16:50:05

will I need to change the syntax for this?

Thanks,
Mandy

 

[pbaldy]

As is the query would miss records on the same date. Try this:

WHERE DateChanged < [Forms]![frmAsAt]![FilterDate] + 1

 

[Geordie2008]

Its really weird.... its not working:

My actual sql (for the exact field names) is:

SELECT ID, Max(Record_Date)
FROM quni_MAIN_STAFF_Details_AUDIT_TRAIL
WHERE DateChanged < [Forms]![frmAsAt]![FilterDate] + 1
GROUP BY ID

It is changing it to:

SELECT quni_MAIN_STAFF_Details_AUDIT_TRAIL.ID, Max(quni_MAIN_STAFF_Details_AUDIT_TRAIL.Record_Date) AS MaxOfRecord_Date
FROM quni_MAIN_STAFF_Details_AUDIT_TRAIL
WHERE ((([DateChanged])<[Forms]![frmAsAt]![FilterDate]+1))
GROUP BY quni_MAIN_STAFF_Details_AUDIT_TRAIL.ID;

 

[Geordie2008]

Sorry... syntax is actually.....

SELECT quni_MAIN_STAFF_Details_AUDIT_TRAIL.ID, Max(quni_MAIN_STAFF_Details_AUDIT_TRAIL.Record_Date) AS MaxOfRecord_Date
FROM quni_MAIN_STAFF_Details_AUDIT_TRAIL
WHERE ((([Record_Date])<[Forms]![frmAsAt]![FilterDate]+1))
GROUP BY quni_MAIN_STAFF_Details_AUDIT_TRAIL.ID;


and still is not behaving....

Could it be the format of the date field?

Thanks,
M

 

[pbaldy]

Does the date field have a date/time data type? Can you post a sample db?

 

[Geordie2008]

Hi,

Sorry, went to the pub to grab food as figured I'll be at my desk for a while yet!

....and when I got back.... this is now working perfectly (?!?!)

I must go to the pub more often!
Thanks so much for your help!
Mandy

 

[pbaldy]

Sometimes all it takes is a break. We had dinner at a pub during a brief visit to London last year. Very enjoyable setting.