?
Solved

How to create a query for a monthly running total

Posted on 2011-10-25
19
Medium Priority
?
284 Views
Last Modified: 2012-05-12
Hello,

I know there are several, close examples of this but none seem to do the trick for me. Essentially I am tracking weekly transactions of two types across several sites and would like to create a query that provides me with the total of monthly transactions for all sites and the running total of cumulative transactions for each transaction type.

I have 11 different sites for which I receive weekly totals of transactions that I want to do monthly running totals for. The query would produce the following:

MonthYear  | TransactionsA | RunningTotalA | TransactionsB | Running TotalB

JUL-2011                10                  10                         15                       15
Aug-2011               10                  20                         20                       35

I am able to group sites up in months for the total transactions, but can't figure out a decent way to do a running total. The MS example found here does not allow for doing the total across years: http://support.microsoft.com/kb/290136/en-us


The snippet provided is the query I put together to do the total monthly transactions.

Thanks in advance,

Nathan

SELECT Sum(usage.transactionA) AS TransactionA, Sum(usage.transactionB) AS TransactionB, Format$([usage].[weekly_period],'mmm-yy') AS [Month]
FROM [usage]
GROUP BY Format$([usage].[weekly_period],'mmm-yy'), Year([usage].[weekly_period])*12+DatePart('m',[usage].[weekly_period])-1
ORDER BY Year([usage].[weekly_period])*12+DatePart('m',[usage].[weekly_period])-1;

Open in new window

0
Comment
Question by:nbotts
  • 8
  • 6
  • 5
19 Comments
 
LVL 31

Expert Comment

by:hnasr
ID: 37028408
Check with this example.
table: _table(theMont, theValueA, theValueB)
Sample Data:
theMonth      theValueA      theValueB
7      10      15
8      10      20
9      10      20

The query:

SELECT s.theMonth, s.theValueA, (SELECT SUM ( t.theValueA) As theTotalA FROM _table t WHERE t.theMonth <=s.theMonth) AS theTotalA, s.theValueB, (SELECT SUM ( t.theValueB) As theTotalB FROM _table t WHERE t.theMonth <=s.theMonth) AS theTotalB
FROM _table AS s
ORDER BY s.theMonth;

Output:
theValueA      theTotalA      theValueB      theTotalB
10      10      15      15
10      20      20      35
10      30      20      55
0
 
LVL 31

Assisted Solution

by:hnasr
hnasr earned 400 total points
ID: 37028441
The result with theMonth as date format mm-yyyy, above comment assumes theMont as integer.
_table:
theMonth      theValueA      theValueB
07-2011      10      15
08-2011      10      20
09-2011      10      20

The query:

SELECT s.theMonth, s.theValueA, (SELECT SUM ( t.theValueA) As theTotalA FROM _table t WHERE t.theMonth <=s.theMonth) AS theTotalA, s.theValueB, (SELECT SUM ( t.theValueB) As theTotalB FROM _table t WHERE t.theMonth <=s.theMonth) AS theTotalB
FROM _table AS s
ORDER BY s.theMonth;

Output:
theMonth      theValueA      theTotalA      theValueB      theTotalB
07-2011      10      10      15      15
08-2011      10      20      20      35
09-2011      10      30      20      55
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37030826
Can you provide us with a db that contains a sample of the data from your [usage] table?

How far back do you want to go with the output?  Is there a startdate for the query or do you want it (the running sum) to go back to the beginning of the data, or do you want the running sum to start on a particular month (Jan or some other fiscal month) for each year?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:nbotts
ID: 37030972
Thank you hnasr, that query does do a running total for both transactions which is great, but it is not grouping by month.

When you say that it assumes theMont is an integer, do you mean that the field with the date is of type integer vs a datetime field? In my database theMonth field is a datetime value.

Attached is a screenshot of what it produces.

Thanks again for your help,

-Nathan
image003.jpg
0
 

Author Comment

by:nbotts
ID: 37031020
Fyed,

Hnasr's database example is essentially correct and the query provided appears to be close except for the monthly grouping. In terms of the time period I would eventually want to be able to constrain it between a couple dates and will probably be reporting it in 3-6 month groupings.

The data itself is not really specific to a year and will need to span years for its report.

Thanks,

Nathan
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37031051
There are several ways to skin this cat, hnsar's is one of those, but with a larger data sample would be helpful.

0
 
LVL 31

Expert Comment

by:hnasr
ID: 37031534
nbotts,

My comment was based on the assumption that you grouped the data by month.

It should be simple by introducing one intermediate step to group the data by month. Then apply the comment presented.

Upload a table with the raw data by date, to implement the solution?
0
 

Author Comment

by:nbotts
ID: 37031821
fyed and hnasr,

Will the attached provide the details that you need? This is essentially how I receive the data.

Thanks,

Nathan
Running-Total-Transaction-Data.xlsx
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37031920
Yes, that will help.

Your original query only groups by year/month.  Do you intend to group by Site as well, or should all sites be rolled together?
0
 

Author Comment

by:nbotts
ID: 37031963
It would need to be all sites rolled together. Essentially I want the month/year, total of TransactionsA for that month, total of TransactionsB for that month, running total of TransactionA and running total of TransactionB.

Thank you,

Nathan
0
 

Author Comment

by:nbotts
ID: 37037984
On the positive side, I will assume that this is a bit of a tough query and so it wasn't just me.

But otherwise it seemed as though we were so close....any ideas how it can be done with the data I provided?
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37038135
sorry, got distracted by work.

Will take another look at it during my lunch hour (1.5 hours from now).
0
 

Author Comment

by:nbotts
ID: 37038226
Ha ha, indeed work can be very distracting. Thanks so much...
0
 
LVL 31

Expert Comment

by:hnasr
ID: 37038840
Away from pc.
Try to do some homework.
Group your raw data per month to create a table say _table
and continue with the comment above.
0
 

Author Comment

by:nbotts
ID: 37039317
Sorry hnasr, I'm not sure what you mean or I guess if I did I would not be here, yes? And this is my homework. I have scoured for examples and ways to do this and would not be paying for this service otherwise.

As noted before, with your help I seem to be able to get as far as the attached image, but can't seem to roll-up the months within the query.

It doesn't seem like this should be difficult, but does seem to be a little complicated to get out of a query vs a report. I'm assuming it can be done though.
image003.jpg
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 1600 total points
ID: 37039830
See attached.


RunningTotal.mdb
0
 

Author Closing Comment

by:nbotts
ID: 37040075
Thank you fyed, that is exactly what I was looking for, much appreciated.

And really like the query that allows for designating the start date.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37040085
Glad I could help.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 37040416
Try this:
Run form
click command button, the code runs queries.

Private Sub cmdProcess_Click()
    'DoCmd.SetWarnings False
    DoCmd.OpenQuery "_table_make"
    DoCmd.OpenQuery "_query_monthly"
    'DoCmd.SetWarnings True
End Sub

'uncomment for no warnings.
monthly-running-total.mdb
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month13 days, 22 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question