>Sorry, The error problem is solved ! I had joined the 'INNER JOIN' statement !
>However, my code still does not do what I want it to. Any suggestions anyone ?
>
>Steve

Hi Steve,

Let me try to add my 2 Eurocents here. It seems to me that what you miss in your database structure is the ending date (i'll refer to it as date2) of the period for which a specific wage level is valid.

First there is the possibility to add a field to your table. When the wage changes, you would access the currently valid entry and set the end date to the day before the new wage becomes valid. At creation of a new record (i.e. when a wage changes) this date should be set as far as possible in the future.

Having such a field would allow you to access the wage rate table with a simple SQL WHERE clause somethinglike (WHERE work.date BETWEEN wage.date1 AND wage.date2.

If adding this field (and going through the process of assigning the values in the currently existing table) is not an option then you will have to compute date2 at query time. Basically this consists of generating a cursor that contains both dates and the wage, associated with the entity (gang?) that executed the work.

This kind of operation (of generating an end date) is to the best of my knowledge only doable via a SCAN ENDSCAN through the table that is ordered by person, date1 descending, and storing date1-1 in the date2 field of the next record (i.e. chronologically the previous one).

Hope this helps.

Marc