Totals query per day per month

[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

 

[Dale Fye]

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