DCount Problem

[jax]

What I have is a report which is grouped on Requested School, giving one page for each school. The report details pupils and what school year they are in.

What I am trying to do is count how many pupils, for each individual school, are in each class year. The code I have used is below, it works but it is counting every class 1 occurance in the whole database. I have put the code in the "Requested School" footer.

=DCount("[Requested School]","tblPersonalDetails","[Year] = '1'")


Can anyone help?

 

[Pat Hartman]

You are not qualifying the selection criteria with the school name. Try something like:

=DCount("[Requested School]","tblPersonalDetails","[Year] = '1' AND [School] = '" & [School] & "'")

A far more efficient method is to create a crosstab query and a subreport.

Create a crosstab that counts the data for each school by year. Use that query as the RecordSource for a report. Place the new report as a subreport in the school footer. Make sure that the master/child links are properly set so the subreport stays sync'd with the main report.