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