News1 min ago
Access Query
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.
Answers
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
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
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.
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.