Count # of unique values in field?

[chet]

Hi,

I'd like to write a query that counts the number of unique
values in a field. Take the example of a table with the
following records:

Company State Country
------------------------------------
Company1 PA USA
Company1 NY USA
Company1 NJ USA
Company2 AB Canada
Company2 ON Canada
Company2 NS Australia

I'd like the query to respond that company1 has 3 values
in the state field and 1 value in the country field, and
that company2 has 3 values in the state field and 2 values
in the country field.

When I use the "count" function in the aggregate query,
Access returns "3" under both state and country for both
company1 and company2.

From this I'm concluding that the count function returns
the number of records with non-null values in the field.
How do I get the number of unique values in each of the
two fields?

Thanks.

 

[Kelvin]

You can create one really complicated query using SQL or just use multiple
count queries.

1) GroupBy Company and State
2) GroupBy Company and Country
3) Join Query 1 to Query 2 by the Company then
GroupBy Company, Count State, Count Country

Kelvin Lu

 

[chet]

Thanks, Kelvin!

Chet

-----Original Message-----
You can create one really complicated query using SQL or just use multiple
count queries.

1) GroupBy Company and State
2) GroupBy Company and Country
3) Join Query 1 to Query 2 by the Company then
GroupBy Company, Count State, Count Country

Kelvin Lu




.