Donate SIGN UP

Access Query

Avatar Image
spotit3 | 21:31 Sun 10th May 2009 | Computers
6 Answers
Any Access experts out there? I have a list of people's names in a table, some of them repeated quite a few times. I have been trying to run a query to count the number of people but I can't figure out how to so it without counting all the duplicates. I have been using 'group as' but have tried every variation I can think of without any joy. Any help would be gratetfully received.
Gravatar

Answers

1 to 6 of 6rss feed

Best Answer

No best answer has yet been selected by spotit3. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.
I dont use Access, but often in SQL you use an option like DISTINCT to find unique items.

A search round the internet makes me think this might be called Count(Distinct) in Access

http://blogs.msdn.com/access/archive/2007/09/1 9/writing-a-count-distinct-query-in-access.asp x
I did answer this earlier but my append got lost, aso lets try again.

I think you need to use Count(Distinct)
Use GROUP BY on the name and see how many records there are at the bottom of the query window - that will be the number of differently named people you have.
This is a dodgy way to do it since you may have spelling mistakes and get a wrong count. A better way would be to give everyone a unique number (membership number) and do a similar query on the unique number.
Try Count(Distinct)
As far as I know count(distinct ....) is not available in Access despite being part of the SQL standard.
An alternative to using my GROUP BY solution above is to create a normal select query on name and then edit the query in SQL mode so that it says SELECT DISTINCT .....
Question Author
Thanks for all your help. I thought no-one had answered since I didn't get the usual email from AB re answers. Do they not semd out emails any longer when someome answers your question.

1 to 6 of 6rss feed

Do you know the answer?

Access Query

Answer Question >>