nbotts
asked on
How to create a query for a monthly running total
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
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;SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
ASKER
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
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
ASKER
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
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
There are several ways to skin this cat, hnsar's is one of those, but with a larger data sample would be helpful.
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?
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?
ASKER
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
Will the attached provide the details that you need? This is essentially how I receive the data.
Thanks,
Nathan
Running-Total-Transaction-Data.xlsx
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?
Your original query only groups by year/month. Do you intend to group by Site as well, or should all sites be rolled together?
ASKER
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
Thank you,
Nathan
ASKER
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?
But otherwise it seemed as though we were so close....any ideas how it can be done with the data I provided?
sorry, got distracted by work.
Will take another look at it during my lunch hour (1.5 hours from now).
Will take another look at it during my lunch hour (1.5 hours from now).
ASKER
Ha ha, indeed work can be very distracting. Thanks so much...
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.
Try to do some homework.
Group your raw data per month to create a table say _table
and continue with the comment above.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
And really like the query that allows for designating the start date.
Glad I could help.
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
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
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