Count Records based on Criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Filo - 19 May 2008 15:26 GMT

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.

Reply to this Message

Klatuu - 19 May 2008 15:32 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

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

Reply to this Message

Filo - 19 May 2008 15:59 GMT

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.

> Create a total query.
> In the query designer, select the fields provider and state. Click on the
[quoted text clipped - 8 lines]
> >
> > Thank you.

Reply to this Message

Klatuu - 19 May 2008 16:14 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> 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
[quoted text clipped - 14 lines]
> > >
> > > Thank you.

Reply to this Message

Filo - 19 May 2008 16:53 GMT

Thank you!!

> Sorry, Filo, I misread your question. I thought you want the number of sales
> by state. You need to add another query.
[quoted text clipped - 23 lines]
> > > >
> > > > Thank you.

Reply to this Message