Updating Table data with data from preceeding entry??

[saebodybuilder]

Hi,

I'll start off by saying I've been reading this forum for a while and while I'm only a novice at access programming, you can learn tons from this place.

My question is this: I have a table where I'm entering employees' hours worked. Basically, it's something like this:

ID WorkerNumber Dateworked TimeStart TimeEnded
1 2 1/2/2013 10:00:00 AM 3:00:00 PM
2 2 1/3/2013 2:00:00 AM 11:00:00 AM
3 2 1/4/2013 12:15:00 AM 11:30:00 AM
4 2 1/5/2013 10:25:00 PM 11:00:00 AM
5 3 1/2/2013 11:00:00 AM 3:30:00 PM
6 3 1/3/2013 12:00:00 PM 10:00:00 PM
7 3 1/10/2013 7:00:00 AM 4:00:00 PM

I have a query that (easily) determines how many hours an employee has worked on any given day. What I can't figure out at all, is how to write a query that can figure out how much time an employee had off in between shifts.

Thus far I'm able to run a query that separates this main table into individual workers by their id numbers, but can't figure out how to determine time off between shifts - as the last hour worked one day, and the first hour worked the next day are on two different lines (they are two different table entries).

Anyone care to help? I'm sure there has to be an easy way but I've spent hours playing and searching and I've gotten nowhere.
Thanks

 

[JHB]

Is this what you wanted?

Remember to change table name and field name to fit yours.

SELECT ID, WorkerNumber, Dateworked, TimeStart, TimeEnded, Format([TimeOffInMinuts]\60,"00\:") & Format([TimeOffInMinuts]-(([TimeOffInMinuts]\60)*60),"00") AS TimeOffInHourMinuts, DateDiff("n",(SELECT TOP 1 [Dupe.Dateworked] & " " & [Dupe.TimeEnded] FROM WorkingDateAndHour AS Dupe WHERE Dupe.WorkerNumber = WorkingDateAndHour.WorkerNumber AND Dupe.Dateworked< WorkingDateAndHour.Dateworked ORDER BY Dupe.Dateworked DESC, Dupe.WorkerNumber),[Dateworked] & " " & [TimeStart]) AS TimeOffInMinuts
FROM WorkingDateAndHour;

 

[saebodybuilder]

Wow, well that absolutely does everything that I was hoping. Thanks! Now for the difficult part: me understanding what the heck is going on. Here is my code and what I seem to think it's doing. Keep in mind I'm a SQL beginner:

SELECT TripInfo.DriverNumber, TripInfo.DateOfWork, TripInfo.TimeStart, TripInfo.TimeEnd,

This defines the first 4 columns I'd like in my output table.

Format([TimeoffInMinutes]\60,"00\:") & Format([TimeOffInMinutes]-(([TimeOffInMinutes]\60)*60),"00") AS TimeOffInHourMinutes,

This is the 5th value column of my table, time between in hours, which is derrived from the TimeOffInMinutes Column which isn't defined yet. Just takes the total minutes, divides that into hours, then takes the remainder and turns that into minutes.

DateDiff("n",

This calls the datediff function which counts minutes between the next two arguments.

(SELECT TOP 1 [Dupe.DateOfWork]& " " & [Dupe.TimeEnd]
FROM TripInfo AS Dupe WHERE Dupe.DriverNumber = TripInfo.DriverNumber
AND Dupe.DateOfWork<TripInfo.DateOfWork ORDER BY Dupe.DateOfWork DESC, Dupe.DriverNumber),

This is the part where I have the most confusion. This is the first argument to the datediff function and it's final output is the Time End from the previous day.

Firstly I'm a little confused over the & " " & syntax that I see here.

It looks like it's creating temp table (DUPE) and inserting into it the columns Date of Work and Time End. Then it's somehow comparing dupe.drivernumber to tripinfo.drivernumber ( I didn't think dupe.drivernumber was even declared here), and then it checks whether the day of work is less than the trip.info day of work. itf it is less it returns the first value, sorted Descending.

[DateOfWork] & " " & [TimeStart])

This is the final value of the Datediff function, it's the start time for the day.

AS TimeOffInMinutes

FROM TripInfo;

Identifies the name of the final column on the table and closes the initial SELECT function.


My biggest question is How does the select statement go through and compare these two tables, since they are the same? Am I correct in saying there is a duplicated table (DUPE) that the values are being compared to/taken from?

Thanks for the help.

Regardless, JHB many thanks for determining what seemed to be an easy fix for you. This might have taken me months to figure out. Now if I can just figure it out completely I'd be all set..

 

[Galaxiom]

This could be simplified:
[TimeOffInMinutes]-(([TimeOffInMinutes]\60)*60)

to

[TimeOffInMinutes] MOD 60

 

[JHB]

SELECT TripInfo.DriverNumber, TripInfo.DateOfWork, TripInfo.TimeStart, TripInfo.TimeEnd,
This defines the first 4 columns I'd like in my output table.

Correct

Format([TimeoffInMinutes]\60,"00\:") & Format([TimeOffInMinutes]-(([TimeOffInMinutes]\60)*60),"00") AS TimeOffInHourMinutes,
This is the 5th value column of my table, time between in hours, which is derrived from the TimeOffInMinutes Column which isn't defined yet. Just takes the total minutes, divides that into hours, then takes the remainder and turns that into minutes.

Correct

[Dupe.DateOfWork]& " " & [Dupe.TimeEnd]
Firstly I'm a little confused over the & " " & syntax that I see here.

It takes the date and time part and put it i one column and seperate it with a space, (else it wouldn't be a date-time value).

It looks like it's creating temp table (DUPE) ...

It create an alias for table TripInfo.

[FONT=&quot]Try to Google for Sub query to get an[/FONT][FONT=&quot] idea[/FONT][FONT=&quot] what is going on. The trick is the "SELECT TOP 1" and how it is sorted "DESC".[/FONT]