Adding running total to union query

[Guest]

I posted this before and thought I had a solution. Unfortunately, it became
too large and I received a "Query is too complex" error, so now I'd like to
try a different approach...

I have created a Union Query to consolidate results from separate queries,
each one of which represents results for a single month:

SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total;
etc. (12 months total)

This gives me the following results:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $7500 $400
03/01/2007 $3500 $350
etc.

However, I would now like to add running sum columns to the union query,
ideally resulting in this:

ReportMonth MonthlyRev MonthlyCost MonthlyRevSum MonthlyCostSum
01/01/2007 $5000 $550 $5000
$550
02/01/2007 $7500 $400 $12500
$950
03/01/2007 $3500 $350 $16000
$1300
etc.

Is it possible to add this type of calculation to my existing union query,
in a way that won't become so long that I can avoid the "query is too
complex" error? (You can see my earlier post started 6/4 titled "Running Sum
in Union Query" for the suggested answer that ultimately caused the error.)

Please note that I'm relatively new to access (just started reading on
subqueries today) so the simpler the better.
Thanks!

 

[Michael Gramelspacher]

I posted this before and thought I had a solution. Unfortunately, it became
too large and I received a "Query is too complex" error, so now I'd like to
try a different approach...

I have created a Union Query to consolidate results from separate queries,
each one of which represents results for a single month:

SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total;
etc. (12 months total)

This gives me the following results:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $7500 $400
03/01/2007 $3500 $350
etc.

However, I would now like to add running sum columns to the union query,
ideally resulting in this:

ReportMonth MonthlyRev MonthlyCost MonthlyRevSum MonthlyCostSum
01/01/2007 $5000 $550 $5000
$550
02/01/2007 $7500 $400 $12500
$950
03/01/2007 $3500 $350 $16000
$1300
etc.

Is it possible to add this type of calculation to my existing union query,
in a way that won't become so long that I can avoid the "query is too
complex" error? (You can see my earlier post started 6/4 titled "Running Sum
in Union Query" for the suggested answer that ultimately caused the error.)

Please note that I'm relatively new to access (just started reading on
subqueries today) so the simpler the better.
Thanks!

Just a thought, does it have to be UNION?

These are examples for the Northwind sample database
that comes with Access. Paste each query into SQL View
of a new query and names the queries as below. They are
merely examples.

Daily Sales Totals
-----------------
SELECT Orders.OrderDate, Sum([Order Subtotals].Subtotal) AS [Daily Sales]
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID=[Order
Subtotals].OrderID
WHERE Orders.Orderdate BETWEEN [Enter start date:] AND [Enter end date:]
GROUP BY Orders.OrderDate;

Monthly Sales Totals
--------------------
SELECT Dateadd("m",Datediff("m",0,[d.OrderDate]),0) AS [Month], Sum(d.[Daily
Sales]) AS [Monthly Sales], Count(d.OrderDate) AS [Business Days], CCur(Sum(d.
[Daily Sales])/Count(d.OrderDate)) AS [Daily Average]
FROM [Daily Sales Totals] AS d
GROUP BY Dateadd("m",Datediff("m",0,[d.OrderDate]),0);

Running Monthly Sales
----------------------
SELECT FORMAT(a.Month,"mmm-yyyy") AS [Month-Year], a.[Monthly Sales], SUM(b.
[Monthly Sales]) AS [Running Sales], COUNT(b.month) AS [Sales Months], DATEPART
("m",a.month) AS [Month Nbr], CCUR(SUM(b.[Monthly Sales])/DATEPART
("m",a.month)) AS [Monthly Average]
FROM [monthly sales totals] AS a INNER JOIN [monthly sales totals] AS b ON
a.month>=b.month
GROUP BY a.month, a.[Monthly Sales]
ORDER BY a.Month;

 

[SteveS]

Thanks Michael,

Each query only represents one month (for reasons I won't go into here...),
so that is why I would like to have the running sum within the union query.
The only other option (just thought of) is if I run a select query using the
union query as the source, and try to do the running sum there... but don't
know if that's even possible. Any other thoughts?

Steve

P.S. Does anybody know what's going on with the MS discussion group? It looks
like nothing is posting since about 2 days ago. I just happened to remember
that this site pulls from there and vice-versa.

I posted this before and thought I had a solution. Unfortunately, it became
too large and I received a "Query is too complex" error, so now I'd like to

[quoted text clipped - 41 lines]

subqueries today) so the simpler the better.
Thanks!

Just a thought, does it have to be UNION?

These are examples for the Northwind sample database
that comes with Access. Paste each query into SQL View
of a new query and names the queries as below. They are
merely examples.

Daily Sales Totals
-----------------
SELECT Orders.OrderDate, Sum([Order Subtotals].Subtotal) AS [Daily Sales]
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID=[Order
Subtotals].OrderID
WHERE Orders.Orderdate BETWEEN [Enter start date:] AND [Enter end date:]
GROUP BY Orders.OrderDate;

Monthly Sales Totals
--------------------
SELECT Dateadd("m",Datediff("m",0,[d.OrderDate]),0) AS [Month], Sum(d.[Daily
Sales]) AS [Monthly Sales], Count(d.OrderDate) AS [Business Days], CCur(Sum(d.
[Daily Sales])/Count(d.OrderDate)) AS [Daily Average]
FROM [Daily Sales Totals] AS d
GROUP BY Dateadd("m",Datediff("m",0,[d.OrderDate]),0);

Running Monthly Sales
----------------------
SELECT FORMAT(a.Month,"mmm-yyyy") AS [Month-Year], a.[Monthly Sales], SUM(b.
[Monthly Sales]) AS [Running Sales], COUNT(b.month) AS [Sales Months], DATEPART
("m",a.month) AS [Month Nbr], CCUR(SUM(b.[Monthly Sales])/DATEPART
("m",a.month)) AS [Monthly Average]
FROM [monthly sales totals] AS a INNER JOIN [monthly sales totals] AS b ON
a.month>=b.month
GROUP BY a.month, a.[Monthly Sales]
ORDER BY a.Month;