Calculate elapsed time

[Timothy.Rybak]

I have a two tables that represent the start of production and the end
of production. The tables are linked by a serial number that is unique
to each part.

The tables are collecting the date and time that each serial number
passes through.

So I have:

Table: tP02
Serial / Date / Time
12345 / 04/24/2006 / 08:35:30 AM

Table: tP39
Serial / Date / Time
12345 / 04/25/2006 / 01:22:10 AM

I need to create a query that will tell me the elapsed time in days,
hours, minutes and seconds from when the part started production until
it ended production.

Can someone help, or at least point me in the right direction for more
help?

Tim

 

[Guest]

Tim,

The problem with what you want to do is how to format it so that you can use
the data in some meaningful way later on. You could format it so that you
track Days, Hours, minutes, and seconds, each in it's own field of the query.
Take a look at the DateDiff function and play with your results. If you are
going to want to find the average time and standard deviation for production
of a particular item, you will probably need to use the second ("s")
parameter in the function, and then convert those seconds into hours and
minutes.

HTH
Dale

 

[Marshall Barton]

I have a two tables that represent the start of production and the end
of production. The tables are linked by a serial number that is unique
to each part.

The tables are collecting the date and time that each serial number
passes through.

So I have:

Table: tP02
Serial / Date / Time
12345 / 04/24/2006 / 08:35:30 AM

Table: tP39
Serial / Date / Time
12345 / 04/25/2006 / 01:22:10 AM

I need to create a query that will tell me the elapsed time in days,
hours, minutes and seconds from when the part started production until
it ended production.

I think this will do what I think you want??

SELECT tP02.Serial,
tP02.Date + tP02.Time As Start,
tP39.Date + tP39.Time As End,
DateDiff("s", Start, End) As Secs,
Secs \ (24 * 3600) & " " & (Secs \ 3600) Mod 24 &
Format((Secs \ 60) Mod 60, "\:00") &
Format(Secs Mod 60, "\:00") As Elapsed
FROM tP02 INNER JOIN tP39
ON tP02.Serial = tP30.Serial

 

[John Spencer]

SELECT tPO2.Serial
, DateDiff("s",tPO2.[Date] + tPO2.[Time], tP39.[Date] + TP39.[Time]) as
NumSeconds
FROM tPO2 INNER JOIN tP39
ON tPO2.Serial = tP39.Serial

In a report you can use num seconds and some math to get the days, hours,
minutes and seconds. You might check out the "More Complete DateDiff
Function" Graham Seach and Doug Steele wrote.

http://www.accessmvp.com/djsteele/Diff2Dates.html

You specify how you want the difference between two date/times to be
calculated by providing which of ymdhns (for years, months, days, hours,
minutes and seconds) you want calculated.

For example:

?Diff2Dates("y", #06/01/1998#, #06/26/2002#)
4 years
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#)
4 years 25 days
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
4 years 0 months 25 days
?Diff2Dates("d", #06/01/1998#, #06/26/2002#)
1486 days

?Diff2Dates("h", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours
?Diff2Dates("hns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours 47 minutes 33 seconds
?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
1 day 18 hours 47 minutes 33 seconds

?Diff2Dates("ymd",#12/31/1999#,#1/1/2000#)
1 day
?Diff2Dates("ymd",#1/1/2000#,#12/31/1999#)
-1 day
?Diff2Dates("ymd",#1/1/2000#,#1/2/2000#)
1 day