1. ## 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. ## Re: Running Count (2003)

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

3. ## 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. ## 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. ## 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. ## Re: Running Count (2003)

Create a query based on your table.
Sort it on Grpnum, then Product Status.
<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. ## 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. ## 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. ## 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
•