How to Count Unique Values in a Microsoft Access Query Field?

How to count unique values ​​in Microsoft Access queries

Question:

When running an SQL query containing count(*) against a table that contains duplicate values, the results will inaccurately count the total number of rows. How do I modify my query so that it only counts unique values ​​in a specific field (such as the "Name" field)?

Example:

Consider table "table1" containing the following data:

<code>ID  姓名  家庭
1   A     AA
2   B     BB
3   A     AB
4   D     DD
5   E     EE
6   A     AC</code>

Question:

Query select count(*) from table1 returns 6 even though there are only 4 unique names in the Name field.

Solution:

To count only unique values ​​in the "Name" field, you can use the following modified query:

SELECT Count(*) AS N
FROM
(SELECT DISTINCT Name FROM table1) AS T;

This query starts by creating a subquery that selects the unique values ​​in the "Name" field and stores them in a temporary table named "T". The outer query then counts the number of rows in the subquery, which represents the number of unique names in the Name field.

Instructions:

The

DISTINCT keyword ensures that only unique values ​​are included in the subquery. The subquery creates a new table with only one column "name" containing unique values. The outer query then counts the rows in this new table to accurately count the number of unique items in the Name field.

More information:

For more information about using the DISTINCT keyword in Access queries, see the Microsoft documentation.

The above is the detailed content of How to Count Unique Values in a Microsoft Access Query Field?. For more information, please follow other related articles on the PHP Chinese website!