Counting records in a report, Access 2003

[dawnd3]

I am trying to figure out the most graceful way to count records in a report. The report is grouped by school and in the detail section it lists all of the children in that school along with their service levels. Only problem is, sometimes kids have more than one service level and so their name is listed twice. (I know I could have grouped the students too, but that wouldn't work with the format they wanted.) So if I put =Count([lngStudentID]) in the school ID footer it gives me the total number of student entries, even if they are dupes. How do I get it to count the students only once?

Thank you,

Dawn

http://www.calliopeconsulting.net

 

[GingerR]

You could use Dcount to count the records in the underlying query or table. For example:

=Dcount("lngStudentID","TableNameHere")

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[dawnd3]

I tried it with the underlying query name and I got # error. Then I used the student table and got 902 in each footer which is the total amount of students in the entire table.

Thanks,

Dawn

http://www.calliopeconsulting.net

 

[dhookom]

Consider placing the service level information in a subreport and removing it from the main report. This would leave unique students in the main report where you can count them.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dawnd3]

Here is what finally worked...

I put a unbound field in the studentID header called [fldCount] and put =1 for the control source. (even though the student names were in the detail section.) I then changed the Running Sum property to "over group". Then I put another field in the SchoolID footer (since I was counting students per school) and the control source for that field was =[fldCount].

It worked!!!

Thank you for all your replies.

Dawn

http://www.calliopeconsulting.net