Count Function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

StuJol - 07 Aug 2005 13:48 GMT

I have a table named "WOStatus" which has a colume named "Status" and another
named "StatusDate". In the WOStatus field in my table i can select values
from a combo box like "New", "Closed", Waiting Payment" etc.

I want to create a query that COUNTS the total number of "new" records,
"Closed" Records and "waiting Payment" records between sertain "Status Dates"

I know how to select BETWEEN dates already.

Can any one help? Do i have to create a new query for each status being
counted or can it b done on one query?

Reply to this Message

tina - 07 Aug 2005 13:55 GMT

try using a Totals query, grouping on the status field, and counting another
field (i usually count on the primary key field).

btw, suggest you don't name a field in your table with the same name as your
table. also suggest you get rid of the Lookup field (combo box) in your
table; see the following link for reasons why.
http://www.mvps.org/access/lookupfields.htm
also recommend you build a form for data entry, rather than entering data
directly into your table. in the form, you can create a combo box to show a
list of statuses to choose from.

hth

> I have a table named "WOStatus" which has a colume named "Status" and another
> named "StatusDate". In the WOStatus field in my table i can select values
[quoted text clipped - 7 lines]
> Can any one help? Do i have to create a new query for each status being
> counted or can it b done on one query?

Reply to this Message

StuJol - 07 Aug 2005 14:13 GMT

Thanks for the quick reply

Can you try to explain the first couple of lines of your response to me in
easyier terms as im still learning the basics of Access, and are finding it
hard to understand your comments.

> try using a Totals query, grouping on the status field, and counting another
> field (i usually count on the primary key field).
[quoted text clipped - 22 lines]
> > Can any one help? Do i have to create a new query for each status being
> > counted or can it b done on one query?

Reply to this Message

tina - 07 Aug 2005 16:05 GMT

to learn more about Totals queries, look up "totals query" or "total records
in a query" in Access Help. follow the instructions and post back if you run
into difficulties.

hth

> Thanks for the quick reply
>
[quoted text clipped - 28 lines]
> > > Can any one help? Do i have to create a new query for each status being
> > > counted or can it b done on one query?

Reply to this Message