Counts with Criteria

[Guest]

I'm trying to track the type of positions we have at work and associate that
with the reason the position is vacant. For example, we have cashiers,
managers, and clerks. Each can be vacant for one of four reasons (quit,
fired, on leave, in training). I need to come up with some way to do a count
of each vacancy reason for each position, and then combine that data into one
report. I've tried two alternatives:
1) Created queries (16 of them) that each do a count but now I can't combine
them on to one form.
2) Created a blank form with VB set to run on open that reads:

Dim cntLOA

If chrMgrType = "Cashier" And chrActualCode = "LOA" Then
cashierLOA = cntLOA.Count
End If

End Sub

Unfortunately, that didn't work at all. What would you suggest? I'm at a
loss and need to turn in my project at a 10am conference call tomorrow!

 

[Douglas J. Steele]

To create a single query that returns them all, try something like:

SELECT Sum(IIf(chrMgrType = "Cashier" And chrActualCode = "LOA", 1, 0)) As
cashierLOA,
Sum(IIf(chrMgrType = "Manager" And chrActualCode = "LOA", 1, 0)) As
managerLOA,
Sum(IIf(chgMgrType = "Clerk" And chrActualCode = "LOA", 1, 0)) As clerkLOA,
Sum(IIf(chrMgrType = "Cashier" And chrActualCode = "Quit", 1, 0)) AS
cashierQuit,
Sum(IIf(chrMgrType = "Manager" And chrActualCode = "Quit", 1, 0)) As
managerQuit,
Sum(IIf(chgMgrType = "Clerk" And chrActualCode = "Quit", 1, 0)) As
clerkQuit,
Sum(IIf(chrMgrType = "Cashier" And chrActualCode = "Fired", 1, 0)) AS
cashierFired,
Sum(IIf(chrMgrType = "Manager" And chrActualCode = "Fired", 1, 0)) As
managerFired,
Sum(IIf(chgMgrType = "Clerk" And chrActualCode = "Fired", 1, 0)) As
clerkFired,
Sum(IIf(chrMgrType = "Cashier" And chrActualCode = "In Training", 1, 0)) AS
cashierTraining,
Sum(IIf(chrMgrType = "Manager" And chrActualCode = "In Training", 1, 0)) As
managerTraining,
Sum(IIf(chgMgrType = "Clerk" And chrActualCode = "In Training", 1, 0)) As
clerkTraining
FROM MyTable

 

[Rick Brandt]

I'm trying to track the type of positions we have at work and
associate that with the reason the position is vacant. For example,
we have cashiers, managers, and clerks. Each can be vacant for one of
four reasons (quit, fired, on leave, in training). I need to come up
with some way to do a count of each vacancy reason for each position,
and then combine that data into one report. I've tried two
alternatives: 1) Created queries (16 of them) that each do a count
but now I can't combine them on to one form.
2) Created a blank form with VB set to run on open that reads:

Dim cntLOA

If chrMgrType = "Cashier" And chrActualCode = "LOA" Then
cashierLOA = cntLOA.Count
End If

End Sub

Unfortunately, that didn't work at all. What would you suggest? I'm
at a loss and need to turn in my project at a 10am conference call
tomorrow!

There is no way to answer your question unles you provide some infromation about
how your data and tables are set up. Are all of these positions and vacancy
reasons in a single table? If so then a single Totals query should give you all
of the counts.

SELECT chrMgrType, chrActualCode, Count(*) as Cnt
FROM TableName
GROUP BY chrMgrType, chrActualCode

 

[Guest]

Hi Jennifer,

you can group your data in order to count it.

Create a query similar to this one:

SELECT Count(YourIdField) AS Quantity, Position, Reason
FROM YourTable
GROUP BY Position, Reason

--
Luiz Cláudio C. V. Rocha
Coordenador de Projetos FórumAccess
São Paulo - Brasil
MVP Office
http://www.msmvps.com/officedev