S
SteveS
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!
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!