Question about "averages"

[PvL]

Hi guys

If I have a table containing fields:
- employee
- date
- amount of jobs

As different employees work different days and have occasionally off days of course, how would I make a query with the result of a list of the employees and the average amounts of jobs per day?

Guess this is an easy one for you gurus, but I just cant get it to work.
Many thanks for the forthcoming answer(s)

 

[theDBguy]

Hi. Are you able to post some sample data?

 

[Mark_]

You can use DCount for this, depending on how often you need to do this.

WorkDays = DCount("[Table that holds the days your employee works]", "Field", [Employee Work Day] & " Between " & [Start date] & " AND " & [End Date] )
WorkJobs = DCount("[Table that holds the Jobs your employee works]", "Field", [Job Day] & " Between " & [Start date] & " AND " & [End Date] )

AverageJobs = WorkJobs/WorkDays

If this is going to be for multiple employees you can do SUM queries for each and join them to a query on employees. You'd then have an expression that does your calculation.

 

[arnelgp]

same as above:

SELECT DISTINCT employee, 
(SELECT SUM([amount of jobs]) 
FROM yourTableName AS T WHERE T.employee=yourTableName.employee)/
(SELECT COUNT("1") FROM yourTableName AS T WHERE T.employee = yourTableName.employee) 
As AvgJobPerDay FROM yourTableName;