Running an Access query on a FILTERED table-CodePudding

I have some related tables that I want to run a Totals/Group By query on.

My "Tickets" table has a field called "PickDate" which is the date that the order/ticket was fulfilled.

I want to group by the weekday (name) (a calculated field) so that results for certain customers on the same day of the week are grouped. Then the average ticket completion time can be calculated per customer for each weekday. It would look something like the following.

--CustName---Day---AvTime

Customer 1 - MON - 72.3
           - TUE - 84.2
           - WED - 110.66
           ..etc
           ..etc
           ..etc
Customer 2 ..

This works fine but the problem I am having is that when this query is run, it works on every record from the tickets table. There are some reasons that, for certain reports, the data that it the query is referencing should be restricted between a date range; for example to track a change in duration over a number of weeks.

In the query properties, there is a property, "filter", to which I can add a string such as:

"([qryCustomerDetails].[PickDate] Between #11/1/2021# And #11/14/2021#)"

to filter the results. The only issue is that since each date is unique, the "group by" of like days such as "Monday" is overridden by this unique date "11/1/2021". The query only works when the PickDate is removed as a field. However, then I can't access it to filter by it.

What I want to achieve would be the same as; in the "Tickets" table itself filtering the results between two dates and then having a query that could run on that filtered table.

Is there any way that I could achieve this?

For reference here is the SQL of the query.

FROM tblCustomers INNER JOIN tblTickets ON tblCustomers.CustomerID = tblTickets.CustomerID
GROUP BY tblCustomers.Customer, WeekdayName(Weekday([PickDate]),False,1), tblCustomers.Round, Weekday([PickDate])
ORDER BY tblCustomers.Round, Weekday([PickDate]);

CodePudding user response：

You probably encountered two issues. The first issue is that to filter results in a totals query by un totaled fields you use HAVING rather than WHERE. the second issue is that calculated fields like Day don't exist at the time of the query. You can't say having Day > Mon. Instead you must repeat the calculation of Day: Having CalculateDay(PickDate) > Monday The designer will usually figure out whether you want having or where automatically. So here is my example:

this gives you the SQL:

SELECT Tickets.Customer, WeekdayName(Weekday([PickDate])) AS [Day], Avg(Tickets.Time) AS AvTime
FROM Tickets
GROUP BY Tickets.Customer, WeekdayName(Weekday([PickDate])), Tickets.PickDate
HAVING (((Tickets.PickDate) Between #11/16/2021# And #11/17/2021#))
ORDER BY Tickets.PickDate;