Update Calc Code

[razchip]

I'm trying to run an update query to update a table field.

I need to look at each record (sorted by date - ascending) xx/xx/xxxx - Job# - Hours

The current record is 02/07/2007 - Job# (12345)- Hours (25), if the previous record 01/28/2007 has the same job# (12345) - Hours (17), then I want to subtract the the hours difference and place it in a field called HrsInc (25 - 17)= 8.

Does anyone have any suggestions. Am I better off trying to update a table or run a query?

Thanks for any help or guidance.

Thanks for the help.
Greg

 

[rac2]

Relational databases are not very effective or efficient when it comes to this kind of calculation. This kind of calculation involves two different rows. Worse, the rows are related by the current date and previous date; finding the row with the previous date is a bit complex.

The previous row for Job# (12345) is the row with the maximum date less than the current date among the rows for Job# (12345).

If we knew the date in the current row, say it is 02/07/2007, then the previous row would be

SELECT hours_duration FROM MyTable
WHERE job_no = 12345
     AND date_posted = (
                                     SELECT MAX(date_posted) FROM MyTable
                                     WHERE  job_no = 12345
                                         AND  date_posted < #02/07/2007#
                                    )

This yields the hours for the previous record for job #12345 with the date of the current record being 2/07/2007 .

To obtain this value for all jobs, you could save a query based on a self-join. Call it MyTablePrevious.

SELECT A.job_no, A.hours_duration, B.hours_duration FROM MyTable A
JOIN MyTable B ON  B.job_no = A.job_no
     AND B.date_posted = (
                                     SELECT MAX(date_posted) FROM MyTable
                                     WHERE  job_no = A.job_no
                                         AND  date_posted < A.date_posted
                                    )

In other words, to each record in table A, join the one record for the same job which has the previous date.

Each row in this result has the two number of interest in the same row. Which is what SQL requires. With this you can do the computation. You could merely show it, or you could use it to update another colum in MyTable.

If it is possible to revise your application in such a way that the computation is stored in the same record as the beginning date is stored, then it will be much more efficient to retrieve this value later.

You would define columns such as date_begin, date_end, and duration. When the activity starts you fillin date_begin. When it ends you fillin date_end and duration. In the future, when you need duration of the activity, it is already calucuated, just retrieve the record. In addition, it is easy to find the latest record because it is the only record where date_end is NULL. No need to look for the MAX(date_posted). Just update the record where date_end IS NULL.

HTH