Count Records based on Criteria

[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.

 

[Klatuu]

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.

 

[Filo]

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.

 

[Klatuu]

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.

 

[Filo]

Thank you!!

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.