Count unique records (Access 2003)

Topic

New Reply

I have a report with classes and students. In the report footer I would like to show a total of classes and a total of students. No problem for the students but the counting of how many classes were taught does not give the right result – it counts the same number as in students.

Any idea on how to handle this? Thanks

Reply | Quote

Replies

Edited by HansV to correct control source to =1 (I had -1 originally)

If your report is grouped by class, you can do it as follows:
– Put a text box in the group header or footer. It can be hidden (Visible = No)
– Set the Control Source property of the text box to =1.
– Set the Running Sum property to Over All.
– Name it (for example) txtGroupCount.
– Put a text box in the report footer.
– Set its Control Source property to =[txtGroupCount]
– This text box will display the number of classes (=groups).

If your report is not grouped by class, you will need an extra query.

Reply | Quote

Thanks Hans that worked great. However, it was best to set the first control to 1 not -1, although I used the abs function in the report footer control.
Thanks again!

Reply | Quote

Thanks Hans that worked great. However, it was best to set the first control to 1 not -1, although I used the abs function in the report footer control.
Thanks again!

Reply | Quote

Oops, I meant =1 instead of -1. Sorry about that. I will correct my original reply.

Reply | Quote

Oops, I meant =1 instead of -1. Sorry about that. I will correct my original reply.

Reply | Quote

Edited by HansV to correct control source to =1 (I had -1 originally)

If your report is grouped by class, you can do it as follows:
– Put a text box in the group header or footer. It can be hidden (Visible = No)
– Set the Control Source property of the text box to =1.
– Set the Running Sum property to Over All.
– Name it (for example) txtGroupCount.
– Put a text box in the report footer.
– Set its Control Source property to =[txtGroupCount]
– This text box will display the number of classes (=groups).

If your report is not grouped by class, you will need an extra query.

Reply | Quote