Distinct query method

[Jessica]

Greetings!

In my table I have two fields: year, collection.
Collection is not a unique field, but what I want to do is
create a query that counts the number of unique
collections per year. The only way I was able to do this
was to first create a query that used the used the totals
functions to group by year and collection number, and then
run a second query on that query that counted the number
of collections in each year. Is there a way to do this
using the distinct row or field function that will still
give me the same count?

Thanks!
Jessica

 

[Dale Fye]

No, but you could do it using a subquery, so you only have to store
one query in your database.

SELECT T.Year, Count(T.Collection) as CollectionCount
FROM (SELECT Distinct Year, Collection
FROM yourTable) as T
GROUP BY T.Year

--
HTH

Dale Fye


Greetings!

In my table I have two fields: year, collection.
Collection is not a unique field, but what I want to do is
create a query that counts the number of unique
collections per year. The only way I was able to do this
was to first create a query that used the used the totals
functions to group by year and collection number, and then
run a second query on that query that counted the number
of collections in each year. Is there a way to do this
using the distinct row or field function that will still
give me the same count?

Thanks!
Jessica

 

[Dale Fye]

You are welcome!

--
HTH

Dale Fye


Thanks that worked! I didn't realize you could use
subqueries.