averages query not accurate

[hlacroix]

My database has 8 clients. During a sample date range, between 1/1/05 and 1/1/11, they worked a total of 348 hours. I need to query them for hours divided by client by date range. 3 clients, for instance, worked a total of 162 in the sample date range but the query is dividing the 162 hours worked
by all 8 clients. I need it to divide the hours in this case by 3. Needless to say, these numbers will change when a different date range is inputted but if the expressions are correct...
Currently, to calculate this number I am using this expression :
Averages: Sum((Nz([SessionHoursCompleted])+Nz([OptionalHoursCompleted])))/DCount("IDOC","spise clients_OLD_OLD").
A copy of the query 'Current Average/Total' is attached.

Thank you for your assistance.

 

[Malcy]

You need to run a seperate query to identify which suppliers had shifts worked. A sort of clunky way (but always works quickly) is to make a temporary table (tblTmpShiftSuppliers) with supplier ID as a primary index; run a query identifying all shifts worked in the period; append the supplier IDs from this query to the temporary table and then do a DCount("lngSuppId", "tblTmpShiftSuppliers").
This will give you your required answer
You will also need to set up a delete query to clear down tblTmpShiftSuppliers otherwise you will not get reliable results next time you run.
You end up with something like

DoCmd.SetWarnings False
strQryDef = "delTmpShiftSup"
DoCmd.RunQuery strQryDef
DoCmd.SetWarnings True

DoCmd.SetWarnings False
strQryDef = "appTmpShiftSup"
DoCmd.RunQuery strQryDef
DoCmd.SetWarnings True

Then run your average statement.
I am sure there are more elegant ways of doing but this one is easy!!
Good luck