Average week

[Harry]

I have a table, StaffHours with that stores what employees
worked what hours on what projects. The relevant fields
are StaffID and Hours.
I need to run a query that finds the average number of
hours each day an employee worked, and the average number
of hours the employee worked each week.
Any ideas how to do this? Thanks-- Harry

 

[John Spencer (MVP)]

Do you have a Date field in this table? What does Hours record - the number of
hours on a particular day or something else? Do you want this by project by employee?

You will need to be clearer on your needs if we are to help you.

Also, do you need this in a query or do you really want to show it in a report?
It is sometimes a lot simpler to show averages in a report, especially when you
want more than one type (daily, weekly) of average in your output.

 

[Harry]

I understand how to handle the daily average:
construct a query that counts the number of days each
employee works, at the same time it sums the total number
of hours. Then divide the number of hours by count of days.
What I can't see is how to break it out by week.
Sometimes they work Saturdays, so it isn't necessarily a
five day week. -- Harry

 

[John Spencer (MVP)]

TRY The following UNTESTED SQL statements. I think they will work

Daily Average for each employee
SELECT [StaffId], Avg([Hours]) as AvgDaily Hours
FROM YourTable
WHERE [Date] Between [SomeStartDate] and [SomeEndDate]
GROUP BY [StaffID]

Weekly Average for Each Employee is a bit more complex.

Save following as qryOne:
SELECT [StaffId],
DateAdd("d",1-Weekday([Date]),[Date]) as WeekStart,
Sum(Hours) as TotalHours
FROM YourTable
WHERE [Date] Between [SomeStartDate] and [SomeEndDate]
GROUP BY StaffID, DateAdd("d",1-Weekday([Date]),[Date])

Use qryOne as source for qryTwo:
SELECT StaffID, Sum(TotalHours)/Count(WeekStart) as WeekAvg
FROM qryOne
GROUP BY StaffID

 

[Harry]

Thanks, John. The Daily Average worked fine. There was
trouble with the weekly average, but let's try this from a
different perspective.
How would I count the number of weeks an employee
worked between [StartDate] And [EndDate]?
Not everyone works every week, but if I have a count
of their weeks I can divide their hours to work out their
averages.
I'll also post this as a fresh question to the list,
since it's different from finding the average.

-----Original Message-----
TRY The following UNTESTED SQL statements. I think they will work

Daily Average for each employee
SELECT [StaffId], Avg([Hours]) as AvgDaily Hours
FROM YourTable
WHERE [Date] Between [SomeStartDate] and [SomeEndDate]
GROUP BY [StaffID]

Weekly Average for Each Employee is a bit more complex.

Save following as qryOne:
SELECT [StaffId],
DateAdd("d",1-Weekday([Date]),[Date]) as WeekStart,
Sum(Hours) as TotalHours
FROM YourTable
WHERE [Date] Between [SomeStartDate] and [SomeEndDate]
GROUP BY StaffID, DateAdd("d",1-Weekday([Date]),[Date])

Use qryOne as source for qryTwo:
SELECT StaffID, Sum(TotalHours)/Count(WeekStart) as WeekAvg
FROM qryOne
GROUP BY StaffID

Sorry for being so terse. Let's try that again. The data
is held in the table StaffHours. It contains the following
fields: StaffID, Date, Hours. (There's a projectID and
other whatnot, but that's not important here.
So a given record would cover who (StaffID), worked
on what day (Date), how many hours (Hours).
It doesn't really matter what they worked on. It's
more about how to average out the number hours they tend
to work in a week.
The underlying point is this: One department has a
number of different employees, some working full time,
part time and three- quarters time. They may also switch
back and forth, with more time or less.
Their vacation days are compensated by an average of
how much they've worked in the past six months. The
managers want a weekly average (for vacation pay) and a
daily average (for personal days).
It's easy to find the overall average; the question is
how to break out the week timeframe.
The results can be in a report or a query, either
way. Thanks-- Harry
Hours
record - the number of want
this by project by employee?

.