time between shifts

[sarsmelt]

I'm trying to calculate the time between when one shift ends and the next begins.
My table is set up so each shift has it's own row/ID.

My issue is that the data I need are on different lines and I'm not sure how to do this query in Access. I could easily do it in Excel but I need the info in this database.

Any suggestions?

Thanks!

[burrina]

You will need a DateDiff Function. Example:

GrandTotal: DateDiff("n",[StartTime],[EndTime])/60

[June7]

Might also need subquery. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

[sarsmelt]

Might also need subquery. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

I have the datediff function but I can't seem to figure out this subquery
This is the query I have now using the datediff function (in Time1 query).

I need the subquery to take id 4 power_on - id 2 end_pour.

I don't know how to insert my info into that code from the link you gave me.

[burrina]

Okay, here again is an Example: Duration is a calculated expression.

Place this function in a standard module. If you create a new module to do this, name the module anything except ElapsedTime.

Code:
Public Function ElapsedTime(Start As Date, Finish As Date) As String
'Calculates elapsed time between 2 date/times and
'parses it out into Hours-Minutes-Seconds in HH:MM:SS format
Dim HoursLapsed, SecondsLeft, MinutesLapsed, SecondsLapsed As Long

TotalSeconds = DateDiff("s", Start, Finish)

HoursLapsed = Int(TotalSeconds / 3600)

SecondsLeft = TotalSeconds Mod 3600

MinutesLapsed = Int(SecondsLeft / 60)

SecondsLapsed = SecondsLeft Mod 60

ElapsedTime = Format(HoursLapsed, "00") & ":" & Format(MinutesLapsed, "00") & ":" & Format(SecondsLapsed, "00")


End Function

Now, in the Query Grid for your query, create a new, calculated field like this:

Duration: ElapsedTime([power_on],[end_pour])

HTH

[sarsmelt]

Okay, here again is an Example: Duration is a calculated expression.


HTH

I understand the duration calculation but that would calculate the time btw the beginning of the shift to the end of the shift. I need to calculate the time btw shifts (ie: when one shift ends to when the next begins).

[burrina]

If you have one query to get the time difference between the 2 start and end times, then you add that query to a new query as has been suggested and then calculate the difference between the start time and the already calculated difference.
Example:

ShiftDiff: DateDiff("n",[EmployeeTime],[StartTime])/60

Where EmployeeTime is from the 1st query to calculate the difference between the 2 times.

HTH

[sarsmelt]

If you have one query to get the time difference between the 2 start and end times, then you add that query to a new query as has been suggested and then calculate the difference between the start time and the already calculated difference.
Example:
Where EmployeeTime is from the 1st query to calculate the difference between the 2 times.

HTH

I already have the DateDiff Function. I know how long it takes btw the beginning of the shift to the end.

Subtracting these isn't going to help me. I need to be able to pull the end_pour from the 1st id and the power_on from the 2nd id

[sarsmelt]

This is the code I'm using to try and pull the power_on date to the same line as the end_pour date.

SELECT Time1.ID, Time1.End_Pour,
(select dupe.power_on from time1 as dupe where dupe.id=time.id+1 orderby dupe.id) as next_power_on
FROM Time1;

It's saying I have a syntax error in the 2nd select query

[sarsmelt]

I figured it out