Append Date/Time Values (Best Of)

[Drepso]

I have a table with numerous timestamps in (dd/mm/yyyy hh:mm:ss) format. I have worked out the calculations of specific time intervals which are updated for each record.

Each “job” may have any number of records associated with it. Each job is given a unique JobID number which is in alpha-numeric format. Eg. 0001-A. Records can be associated for the same job by comparing the Job ID number. If the numeric component is the same and the alpha is different (eg. Rows 1 & 2) then these records are for the same job.


Date,Job ID,Start Time,Time1,Time 2
01/01/2002,0001-A,01/01/2002 23:45:00,00:01:00, 00:05:00
01/01/2002,0001-B,02/01/2002 00:10:00,00:02:00, 00:03:00
02/01/2002,0002-A,01/01/2002 14:10:00,00:03:00, 00:05:00

What I need to do!

In the above example I need to run a query which will identify that the first two rows are associated to the same job and append the timestamps with the “lowest time elapsed” in Time 1 & Time2 to a separate table with just the numeric part of the Job ID.

Eg:

Date,Job ID,Start Time,Time1,Time 2
01/01/2002,0001,00:01:00,00:03:00

A problem I have is that the Job ID number is reset and may occur several times throughout the month but not within 24 hours of the “Start Time”. Also some timestamps may be recorded on different days ie. Before and after midnight.

Can anyone help me out? I have limited SQL knowledge and I’m not sure how to tackle the above.

Thanks in advance

 

[hermanlaksko]

I think that a dmin might help you out here.

Dmin("Time2","YrTbl","Date=" & YrDate & "Left(JobID,4)=" & 0001)
Try it and let me know ;-)
Herman

 

[MichaelRed]

I'm sure the stated problem can be solved. I'm not going there. The larger issue is buried in the business rules which permit the generation of duplicate keys in the records. I would FIRST suggest that the 'powers-that-be' change the [Job Id] to a unique value throughout the database. It will resolve your immediate problem and facilliate database maintenance and processing in the general sense.

Knowing the Zero probability of hte above ...

From the statement (" ... but not within 24 hours of ... &quot, it looks like you could form a composite key of the [Job Id] and the DATE portion of the [Start Time]. This would permit you to associate your calculation with the proper [Job id].

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Drepso]

Thanks for your help guys... I will try what you suggested and let you know how I go. I think I am out of my depth here though.
Thanks again