Finding first start time and last end time in multiple records

[Biscuit]

Hi everyone. First time here so excuse anything that looks a bit stupid. I can't help that!!!

I'm working on a summarized time-sheet based on a number of workers who do several jobs during the day. For example:

Worker Fred sees client Jean at 9am for 2 hours.
He then sees Joan at 11.30 for 1 hour
He sees Mary at 2pm for 2 hours - finishes the day at 4pm

I know how to find the end time per job but that's where I get stuck.

He's worked 5 hours but from start time to end time is 7 hours and that's what I need to calculate and pay him. Any idea of how I can write a query to show that? This is doing my head in.....

 

[recyan]

How is the working hours data stored in the table/s for Fred?

Thanks

 

[Biscuit]

Thanks for taking the time to have a look at this.

I've got fields in my query as follows:

Date: Date Field
Time: Med Time format
Duration: recorded as # hours worked
Minutes: [Duration]*60
ConvertMins: [Minutes]*0.000694
EndTime: ([time]+[Convertmins]+0.000694)


So for each record I've got a start time, duration and a calculated end time, with multiple records in any one day.



I ended there by more luck than judgement. That make sense?

 

[recyan]

Was not able to get how the data is stored in the tables.

However, as an example :

mytable
WorkerName
Date
Time
Duration

You can try writing a query that Groups on WorkerName, Date and Sums on Duration.

Am a bit puzzled as to why Duration is stored & End time is calculated & not the other way round.

Thanks

 

[Biscuit]

I can attach the table design if there is a way to do that?

Basically it's a roster which pulls up a client from a list, a worker from a list and then combines the two into a job. Because jobs are listed as "Archived"(they've bee rolled over or "Current" (i.e.not yet archived) I've combined the two lots into one temporary table so that all jobs can be identified based on a date range.

This database has a start time and a duration for each job but no defined end time as such.

I appreciate your help with this. Believe me I need it. My knowledge of Access is based on making up databases in different jobs because there weren't any. Can write macros and some queries but can't program to save my life. This one is a database already here and I'm modifying it to suit.

thanks

 

[recyan]

No need for the database.
You have not replied about the suggested line of query, whether it will work or not work (if not - why not).

Basically what I have understood is :
you want the result of the query as below :

EmployeeName    Date    SumOfHoursWorked
Fred    1/22/2012    4
Guido    1/22/2012    6
Fred    1/23/2012    6
Guido    1/23/2012    3

If yes, then all you need to do is look at the table/s which will provide the source data for this result & then design the query.

If you are facing problem in doing this, just post the relevant source tables with a few dummy data thrown in (the way I have done for the query result) & the query result that you want out of it. I am sure someone should be able to help you along.
In case you have not, try fiddling around with your queries in the Design window.

Thanks

 

[Biscuit]

Sorry - wasn't being specific enough.

summing the total hours for the jobs is not a problem. I know if Fred has 5 jobs, each lasting an hour then the job total is 5 hours, and I ca get that for Harry and Dick as well.

Trouble is, Fred gets paid from the time he starts his first job to the time he leaves the last one. This includes travel and other down time.

The table says per job:

Job # Auto number
Worker: Fred
Date: 24/1/12
Job Start time 9am
Duration (in hours) 1.5 (hours)

Fred might have 5 jobs on that day of differing durations and differing travel and down time. A total of the duration is less that the time between start time of the first job and the end time of the last job. I've got a query which shws me Fred's start time on 24th and a separate query on the finish time of Fred's last job but when I try and combine them to get Fred's start time of his first job and Fred's finish time of his last job I get some weird results. I need the number of elapsed time between his start time and when he finishes his last job. Can't seem to fix it.

Does that help - clear as mud?

 

[Biscuit]

Don't know if this helps:




Calculated Job # Worker Start time Duration end time 1 Fred 9:00 1.5 10.30 2 Fred 11:00 1 12.00 3 Fred 12:30 2 2.30 4 Fred 3:00 1 4.00


5.5





Total Duration 5.5 Total Time on road 9am to 4pm 7.00




I need to pay Fred 7 hours

sigh

 

[Biscuit]

hmm - lost the formatting

 

[Biscuit]

Lets try this

 

[recyan]

Lets try this

Now, I think I have understood, what you want.

Just see if below gives some guidelines :

The table :

tblJobs
JobID
Worker
JobDate
JobStartTime
Duration_In_Hours

Am assuming that Duration_In_Hours has been stored as a decimal (eg: 1, 1.5, 1.25, 1.75, etc).

The sub-queries :

qryJobEndTime

SELECT 
    tblJobs.JobID, 
    tblJobs.Worker, 
    tblJobs.JobDate, 
    tblJobs.JobStartTime, 
    tblJobs.Duration_In_Hours, 
    Int([Duration_In_Hours])*60 AS DurationHoursInMin, 
    [Duration_In_Hours]-Int([Duration_In_Hours]) AS DurationDecimalPart, 
    [DurationDecimalPart]*60 AS DurationDecimalPartInMinutes, 
    [DurationHoursInMin]+[DurationDecimalPartInMinutes] AS TotalDurationInMinutes, 
    DateAdd("n",[TotalDurationInMinutes],[JobStartTime]) AS Job_End_Time
FROM 
    tblJobs;

qryTotalDurationDateStartTimeEndTime

SELECT 
    qryJobEndTime.Worker, 
    qryJobEndTime.JobDate, 
    Sum(qryJobEndTime.TotalDurationInMinutes) AS SumOfTotalDurationInMinutes, 
    Min(qryJobEndTime.JobStartTime) AS MinOfJobStartTime, 
    Max(qryJobEndTime.Job_End_Time) AS MaxOfJob_End_Time
FROM 
    qryJobEndTime
GROUP BY 
    qryJobEndTime.Worker, 
    qryJobEndTime.JobDate;

The final query to run :

qryTotalDurationTotalStartToEndTimeSpent

SELECT 
    qryTotalDurationDateStartTimeEndTime.Worker, 
    qryTotalDurationDateStartTimeEndTime.JobDate, 
    qryTotalDurationDateStartTimeEndTime.SumOfTotalDurationInMinutes, 
    qryTotalDurationDateStartTimeEndTime.MinOfJobStartTime, 
    qryTotalDurationDateStartTimeEndTime.MaxOfJob_End_Time, 
    DateDiff("n",[MinOfJobStartTime],[MaxOfJob_End_Time]) AS TotalTimeStartToEndinMinutes
FROM 
    qryTotalDurationDateStartTimeEndTime
ORDER BY 
    qryTotalDurationDateStartTimeEndTime.Worker, 
    qryTotalDurationDateStartTimeEndTime.JobDate;

Am sure above is overly complicated. You should be able to simplify things if it gives some guidelines.

Thanks

 

[Biscuit]

wow - thanks very much for putting so much effort into solving my problem. I've very grateful.

I'll have a play with it over the next couple of days and see what happens.

thanks so much

 

[Biscuit]

Thanks a lot recyan. I built the queries and I got exactly the result I wanted. I'd have never got there by myself. You are a legend .

 

[Biscuit]

Is there a "Thank You" button anywhere? If so I'd press it.

Thank you

 

[recyan]

Is there a "Thank You" button anywhere? If so I'd press it.

Thank you

I really do not know. New here as well.
All the same, glad you got things going.
But just test things thoroughly.

Thanks

 

[Biscuit]

will do - all good