Carry Over Balances

I have a purchase table and a sales table. I used a union query to join
them and using CASE statement, I have been able to extract purchase and
sales amounts. Now, I can now generate the following report using a matrix:
JAN FEB
Purchase product x xx xx
product y xx xx
product z xx xx
total xx xx
Sales product x xx xx
product y xx xx
product z xx xx
total xx xx
I need to put an Inventory group at the bottom so that it looks like this:
JAN FEB
Purchase product x xx xx
product y xx xx
product z xx xx
total xx xx
Sales product x xx xx
product y xx xx
product z xx xx
total xx xx
Inventory product x xx xx (Purchase-Sales)
product y xx xx
product z xx xx
total xx xx
My problem is:
1. How to add the Inventory group efficiently
2. How to carry over the balances of Jan and add it to Feb... and so on
Would appreciate any inputs. Thanks!
Message posted via http://www.webservertalk.comHi
Have you looks at the ROLLUP function in Books Online?
Posting DDL (Create table statements etc...)
http://www.aspfaq.com/etiquette.asp?id=5006 and example data as insert
statements http://vyaskn.tripod.com/code.htm#inserts
along with your current code would help to answer the second question. You
may need to use a derived table to get the monthly values and/or us a
inequality to get the cumulative total e.g JAN is '< 1-Feb', Feb is '<
1-Mar' etc...
John
"Randy via webservertalk.com" wrote:

> I have a purchase table and a sales table. I used a union query to join
> them and using CASE statement, I have been able to extract purchase and
> sales amounts. Now, I can now generate the following report using a matri
x:
> JAN FEB
> Purchase product x xx xx
> product y xx xx
> product z xx xx
> total xx xx
> Sales product x xx xx
> product y xx xx
> product z xx xx
> total xx xx
> I need to put an Inventory group at the bottom so that it looks like this:
> JAN FEB
> Purchase product x xx xx
> product y xx xx
> product z xx xx
> total xx xx
> Sales product x xx xx
> product y xx xx
> product z xx xx
> total xx xx
> Inventory product x xx xx (Purchase-Sales)
> product y xx xx
> product z xx xx
> total xx xx
> My problem is:
> 1. How to add the Inventory group efficiently
> 2. How to carry over the balances of Jan and add it to Feb... and so on
> Would appreciate any inputs. Thanks!
> --
> Message posted via http://www.webservertalk.com
>