Sum by week

J

Jasper Recto

I have a query that gives me the hours worked by each employee everyday.

I have it sorted by day and I want it to sum the total hours worked each
week (Sunday - Saturday).

How would I accomplish that?

Thanks,
Jasper
 
L

louisjohnphillips

I have a query that gives me the hours worked by each employee everyday.

I have it sorted by day and I want it to sum the total hours worked each
week (Sunday - Saturday).

How would I accomplish that?

Thanks,
Jasper
Click to expand...

The format() function can be called:

WeekOfYear = format( WorkDate, "ww", vbSunday, vbFirstJan1)

to return the week of the year.

Therefore, a query could be written:

SELECT format( WorkDate, "ww", vbSunday, vbFirstJan ) as WeekOfYear,
EmployeeID, sum( HoursWorked )
from WeeklyHours
group by format( WorkDate, "ww", vbSunday, vbFirstJan ) , EmployeeID;

That would yield the desired sum.
 
J

Jasper Recto

what is the vbSunday and vbFirstjan1?

Thanks,
Jasper
I have a query that gives me the hours worked by each employee everyday.

I have it sorted by day and I want it to sum the total hours worked each
week (Sunday - Saturday).

How would I accomplish that?

Thanks,
Jasper
Click to expand...

The format() function can be called:

WeekOfYear = format( WorkDate, "ww", vbSunday, vbFirstJan1)

to return the week of the year.

Therefore, a query could be written:

SELECT format( WorkDate, "ww", vbSunday, vbFirstJan ) as WeekOfYear,
EmployeeID, sum( HoursWorked )
from WeeklyHours
group by format( WorkDate, "ww", vbSunday, vbFirstJan ) , EmployeeID;

That would yield the desired sum.
 
L

louisjohnphillips

what is the vbSunday and vbFirstjan1?

Thanks,






The format() function can be called:

    WeekOfYear = format( WorkDate, "ww", vbSunday, vbFirstJan1)

to return the week of the year.

Therefore, a query could be written:

SELECT format( WorkDate, "ww", vbSunday, vbFirstJan ) as WeekOfYear,
EmployeeID, sum( HoursWorked )
from WeeklyHours
group by format( WorkDate, "ww", vbSunday, vbFirstJan ) , EmployeeID;

That would yield the desired sum.
Click to expand...

You can specify which day of the week and which day of the year to
start counting.

That way you can count your weeks from a calendar or fiscal year.
Further, your week can start on Monday or Sunday.
 
M

Michel Walsh

They are both VBA constants. In a query, you have to use the value of the
constant rather than their VBA name. To find their value, you can use the
Browser (F2), or simply use the Immediate Debug Window:

? vbSunday, vbFirstJan1
1 1


which tell you that their value is one, for each of these two names.


Vanderghast, Access MVP
 
Top