By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,608 Members | 1,940 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,608 IT Pros & Developers. It's quick & easy.

Counting distinct Surnames on report

P: n/a
I have a report which has records of students in various courses. It would
normally show say 400 entries for 80 students doing an average of 5 courses
each. I want to say on the report how many different students there are.
Normally, I'd just use "No. Students = " & Count([Surname]) , but this just
gives me 400 - how do I do a count of Distinct Surnames on a report?

dixie
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Create a query that returns the distinct surnames. Then use DCount on that
query.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"dixie" <di****@dogmail.com> wrote in message
news:VK**************@nnrp1.ozemail.com.au...
I have a report which has records of students in various courses. It would normally show say 400 entries for 80 students doing an average of 5 courses each. I want to say on the report how many different students there are.
Normally, I'd just use "No. Students = " & Count([Surname]) , but this just gives me 400 - how do I do a count of Distinct Surnames on a report?

dixie

Nov 13 '05 #2

P: n/a
Hi Dixie,

In Help, look up TOP. You can create your query using the QBE format but
you must go to SQL view to add "TOP 1".

Note that I'd change the query to return Last, First, Middle in order to
catch repeating surnames. Otherwise you'd count all of the Smiths as one
person. :-)

HTH
--
-Larry-
--

"dixie" <di****@dogmail.com> wrote in message
news:VK**************@nnrp1.ozemail.com.au...
I have a report which has records of students in various courses. It would normally show say 400 entries for 80 students doing an average of 5 courses each. I want to say on the report how many different students there are.
Normally, I'd just use "No. Students = " & Count([Surname]) , but this just gives me 400 - how do I do a count of Distinct Surnames on a report?

dixie

Nov 13 '05 #3

P: n/a
Not sure how having TOP 1 is going to give the number of distinct Names.

And you don't actually have to go to the SQL view to set the TOP 1. Open the
query's Properties. One of the properties is Top (3rd one one down in Access
97, 4th one in Access 2003: don't have any other versions of Access
installed on this machine)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Larry Daugherty" <La********************@verizon.net> wrote in message
news:vv***************@nwrddc03.gnilink.net...
Hi Dixie,

In Help, look up TOP. You can create your query using the QBE format but
you must go to SQL view to add "TOP 1".

Note that I'd change the query to return Last, First, Middle in order to
catch repeating surnames. Otherwise you'd count all of the Smiths as one
person. :-)

HTH
--
-Larry-
--

"dixie" <di****@dogmail.com> wrote in message
news:VK**************@nnrp1.ozemail.com.au...
I have a report which has records of students in various courses. It

would
normally show say 400 entries for 80 students doing an average of 5

courses
each. I want to say on the report how many different students there are. Normally, I'd just use "No. Students = " & Count([Surname]) , but this

just
gives me 400 - how do I do a count of Distinct Surnames on a report?

dixie


Nov 13 '05 #4

P: n/a
If I understand what you're trying to achieve this might do it?

SELECT surname, Count([studentID]) AS numberOfStudents
FROM tableName
GROUP BY surname

HTH

Simon.

"dixie" <di****@dogmail.com> wrote in message
news:VK**************@nnrp1.ozemail.com.au...
I have a report which has records of students in various courses. It would normally show say 400 entries for 80 students doing an average of 5 courses each. I want to say on the report how many different students there are.
Normally, I'd just use "No. Students = " & Count([Surname]) , but this just gives me 400 - how do I do a count of Distinct Surnames on a report?

dixie



Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.