[AccessD] SQL Union Query Help

Hi Paul

Use the union query as recordsource for a new query where you Group By
CustNum and Sum the other fields.

/gustav

> I'm trying to run the SQL union query and would like to have this shown into one record...Right now, the results are displayed into two records.  This query is run against our SQL Server database.
> Also, in Oracle I'm able to use a SQL wrapper which helps my situation but won't work in SQL Server...For example, select CustNum, Shipments, Revenue, AdjCost from(...SQL query below).

> Eventually, I will be removing the Customer Number where clause to run against all customers.

> Thanks,
> Paul Millard
> San Jose, CA


> select
> t1.ShipNum as CustNum,
> Count(*) as Shipments,
> Sum(t1.Rev) as Revenue,
> Sum(t1.AdjCost) as AdjCost
> from
> tblRawAccess as t1
> where
> t1.ShipNum = 'KKL2712SKM' and 
> t1.FbType = 'P' and 
> t1.Month = '0306'
> group by
> t1.ShipNum
> union
> select
> t1.CsgnNum as CustNum,
> Count(*) as Shipments,
> Sum(t1.Rev) as Revenue,
> Sum(t1.AdjCost) as AdjCost
> from
> tblRawAccess as t1
> where
> t1.CsgnNum = 'KKL2712SKM' and 
> t1.FbType = 'C' and 
> t1.Month = '0306'
> group by
> t1.CsgnNum

> query results....
> KKL2712SKM      1       72.910003662109375      129.64300537109375
> KKL2712SKM      29      4155.4299850463867      3413.1434745788574

> need into 
> KKL2712SKM 30   4228                    3543