Get Runtime in Query

[Guest]

I want to calc the end time of jobs that I store in a table. We only store
the beginning time of each job but the end time would just be the start time
of the next job.
For example Job 1 has a start time of 1:30
Job 2 has a start time of 2:45
Job 3 has a start time of 3:45.

I want my query to calc the end date of job 1 as 2:45, job 2 as 3:45.

How would you write this?

Thanks

 

[Guest]

This will do it:

SELECT T1.Job, T1.Start, T2.Start FROM tblTest As T1, tblTest As T2 WHERE
T2.Job = T1.Job + 1;

But note the last job is ignored since there is no next job to determine
when it finished.

-Dorian

 

[kingston via AccessMonster.com]

There are lots of ways to do this so here's just one way:

Make a query based on the table but include it twice. It will appear in the
design pane as

and [Table_1]. Next link the field [Job] between the
two tables. The elapsed time output field should be [Table_1].[Time]-

.
[Time]. Now, here's the trick. In SQL mode, modify the linked field for

to be [Job+1]. This assumes that the format of the field [Job] is
simply and uniformly incremented. HTH.

 

[Van T. Dinh]

.... and finally, I don't think you should store it at all!

If you adopt the algorithm for working out the EndTime as you posted, then
simply work it out whenever you need it. This will give you more accurante
and consistent value unlike the stored value of EndTime.

Using your example, let's say you worked out the EndTime of Job 2 is 15:45
and stored it. Shortly after, a job was done between 15:15 to 15:45 but
hasn't been recorded in the database so a new Record is created for "Job
2bis". Thus, now your EndTime for "Job 2" is incorrect and the data is
inconsistent (DON'T rely on the person enters the "Job 2bis" to do the right
thing).

However, if you simply work it out when you need it, the value will be
correct and consistent with the data stored in your database.