Access query question simplification - finding average for each type for every week

[CannotAccess]

Hello!



I’m trying to simplify my query design. As of now I have individual queries that have the criteria for each week that I’m looking for. Is there a way to retrieve average repair times for each repair type for each week of the year without creating a million query’s?

[ranman256]

you make 1 query, and feed it the date range.
in a form put 2 text boxes: txtStartDate, txtEndDate

the query will read them:
select Avg(field) ,Client from table where [DateFld] between forms!fMyForm!txtStartDate and forms!fMyForm!txtEndDate

set the dates in the boxes, then open the query.

[CannotAccess]

And this will work for multiple weeks?

[CJ_London]

if you want multiple weeks, group the week as well

SELECT repairType, datepart("yyyy",repairdate) as Yr, datepart("ww",repairdate) as Week, Avg(repairTime) as avgRepairTime
FROM myTable
GROUP BY repairType, datepart("yyyy",repairdate), datepart("ww",repairdate)

You haven't provided details of your tables or said how you define a week so you may need to adapt this code in some way