Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Counts with Criteria

Discussion in 'Information Technology' started by Jennifer Cali, Jul 28, 2006.

  1. Jennifer Cali

    Jennifer Cali
    Expand Collapse
    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!

    --
    Thank you! - Jennifer
     
  2. Loading...

    Similar Threads Forum Date
    Afghan's Only Sikh MP Recounts Her Struggles Sikh Personalities Feb 16, 2013
    World Iranian World Bank Economist Counts the Cost of Barriers to Women Breaking News Jan 13, 2011
    Sikh Eyewitness Accounts Sikh Youth Aug 28, 2008
    Sikh News Punjab seals local govts’ record, freezes accounts (Dawn) Breaking News Apr 24, 2008
    Character Counts ! Sikh Sikhi Sikhism Jan 21, 2008

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Jennifer Cali" <JenniferCali@discussions.microsoft.com> wrote in message
    news:1424F240-548A-44DD-A49A-26B88150A106@microsoft.com...
    > 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!
    >
    > --
    > Thank you! - Jennifer
     
  4. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Jennifer Cali wrote:
    > 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

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  5. Luiz Cláudio C. V. Rocha

    Luiz Cláudio C. V. Rocha
    Expand Collapse
    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


    "Jennifer Cali" wrote:

    > 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!
    >
    > --
    > Thank you! - Jennifer
     

Share This Page