Previous REcord

[tms05]

I have tried searching through the forum but can't seem to find any solutions for the following. I am sure one exists but didnt have any more spare time sifting through.
How can I pull the previous record depending on date effective. For example. I have a rate history table. I need to pull in the row, the emp ID, Name, rate, rate date and then rate prior to rate(rate amount which was effective prior to this record.
So if ANnie had effective 10/01/02 a rate of 10.00. On 11/01/02 his rate was 12.00. On 01/01/03 his rate was 15.00. I need my query to show-
Name ID Rate Date Rate Previous Rate
Annie 5607 01/01/03 15.00 12.00
Annie 5607 11/01/02 12.00 10.00
Annie 5607 10/01/02 10.00 0

I am also needing it to work in case a date is time stamped. There can't be duplicate dates for the same employee and sometimes they recieve an increase and also a promotion on the same day so the latter would have the time added to the date. ANy help would be greatly appreciated

 

[rac2]

This problem involves comparing one row of a table to another row; this is something SQL does not do easily. This kind of processing can be handled in procedures using a cursor. Be that as it may, there is a query which will turn up the previous record.

SELECT a.Name, a.ID, a.DateEffective, a.Rate,
       (SELECT Rate
        FROM EmployeeRateHistory
        WHERE ID =a.ID
          AND DateEffective =
              (SELECT MAX(DateEffective)
               FROM EmployeeRateHistory
               WHERE ID =a.ID
               AND DateEffective < a.DateEffective)
       ) AS &quot;Previous Rate&quot;
FROM EmployeeRateHistory a

Then you can spiff that up with the Access equivalent of ISNULL( (SELECT Rate ... a.DateEffective)), 0) which may be the same function, it provides the zero for the oldest row.

And you can ORDER BY as you need.

 

[GingerR]

put a new column in your query.

Expr: DMax(&quot;Rate&quot;,&quot;TableName&quot;,&quot;EmpID = &quot; & [EmpID] & &quot; and RateDate < #&quot; & [RateDate] & &quot;#&quot;)

this says:
find the rate for the max date which is less than the date in the current row. for the empID which is showing.

ok? you can look up dmax, dcount, etc in HELP.