A Tuff one 1

[InfoNow]

I have a table with the following fields:
StartTime, EndTime, and Total.
The StartTime and EndTime are formated so the Date and Time are recorded like this "10/06/03 5:45:00 AM."
When the staffs starts to work, they would enter the StartTime and at the end of the shift, they would enter the EndTime and the total units of work that they performanced. So over time you would see something like this in the table:
StartTime EndTime Total
10/06/03 5:45:00 AM 10/06/03 10:00:00 AM 150
10/06/03 10:00:00 AM 10/06/03 8:30:00 PM 202
10/07/03 6:30:00 PM 10/08/03 2:30:00 AM 125

What I need is to have a query running that will give me a total number of hours worked between the StartTime and EndTime. It must also give me the total number of hours worked on that day and the accumulated Total for that day. So for 10/06/03, it would give the total number of hours for both entries and a accumulated total of 352.

 

[GHolden]

Hi, the first thing I've noticed is that start and end times appear to go across days.

ie. the third row of data

10/07/03 6:30:00 PM 10/08/03 2:30:00 AM 125

starts on the 7th and ends on the 8th so what logic would you like to use for calculating the totals for each of these days?




There are two ways to write error-free programs; only the third one works.

 

[InfoNow]

Hi, thank you for looking in to this for me.
The third row does crosses over to a new day, but I would like it to just use the start time as the day for the accumulation calculations.

 

[GHolden]

Hi,

I think this will do it...

SELECT Format(StartTime,"dd/mm/yyyy&quot AS DateWorked,
Sum(DateDiff("h",tblUnits.StartTime,tblUnits.EndTime)) AS HoursWorked,
Sum(tblUnits.Total) AS SumOfTotal
FROM tblUnits
GROUP BY Format(StartTime,"dd/mm/yyyy"

You will have to substitute tblUnits for your table name. The other problem is that DateDiff will round your hours down to the nearest hour. You could fix this with a more complex DateDiff using Minutes and dividing to get fractional hours.

Hope this helps...



There are two ways to write error-free programs; only the third one works.

 

[GHolden]

Oh, another thing, I've done the date formatting as UK format, you'll have to change to "mm/dd/yyyy" in both format statements.

There are two ways to write error-free programs; only the third one works.

 

[InfoNow]

That works beautifully!!! Thank you so much. You are a STAR!!

 

[InfoNow]

ooppss...I forgot to ask one more thing.
How would I have the query ask the user for a range of date to display the data for?
Something like "Enter Start Date" and "Enter End Date" and the query would only returns data for that range of date(s).

Thanks again!

 

[GHolden]

Hi,

What you need to do is...

Add this as the first line of the SQL in the Query:

PARAMETERS StartDate DateTime, EndDate DateTime;

Then add this line between the FROM line and the GROUP BY line:

WHERE (Format([StartTime],&quot;dd/mm/yyyy&quot >=[StartDate] And Format([StartTime],&quot;dd/mm/yyyy&quot <=[EndDate])

Now when you run the query you should be prompted for a Start and End date.



There are two ways to write error-free programs; only the third one works.

 

[GHolden]

Sorry,

You will again have to change the date format to &quot;mm/dd/yyyy&quot; for US dates.



There are two ways to write error-free programs; only the third one works.

 

[InfoNow]

Got it!
Thank you so much.

 

[GHolden]

No problems.

Have a good day

There are two ways to write error-free programs; only the third one works.