Selecting count of distinct items

[Paul Brown]

I have a single table containing information about people such as forenames,
surnames, age, etc. I want to create a query that returns two columns of
information: a distinct list of surnames, and a count of how many times each
distinct surname appears in the main table.

Each of these elements is easy enough to achieve on its own with a SELECT
DISTINCT or SELECT COUNT(DISTINCT) style query, but how can I combine them
to get what I want?

 

[Brian Camire]

You might try a query whose SQL looks something like this:

SELECT
[Your Table].[Surname],
Count(*) AS [Instances]
FROM
[Your Table]
GROUP BY
[Your Table].[Surname]

 

[Paul Brown]

You might try a query whose SQL looks something like this:

SELECT
[Your Table].[Surname],
Count(*) AS [Instances]
FROM
[Your Table]
GROUP BY
[Your Table].[Surname]

Many thanks Brian, that works a treat!