Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Count (2003)

    I would like to do a running count in a query like so - running-count [Product Status] for Group Number. A group number can have 1 or 2 Product Status - There could be both Cancelled and Active, Just Active, Just Cancelled. I would sort on this field so the Active is first if there is an Active and a Cancel. Even if there is only Cancel, I want that to equal 1. So each group should have the most current Product Status. In the criteria for this field, I would put =1. Is this possible in a query?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Count (2003)

    I'm sorry, I don't understand. Could you provide a detailed example?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Count (2003)

    The runCnt is a calculation - Running-Count [Product Status] for Grpnum. For Grpnum there are 2 status - Active and Canceled. I need to get one status per group be it active or cancelled. If there are two status - an Active and a Cancel - I just want the Active. That is why I need a running count and for that count to equal one. Is this possible in Access? Once I get one status per group then I have to say the Product Status = 'Cancelled' but if I can get a running count I can make another query and do that.
    Grpnum Product Status runCnt
    01005100 Active 1
    01005140 Active 1
    01015010 Active 1
    01015010 Canceled 2
    01015011 Active 1
    01015011 Canceled 2
    01015012 Active 1
    01015012 Canceled 2
    01015013 Active 1
    01015013 Canceled 2
    01015020 Active 1
    01015020 Canceled 2
    01015021 Active 1
    01015021 Canceled 2
    01015022 Active 1
    01015022 Canceled 2
    01015023 Active 1
    01015023 Canceled 2

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Count (2003)

    Sorry, I'm confused. At first, you seem (to me) to say that runCnt should be computed from the value of Product Status, but later on I get the impression that you want to calculate Product Status from the value of runCnt.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Count (2003)

    The RunCnt field is the calculation I would like to do in an Access query. The example is the result I am looking for. I want to count Product Status for Group number. That would be the calculation. If Group Number appears twice, it has a Product Status of Active and Cancelled or it could have two cancels. I want to sort Product Status according to group number and then do a running count.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Count (2003)

    Create a query based on your table.
    Sort it on Grpnum, then Product Status.
    Add a calculated column:
    <code>
    runCnt: DCount("*", "NameOfTable", "[Grpnum] = " & Chr(34) & [Grpnum] & Chr(34) & " AND [Product Status] <= " & Chr(34) & [Product Status] & Chr(34))
    </code>
    Substitute the table name, and modify the field names as needed.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Count (2003)

    If you have a moment , can you explain the ampersands and the Chr(34). I would like to use this in the future and the syntax looks confusing. Thanks for your help. If the fields were number fields, would the syntax change?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Count (2003)

    Chr(34) is the double quote character " that is needed to enclose text values. If the fields had been number fields, the Ch(34) wouldn't have been necessary.

    The & is used to concatenate text strings, i.e. "glue" them together into a longer string. For example, "Bill" & "Gates" results in "BillGates".
    In the expression that I posted, fixed strings such as "[Grpnum] = " are combined with values from the query such as [GrpNum] using &.

    If Grpnum = "01005100" and Product Status is "Active", the expression

    [Grpnum] = " & Chr(34) & [Grpnum] & Chr(34) & " AND [Product Status] <= " & Chr(34) & [Product Status] & Chr(34)

    evaluates to

    [Grpnum] = "01005100" AND [Product Status] <= "Active"

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Count (2003)

    Thanks Hans for taking the time to explain. Really appreciate all your help...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •