Sum by week

[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

 

[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

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.

 

[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

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.

 

[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.

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.

 

[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