Count Records with a Specific Value in a Field

[sail87]

I have a table called 'WorkOrder Table', in it is a field labeled 'Status'. There are about 15 different statuses, new, backlog, done, etc. I would like a query that I can run periodically that would tell me how many records there are for each status, like below:

1500 New
2000 Backlog
etc.

Any help would be appreciated (I'm realtively new to Access and SQL in general.

Thank you

[nico5038]

You'll need to have a table containing al possible values for the Status field and JOIN that in the query editor with the tblWorkOrder.
Now place the status field and the ID of the tblWorkOrder and press the groupby button (looks like "E").
Change underneath the ID the "GroupBy" into "Count" and see the result.

Nic;o)

[sail87]

You'll need to have a table containing al possible values for the Status field and JOIN that in the query editor with the tblWorkOrder.
Now place the status field and the ID of the tblWorkOrder and press the groupby button (looks like "E").
Change underneath the ID the "GroupBy" into "Count" and see the result.

Nic;o)

Thank you, that is exactly what I was looking for. It worked perfectly!

[NeoPa]

You should get away with :

SELECT [Status],
       Count([Status]) AS [StatusCount]
FROM [WorkOrder Table]
GROUP BY [Status]
ORDER BY [Status]

[nico5038]

Not entirely NeoPa, when you want all statuses to show, an outer join is needed.
That part I was forgotten to mention in my comment :-)

sail87, open the query in design mode and double click the JOIN line between the two tables. No chose option 2 or 3, making the tblStatus the "master".
This will make sure all status fields are present, even when there's no data.

Nic;o)

[NeoPa]

Fair point Nico.

I read the question differently, but on rereading, I think your reading of it is probably the correct one :)

That is to say :-
Show all POSSIBLE statuses (rather than all represented ones), and the count of each.

Whereas I was showing the count of all statuses that existed in the [WorkOrder Table] table.