Totals query per day per month



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
(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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question