Running Sum On Value

[Kodama76]

Okay, I'm feeling really stupid since I think this should be pretty easy to do.

I have a table of transactions that looks like this:

CODE POLICY
44 FG48573
44 FG36439
44 FG29483
36 FG69274
36 FG23853
28 FG38567

I want to make a query that makes a running sum for EACH CODE and not for the entire query. The end result would look like this.

CODE POLICY COUNT
44 FG48573 1
44 FG36439 2
44 FG29483 3
36 FG69274 1
36 FG23853 2
28 FG38567 1

Seems easy but I must be missing something. I know its easy to do in a report but I need it in a query for other reasons. Any ideas??

 

[Jon K]

In a query, you can do it with a subquery e.g.

SELECT tblPolicy.CODE, tblPolicy.POLICY,
(Select Count(*) from [tblPolicy] as S where S.

=tblPolicy.[Code] and S.[POLICY]>=tblPolicy.[POLICY]) AS [Count]
FROM tblPolicy;

Given your sample data, the above query would work as the Policy numbers within each Code happen to be in descending order.


If the Policy numbers are not in any particular order, then you have to based the subquery on a date field or an AutoNumber field instead of the [POLICY] field.

SELECT tblPolicy.CODE, tblPolicy.POLICY, 
(Select Count(*) from [tblPolicy] as S where S.[Code]=tblPolicy.[Code] and S.[ID]<=tblPolicy.[ID]) AS [Count]
FROM tblPolicy;


I have attached a sample database using an AutoNumber [ID] field as illustration.

Note
This kind of subquery is very inefficient. If the table is large, you may consider adding a Count field to the table and update the field using VBA code.
[color=white].[/color]

 

[Kodama76]

Wow, that did it. Thanks so much for the help. I was starting to get afraid that it couldn't be done outside of a report.

Thanks again.