Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

running sum

I would to create a running sum on the following but not sure how in access:

SELECT Count(CWSMMTBuilds.[MVRIS CODE]) AS [CountOfMVRIS CODE], CWSMMTBuilds.BuildYearMonth
FROM CWSMMTBuilds
GROUP BY CWSMMTBuilds.BuildYearMonth;

Open in new window

0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 2
1 Solution
 
Dale FyeCommented:
How are you going to output your results?

If in a report, you can add another textbox to the report, set the control source to the name of the field you want to sum over, then set the RunningSum property of that textbox to "Over Group" or "Over All"
0
 
PeterBaileyUkAuthor Commented:
just to view in the query as a field
0
 
Patrick MatthewsCommented:
Assuming that whatever data type BuildYearMonth is, it allows for a valid sort, try:

SELECT c1.BuildYearMonth,
    Count(c1.[MVRIS CODE]) AS [CountOfMVRIS CODE],
    (SELECT Count(c2.[MVRIS CODE])
    FROM CWSMMTBuilds c2
    WHERE c2.BuildYearMonth <= c1.BuildYearMonth) AS RunningCount
FROM CWSMMTBuilds c1
GROUP BY c1.BuildYearMonth
ORDER BY c1.BuildYearMonth;

Open in new window

0
 
Patrick MatthewsCommented:
Or (performance might suck):

SELECT c1.BuildYearMonth,
    Count(c1.[MVRIS CODE]) AS [CountOfMVRIS CODE],
    DCount("[MVRIS CODE]", "CWSMMTBuilds", "[BuildYearMonth] = " & 
        c1.BuildYearMonth) AS RunningCount
FROM CWSMMTBuilds c1
GROUP BY c1.BuildYearMonth
ORDER BY c1.BuildYearMonth;

Open in new window


or if BuildYearMonth is text...

SELECT c1.BuildYearMonth,
    Count(c1.[MVRIS CODE]) AS [CountOfMVRIS CODE],
    DCount("[MVRIS CODE]", "CWSMMTBuilds", "[BuildYearMonth] = '" & 
        c1.BuildYearMonth & "'") AS RunningCount
FROM CWSMMTBuilds c1
GROUP BY c1.BuildYearMonth
ORDER BY c1.BuildYearMonth;

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now