Total Hours by Week!?!

[cathalfarrell]

Hi All,

me again, now that I've got over the working hours hurdle I'd like to sum by week.

I'm quite new to access so please excuse my simple questions.

My fields are:

LogID {EmployeeID&Now()}
EmployeeName
StartTime {date&time}
FinishTime {date&time}

I have created a query to give me the total hours worked per day. Is there anyway I can total this by week?

Once again your help is greatly appreciated

 

[DCrake]

Simple Software Solutions

Hi

Working on the assumption you have a field in your table that represents the date.

In your query create a column as such:

WkNo:Format([DateField],"ww")

Group by WkNo
In a second column select the field that contains the value you want to sum and instead of it being a Group By column change it to a Sum type column.

And try that.

David

 

[namliam]

No need to make seperate questions for a follow up question... just attach it to the old one...

When you are in the query design you click on the Sigma sign (looks like an M but sideways)
Now you get an extra line in your query design called the group by function, here you can set stuff like SUM to add up worked hours.
I presume you have someplace a date to convert to a weeknumber as well?
Format ( [datefield] , "ww")
should do that for you.

Regards

 

[fibayne]

Hi
I read your WkNo solution which was just what I was looking for thanks you very much, would you know if this can them be converted to the weekending date of that week number eg wk1 would be 06/01/2008 ??

thanks in advance

Fi

 

[DCrake]

Simple Software Solutions

Now that get a little bit more complicated.

First what you have to do is to create a function that accepts a date and returns a number

vis:

Public Function WeekEnding(AnyDate As Date) As Integer

Next you have to build a select statement that evaluates the day of the week of the date it reads in

vis:
Select Case Format(AnyDate,"ddd")

Case "Mon"

Case "Tue"

etc

End Select

You need to establish which day of the week is the first/last day of the week

And compare this to the day of the date. So if Sunday is the last day of the week and the incoming date is a Friday then you need to add 2 days to the date.

When you have done that you need to calculate the week number of the new date.

But having said that it both dates will return the same week number. If you want to return the last date of the week - the week ending date - then instead of returning the week number change it to return the weekending date

Hope this makes sense.

 

[namliam]

Calculating the week ending date is actually quite easy... if you use the fact that dates are really doubles as well.

Weekday([yourdate]) will return the daynumber you are on.
i.e. Weekday(March 11 2008, 2) = 2

the first 2 is to make the "week" start on monday (in VB you could also use vbMonday).
If you leave it blank or put in 1 (vbSunday in VB), it will return 3.

Now [datefield] - weekday([datefield],2) will return you to last SUNDAY.
Again using today, makes it March 9 2008

Depending on which date you want as your weekending date... add some days to it.
[datefield] - weekday([datefield],2) + 5 = next Friday (March 14)
[datefield] - weekday([datefield],2) + 6 = next Saturday (March 15)
[datefield] - weekday([datefield],2) + 7 = next sunday (March 16)
[datefield] - weekday([datefield],2) + 8 = next Monday (March 17)

Good luck

 

[cathalfarrell]

Still not successfull

Thank you for your responses.

Howver, this still doesn't seem to give me that results I am expecting

I'm probably setting up incorrectly!

I have a table containing the following:

Log ID
Trainer
Today's Date
Start Time
Finish Time

I have created a query adding another column

Total Hours = [finsih time] - [start time] - 30/60/24



This gives me the total hours worked per day

I would like to group by Trainer, showing a total number of hours per week.

 

[Jon K]

Try this query.

SELECT Trainer,
[Todays Date]-Weekday([Todays Date],2)+7 AS WeekendingDate,
Round(Sum([Finish Time]-[Start Time]-#0:30:0#)*24,4) AS WeeklyHours,
Int(Sum([Finish Time]-[Start Time]-#0:30:0#)*24) & " hr " & Format(Sum([Finish Time]-[Start Time]-#0:30:0#),"nn"" min ""ss"" sec""") AS WeeklyTime
FROM [Every 1's Daily Log]
GROUP BY Trainer, [Todays Date]-Weekday([Todays Date],2)+7
ORDER BY [Todays Date]-Weekday([Todays Date],2)+7 DESC;



Notes:
The expression for WeeklyHours returns numeric weekly hours rounded to 4 places of decimal, in case you need the figures in subsequent calculations.

The expression for WeeklyTime returns weekly hours in the format "?hr ?min ?sec", which is a text string.

It's better not to use spaces and special characters in table and field names.
.

 

[namliam]

You seem to be allmost there.

You are however grouping on "todays date" instead of the weekdate...
So replace Todays date by: NextSunday: [Todays date] - weekday([Todays date],2) + 8

Now also you need to remove the start time and finish time from your query, as they are irrelavent to your result...
Now change your "total hours" from "group by" to "Sum"

That should do it...

 

[cathalfarrell]

Great! Thanks!

Thanks for this your code worked a treat.

Eaxctly what I was lookin for.

An just in time 2!

Cheers every1!