Need to find salary as of...

[ps40life]

Hi,
I haven't found an answer to this, so...
I need to find the salary of a person as of 8/1/2003. I have a history table that lists the date of a salary change and the amount. For example...

Emp 1 5/3/01 54,000
Emp 1 6/4/02 57,000
Emp 1 8/6/03 59,000

So, I need the 6/4/02 record at 57,000 to populate a field on my form &quot;Salary as of 8/1&quot;. I tried using the max function with <=8/1/2003 in criteria in the date field, but can't pull salary amount because of grouping. Any suggestions??
Thanks!

 

[daneharnett]

Try this out,

SELECT Max(Table1.DateChanged) AS MaxOfDateChanged, Last(Table1.Salary) AS LastOfSalary
FROM Table1
HAVING (((Table1.DateChanged)<=#1/8/2003#));


Table1
Employee - Text
DateChanged - DateTime
Salary - Currency

Hope it helps