Count Records based on Criteria

  • Thread starter Thread starter Filo
  • Start date Start date
F

Filo

I have a table which lists all sales made by a provider. How can I count the
number of States in which providers make sales? Caveat: A provider may make
multiple sales in a State, and in that case I want the counter to count 1
(one State).

Thank you.
 
Create a total query.
In the query designer, select the fields provider and state. Click on the
totals icon. It looks like the Greek letter Sigma. You will get a new row
in the query builder. For provide select Group By. For state select count.
The query will return one row for each provide with the count by state.
 
I tried it, but there is a problem. For example, for a specific provider it
counted 8 for INDIANA and 1 for ILLINOIS, because the provider made 8 sales
in INDIANA and 1 in ILLINOIS. I would like the counter in that case to list 2
(two states). Thank you for your help.
 
Sorry, Filo, I misread your question. I thought you want the number of sales
by state. You need to add another query.

SELECT DISTINCT state, provider FROM YourTableName;

Then base your totals query on this query instead of the table. This query
will return only once occurance of each provider/state combination. You
total query should then a count of states rather than a count of sales.
 
Thank you!!

Klatuu said:
Sorry, Filo, I misread your question. I thought you want the number of sales
by state. You need to add another query.

SELECT DISTINCT state, provider FROM YourTableName;

Then base your totals query on this query instead of the table. This query
will return only once occurance of each provider/state combination. You
total query should then a count of states rather than a count of sales.
Click to expand...
 
Back
Top