Access Query

[spotit3]

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.

[VHG]

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

[VHG]

I did answer this earlier but my append got lost, aso lets try again.

I think you need to use Count(Distinct)

[vascop]

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.

[VHG]

Try Count(Distinct)

[vascop]

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 .....

[spotit3]

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.