Totals query per day per month

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

I want to set up a query to pull together the information I need for a
pivotchart form to display average referrals per school day per month. Each
year the school days in a month will vary so I have set up a table
tblSchoolDays which looks like the following

SchoolMths (text) SchoolDays (Integer)
2008 12 10
2009 01 3
2009 02 20
etc
(I'm in Australia if your thinking the data looks a little strange)

I have another qry (qryAllCharts) that uses various filters to bring back a
record set that has fields

InfractionId
ReferralDate
BMonth: Format([BDate],"yyyy mm")

I think I want a totals query based on qryAllCharts and tbleSchoolDays
joining BMonth and SchoolMths with an expression something like
AverageInfraction: Round(Count([InfractionID])/[SchoolDays],2)

but at this point I'm quite lost. When I make the join nothing is returned
in the recordset
 
What does your SQL currently look like?

You are going to have to aggregate your qryAllCharts before you link it to
tblSchoolDays. You can do this in a separate query, or in a subquery. The
subquery version might look like:

SELECT S.SchoolMths,
Round(T.Infractions/S.SchoolDays), 2) as AvgInfr
FROM tblSchoolDays as S
LEFT JOIN (SELECT BMonth, Count(InfractionID) as Infractions
FROM qryAllCharts
GROUP BY BMonth) as T
ON S.SchoolMths = T.BMonth
 
Back
Top