Running Sum in Union Query?

S

SteveS

Hello,

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.

This works very well for showing me monthly revenues vs. costs. However, I
would also like to be able to create a similar Union Query that shows a
running sum of the monthly revenue and costs. (Note: for reasons I won't get
into here, I have to have each month's data in its own query.)

Is it possible in a Union query to add, for example, [MonthlyRev] from
qryMonth1Total into [MonthlyRev] qryMonth2Total, then the new total from
qryMonth2Total into [MonthlyRev] qryMonth3Total, etc. to give me the running
total I'd like to see?

Thanks!
P.S. Relatively new (and self-taught) in Access, so the simpler the better. :)
 
K

KARL DEWEY

Try this ---
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
Sum(NZ([qryMonth1Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth2Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth3Total].[MonthlyCost]),0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;
 
K

KARL DEWEY

My error --
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Try this ---
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
Sum(NZ([qryMonth1Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth2Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth3Total].[MonthlyCost]),0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


SteveS said:
Hello,

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.

This works very well for showing me monthly revenues vs. costs. However, I
would also like to be able to create a similar Union Query that shows a
running sum of the monthly revenue and costs. (Note: for reasons I won't get
into here, I have to have each month's data in its own query.)

Is it possible in a Union query to add, for example, [MonthlyRev] from
qryMonth1Total into [MonthlyRev] qryMonth2Total, then the new total from
qryMonth2Total into [MonthlyRev] qryMonth3Total, etc. to give me the running
total I'd like to see?

Thanks!
P.S. Relatively new (and self-taught) in Access, so the simpler the better. :)
Click to expand...
Click to expand...
 
S

SteveS

Hi Karl,

Thanks for the response. This worked, but not in the way I would like. It
gave me a total result at the end (and only for the cost). However, instead
of the output looking like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $7500 $400
03/01/2007 $3500 $350
ALL $1300

I'm looking for a running monthly total like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $12500 $950
03/01/2007 $16000 $1300
etc.

I hope that clarifies. Is something like this possible in a union query?
Thanks again for your help!
Steve

KARL DEWEY said:
My error --
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Try this ---
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
Sum(NZ([qryMonth1Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth2Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth3Total].[MonthlyCost]),0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


SteveS said:
Hello,

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.

This works very well for showing me monthly revenues vs. costs. However, I
would also like to be able to create a similar Union Query that shows a
running sum of the monthly revenue and costs. (Note: for reasons I won't get
into here, I have to have each month's data in its own query.)

Is it possible in a Union query to add, for example, [MonthlyRev] from
qryMonth1Total into [MonthlyRev] qryMonth2Total, then the new total from
qryMonth2Total into [MonthlyRev] qryMonth3Total, etc. to give me the running
total I'd like to see?

Thanks!
P.S. Relatively new (and self-taught) in Access, so the simpler the better. :)
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Ok, just apply what I did in the last union to the second using first and
second queries.
Then use the three in the third, etc.

--
KARL DEWEY
Build a little - Test a little


SteveS said:
Hi Karl,

Thanks for the response. This worked, but not in the way I would like. It
gave me a total result at the end (and only for the cost). However, instead
of the output looking like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $7500 $400
03/01/2007 $3500 $350
ALL $1300

I'm looking for a running monthly total like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $12500 $950
03/01/2007 $16000 $1300
etc.

I hope that clarifies. Is something like this possible in a union query?
Thanks again for your help!
Steve

KARL DEWEY said:
My error --
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Try this ---
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
Sum(NZ([qryMonth1Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth2Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth3Total].[MonthlyCost]),0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

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.

This works very well for showing me monthly revenues vs. costs. However, I
would also like to be able to create a similar Union Query that shows a
running sum of the monthly revenue and costs. (Note: for reasons I won't get
into here, I have to have each month's data in its own query.)

Is it possible in a Union query to add, for example, [MonthlyRev] from
qryMonth1Total into [MonthlyRev] qryMonth2Total, then the new total from
qryMonth2Total into [MonthlyRev] qryMonth3Total, etc. to give me the running
total I'd like to see?

Thanks!
P.S. Relatively new (and self-taught) in Access, so the simpler the better. :)
Click to expand...
Click to expand...
Click to expand...
 
S

SteveS

I think this is what you mean, which does give me the running total for
MonthlyCost:

SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL
SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total
UNION ALL
SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

This code works, but still gives me the following issues:
- Instead of showing the dates for month 2 and month 3, it says "ALL" - how
can I change the code to display the date? (I tried changing the "ALL" AS
ReportMonth statement a few ways but just got errors.)
- It's not showing me any results for the MonthlyRev (I assume because of
the "NULL as MonthlyRev" statement.) How can I change this to show both the
MonthlyRev running total and the MonthlyCost running total?

We're getting there - thanks!
Steve

KARL DEWEY said:
Ok, just apply what I did in the last union to the second using first and
second queries.
Then use the three in the third, etc.

--
KARL DEWEY
Build a little - Test a little


SteveS said:
Hi Karl,

Thanks for the response. This worked, but not in the way I would like. It
gave me a total result at the end (and only for the cost). However, instead
of the output looking like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $7500 $400
03/01/2007 $3500 $350
ALL $1300

I'm looking for a running monthly total like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $12500 $950
03/01/2007 $16000 $1300
etc.

I hope that clarifies. Is something like this possible in a union query?
Thanks again for your help!
Steve

KARL DEWEY said:
My error --
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


:

Try this ---
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
Sum(NZ([qryMonth1Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth2Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth3Total].[MonthlyCost]),0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

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.

This works very well for showing me monthly revenues vs. costs. However, I
would also like to be able to create a similar Union Query that shows a
running sum of the monthly revenue and costs. (Note: for reasons I won't get
into here, I have to have each month's data in its own query.)

Is it possible in a Union query to add, for example, [MonthlyRev] from
qryMonth1Total into [MonthlyRev] qryMonth2Total, then the new total from
qryMonth2Total into [MonthlyRev] qryMonth3Total, etc. to give me the running
total I'd like to see?

Thanks!
P.S. Relatively new (and self-taught) in Access, so the simpler the better. :)
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this --
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL
SELECT qryMonth2Total.ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total
UNION ALL
SELECT qryMonth3Total.ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


SteveS said:
I think this is what you mean, which does give me the running total for
MonthlyCost:

SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL
SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total
UNION ALL
SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

This code works, but still gives me the following issues:
- Instead of showing the dates for month 2 and month 3, it says "ALL" - how
can I change the code to display the date? (I tried changing the "ALL" AS
ReportMonth statement a few ways but just got errors.)
- It's not showing me any results for the MonthlyRev (I assume because of
the "NULL as MonthlyRev" statement.) How can I change this to show both the
MonthlyRev running total and the MonthlyCost running total?

We're getting there - thanks!
Steve

KARL DEWEY said:
Ok, just apply what I did in the last union to the second using first and
second queries.
Then use the three in the third, etc.

--
KARL DEWEY
Build a little - Test a little


SteveS said:
Hi Karl,

Thanks for the response. This worked, but not in the way I would like. It
gave me a total result at the end (and only for the cost). However, instead
of the output looking like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $7500 $400
03/01/2007 $3500 $350
ALL $1300

I'm looking for a running monthly total like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $12500 $950
03/01/2007 $16000 $1300
etc.

I hope that clarifies. Is something like this possible in a union query?
Thanks again for your help!
Steve

:

My error --
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


:

Try this ---
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
Sum(NZ([qryMonth1Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth2Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth3Total].[MonthlyCost]),0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

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.

This works very well for showing me monthly revenues vs. costs. However, I
would also like to be able to create a similar Union Query that shows a
running sum of the monthly revenue and costs. (Note: for reasons I won't get
into here, I have to have each month's data in its own query.)

Is it possible in a Union query to add, for example, [MonthlyRev] from
qryMonth1Total into [MonthlyRev] qryMonth2Total, then the new total from
qryMonth2Total into [MonthlyRev] qryMonth3Total, etc. to give me the running
total I'd like to see?

Thanks!
P.S. Relatively new (and self-taught) in Access, so the simpler the better. :)
Click to expand...
Click to expand...
Click to expand...
 
S

SteveS

That worked like a charm for showing the month. Here's what I did to also
show the MonthlyRev:

SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL
SELECT qryMonth2Total.ReportMonth,
NZ([qryMonth1Total].[MonthlyRev],0) +
NZ([qryMonth2Total].[MonthlyRev],0) AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total
UNION ALL
SELECT qryMonth3Total.ReportMonth,
NZ([qryMonth1Total].[MonthlyRev],0) +
NZ([qryMonth2Total].[MonthlyRev],0) +
NZ([qryMonth3Total].[MonthlyRev],0) AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

Now I just need to duplicate for the full year (or potentially 18 months),
and this is going to get l-o-n-g... but it works!

Thanks again,
Steve

KARL DEWEY said:
Try this --
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL
SELECT qryMonth2Total.ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total
UNION ALL
SELECT qryMonth3Total.ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


SteveS said:
I think this is what you mean, which does give me the running total for
MonthlyCost:

SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL
SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total
UNION ALL
SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

This code works, but still gives me the following issues:
- Instead of showing the dates for month 2 and month 3, it says "ALL" - how
can I change the code to display the date? (I tried changing the "ALL" AS
ReportMonth statement a few ways but just got errors.)
- It's not showing me any results for the MonthlyRev (I assume because of
the "NULL as MonthlyRev" statement.) How can I change this to show both the
MonthlyRev running total and the MonthlyCost running total?

We're getting there - thanks!
Steve

KARL DEWEY said:
Ok, just apply what I did in the last union to the second using first and
second queries.
Then use the three in the third, etc.

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

Thanks for the response. This worked, but not in the way I would like. It
gave me a total result at the end (and only for the cost). However, instead
of the output looking like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $7500 $400
03/01/2007 $3500 $350
ALL $1300

I'm looking for a running monthly total like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $12500 $950
03/01/2007 $16000 $1300
etc.

I hope that clarifies. Is something like this possible in a union query?
Thanks again for your help!
Steve

:

My error --
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


:

Try this ---
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
Sum(NZ([qryMonth1Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth2Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth3Total].[MonthlyCost]),0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

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.

This works very well for showing me monthly revenues vs. costs. However, I
would also like to be able to create a similar Union Query that shows a
running sum of the monthly revenue and costs. (Note: for reasons I won't get
into here, I have to have each month's data in its own query.)

Is it possible in a Union query to add, for example, [MonthlyRev] from
qryMonth1Total into [MonthlyRev] qryMonth2Total, then the new total from
qryMonth2Total into [MonthlyRev] qryMonth3Total, etc. to give me the running
total I'd like to see?

Thanks!
P.S. Relatively new (and self-taught) in Access, so the simpler the better. :)
Click to expand...
Click to expand...
Click to expand...
 
S

SteveS

I've run into a problem using this method. When I stretch this out to 6
months (I would like to go to at least 12) I get an error message that the
"Query is too complex". Is there another suggested method where I could
reduce the number of fields in the Select clauses? (e.g. Instead of adding
every month over and over again, just have the month refer to the total of
the previous month rather than all previous months?)

KARL DEWEY said:
Try this --
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL
SELECT qryMonth2Total.ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total
UNION ALL
SELECT qryMonth3Total.ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


SteveS said:
I think this is what you mean, which does give me the running total for
MonthlyCost:

SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL
SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total
UNION ALL
SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

This code works, but still gives me the following issues:
- Instead of showing the dates for month 2 and month 3, it says "ALL" - how
can I change the code to display the date? (I tried changing the "ALL" AS
ReportMonth statement a few ways but just got errors.)
- It's not showing me any results for the MonthlyRev (I assume because of
the "NULL as MonthlyRev" statement.) How can I change this to show both the
MonthlyRev running total and the MonthlyCost running total?

We're getting there - thanks!
Steve

KARL DEWEY said:
Ok, just apply what I did in the last union to the second using first and
second queries.
Then use the three in the third, etc.

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

Thanks for the response. This worked, but not in the way I would like. It
gave me a total result at the end (and only for the cost). However, instead
of the output looking like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $7500 $400
03/01/2007 $3500 $350
ALL $1300

I'm looking for a running monthly total like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $12500 $950
03/01/2007 $16000 $1300
etc.

I hope that clarifies. Is something like this possible in a union query?
Thanks again for your help!
Steve

:

My error --
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


:

Try this ---
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
Sum(NZ([qryMonth1Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth2Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth3Total].[MonthlyCost]),0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

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.

This works very well for showing me monthly revenues vs. costs. However, I
would also like to be able to create a similar Union Query that shows a
running sum of the monthly revenue and costs. (Note: for reasons I won't get
into here, I have to have each month's data in its own query.)

Is it possible in a Union query to add, for example, [MonthlyRev] from
qryMonth1Total into [MonthlyRev] qryMonth2Total, then the new total from
qryMonth2Total into [MonthlyRev] qryMonth3Total, etc. to give me the running
total I'd like to see?

Thanks!
P.S. Relatively new (and self-taught) in Access, so the simpler the better. :)
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try having each of the queries do the running sum and just union.
--
KARL DEWEY
Build a little - Test a little


SteveS said:
I've run into a problem using this method. When I stretch this out to 6
months (I would like to go to at least 12) I get an error message that the
"Query is too complex". Is there another suggested method where I could
reduce the number of fields in the Select clauses? (e.g. Instead of adding
every month over and over again, just have the month refer to the total of
the previous month rather than all previous months?)

KARL DEWEY said:
Try this --
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL
SELECT qryMonth2Total.ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total
UNION ALL
SELECT qryMonth3Total.ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


SteveS said:
I think this is what you mean, which does give me the running total for
MonthlyCost:

SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL
SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total
UNION ALL
SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

This code works, but still gives me the following issues:
- Instead of showing the dates for month 2 and month 3, it says "ALL" - how
can I change the code to display the date? (I tried changing the "ALL" AS
ReportMonth statement a few ways but just got errors.)
- It's not showing me any results for the MonthlyRev (I assume because of
the "NULL as MonthlyRev" statement.) How can I change this to show both the
MonthlyRev running total and the MonthlyCost running total?

We're getting there - thanks!
Steve

:

Ok, just apply what I did in the last union to the second using first and
second queries.
Then use the three in the third, etc.

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

Thanks for the response. This worked, but not in the way I would like. It
gave me a total result at the end (and only for the cost). However, instead
of the output looking like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $7500 $400
03/01/2007 $3500 $350
ALL $1300

I'm looking for a running monthly total like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $12500 $950
03/01/2007 $16000 $1300
etc.

I hope that clarifies. Is something like this possible in a union query?
Thanks again for your help!
Steve

:

My error --
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


:

Try this ---
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
Sum(NZ([qryMonth1Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth2Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth3Total].[MonthlyCost]),0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

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.

This works very well for showing me monthly revenues vs. costs. However, I
would also like to be able to create a similar Union Query that shows a
running sum of the monthly revenue and costs. (Note: for reasons I won't get
into here, I have to have each month's data in its own query.)

Is it possible in a Union query to add, for example, [MonthlyRev] from
qryMonth1Total into [MonthlyRev] qryMonth2Total, then the new total from
qryMonth2Total into [MonthlyRev] qryMonth3Total, etc. to give me the running
total I'd like to see?

Thanks!
P.S. Relatively new (and self-taught) in Access, so the simpler the better. :)
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try having the current running sum in each query and only union in the union.
--
KARL DEWEY
Build a little - Test a little


SteveS said:
I've run into a problem using this method. When I stretch this out to 6
months (I would like to go to at least 12) I get an error message that the
"Query is too complex". Is there another suggested method where I could
reduce the number of fields in the Select clauses? (e.g. Instead of adding
every month over and over again, just have the month refer to the total of
the previous month rather than all previous months?)

KARL DEWEY said:
Try this --
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL
SELECT qryMonth2Total.ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total
UNION ALL
SELECT qryMonth3Total.ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


SteveS said:
I think this is what you mean, which does give me the running total for
MonthlyCost:

SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL
SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total
UNION ALL
SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

This code works, but still gives me the following issues:
- Instead of showing the dates for month 2 and month 3, it says "ALL" - how
can I change the code to display the date? (I tried changing the "ALL" AS
ReportMonth statement a few ways but just got errors.)
- It's not showing me any results for the MonthlyRev (I assume because of
the "NULL as MonthlyRev" statement.) How can I change this to show both the
MonthlyRev running total and the MonthlyCost running total?

We're getting there - thanks!
Steve

:

Ok, just apply what I did in the last union to the second using first and
second queries.
Then use the three in the third, etc.

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

Thanks for the response. This worked, but not in the way I would like. It
gave me a total result at the end (and only for the cost). However, instead
of the output looking like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $7500 $400
03/01/2007 $3500 $350
ALL $1300

I'm looking for a running monthly total like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $12500 $950
03/01/2007 $16000 $1300
etc.

I hope that clarifies. Is something like this possible in a union query?
Thanks again for your help!
Steve

:

My error --
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


:

Try this ---
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
Sum(NZ([qryMonth1Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth2Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth3Total].[MonthlyCost]),0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

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.

This works very well for showing me monthly revenues vs. costs. However, I
would also like to be able to create a similar Union Query that shows a
running sum of the monthly revenue and costs. (Note: for reasons I won't get
into here, I have to have each month's data in its own query.)

Is it possible in a Union query to add, for example, [MonthlyRev] from
qryMonth1Total into [MonthlyRev] qryMonth2Total, then the new total from
qryMonth2Total into [MonthlyRev] qryMonth3Total, etc. to give me the running
total I'd like to see?

Thanks!
P.S. Relatively new (and self-taught) in Access, so the simpler the better. :)
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try having the sum in each query and just union in the union.
--
KARL DEWEY
Build a little - Test a little


SteveS said:
I've run into a problem using this method. When I stretch this out to 6
months (I would like to go to at least 12) I get an error message that the
"Query is too complex". Is there another suggested method where I could
reduce the number of fields in the Select clauses? (e.g. Instead of adding
every month over and over again, just have the month refer to the total of
the previous month rather than all previous months?)

KARL DEWEY said:
Try this --
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL
SELECT qryMonth2Total.ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total
UNION ALL
SELECT qryMonth3Total.ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


SteveS said:
I think this is what you mean, which does give me the running total for
MonthlyCost:

SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL
SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total
UNION ALL
SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

This code works, but still gives me the following issues:
- Instead of showing the dates for month 2 and month 3, it says "ALL" - how
can I change the code to display the date? (I tried changing the "ALL" AS
ReportMonth statement a few ways but just got errors.)
- It's not showing me any results for the MonthlyRev (I assume because of
the "NULL as MonthlyRev" statement.) How can I change this to show both the
MonthlyRev running total and the MonthlyCost running total?

We're getting there - thanks!
Steve

:

Ok, just apply what I did in the last union to the second using first and
second queries.
Then use the three in the third, etc.

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

Thanks for the response. This worked, but not in the way I would like. It
gave me a total result at the end (and only for the cost). However, instead
of the output looking like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $7500 $400
03/01/2007 $3500 $350
ALL $1300

I'm looking for a running monthly total like this:

ReportMonth MonthlyRev MonthlyCost
01/01/2007 $5000 $550
02/01/2007 $12500 $950
03/01/2007 $16000 $1300
etc.

I hope that clarifies. Is something like this possible in a union query?
Thanks again for your help!
Steve

:

My error --
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
NZ([qryMonth1Total].[MonthlyCost],0) +
NZ([qryMonth2Total].[MonthlyCost],0) +
NZ([qryMonth3Total].[MonthlyCost],0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


:

Try this ---
SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth1Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth2Total
UNION ALL SELECT ReportMonth, MonthlyRev, MonthlyCost
FROM qryMonth3Total
UNION ALL SELECT "ALL" AS ReportMonth, NULL AS MonthlyRev,
Sum(NZ([qryMonth1Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth2Total].[MonthlyCost]),0) +
Sum(NZ([qryMonth3Total].[MonthlyCost]),0) AS MonthlyCost
FROM qryMonth1Total, qryMonth2Total, qryMonth3Total;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

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.

This works very well for showing me monthly revenues vs. costs. However, I
would also like to be able to create a similar Union Query that shows a
running sum of the monthly revenue and costs. (Note: for reasons I won't get
into here, I have to have each month's data in its own query.)

Is it possible in a Union query to add, for example, [MonthlyRev] from
qryMonth1Total into [MonthlyRev] qryMonth2Total, then the new total from
qryMonth2Total into [MonthlyRev] qryMonth3Total, etc. to give me the running
total I'd like to see?

Thanks!
P.S. Relatively new (and self-taught) in Access, so the simpler the better. :)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Running Sum Within Union Query 0
Adding running total to union query 2
SUM in a UNION query 2
Union Query 1
Union query not displaying select statement 1
Duplicates in union query 3
union query problem 16
union query issue 3

Top