Count Unique Occurrences

[Lee]

My database has two tables, Top Providers and Claims. The
Top Providers table contains one field, Provider TIN,
while the Claims table contains EE ID and Provider TIN. I
am trying to run a query that will allow me to count the
number of unique occurences for each Provider TIN in the
Claims table by EE ID. I have already done counts that
allow me to count the number of occurences for each
Provider TIN in the Claims table, but now I want to break
it even further to determine how many different EEs
utilized the different Providers. In other words, one
Provider TIN may appear 1000 times in the Claims table,
representing 1000 claims, but these claims were incurred
for only 15 different EE IDs. That's what I'm trying to
get. I've tried selecting Unique Values in the Properties
but that made no difference; I'm still getting the total
count of Providers TINS in the Claims table.

 

[[MVP] S. Clark]

The answer is going to contain the Group By operator somewhere in the query.
It is possible that you may need to use two queries. The first to do one
aggregation, and the 2nd to do the 2nd aggregation.

If this doesn't help, then list your table structures and the SQL that
you've already created.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-