MS Access UNION ALL conversion

[imfarhan]

Hi All,
I've a two tables (new) and (Old) and like to compare the values the sql and output shown below

select 'TableA' , costcentre, count(costcentre) as Total Count, sum(values) as TotalAmt from TableA group by costcentre
UNION ALL
select 'TableB' , costcentre, count(costcentre) as TotalCount,   sum(values) as TotalAmt  from TableB group by costcentre
costcentre

The output is like :

TableName	Cost Centre	TotalCount
TableA	12345	100
TableB	12345	110

The desire output i would like to use as follow without Crosstab or INNER JOIN as there is not key field exist in the table?

TableOld	TableNew	CostCentre	Old Table Count	New Table Count	Diff	Value Diff
TableA	TableB	12345	100	110	10	£9999

Hope make sense
Thanks for your help

 

[theDBguy]

Hi. Looks like you can use INNER JOIN on CostCentre, no?

 

[The_Doc_Man]

Because of the aggregates and grouping involved, create two separate queries and do the aggregation separately. One query will more or less match the first part of your offered UNION query, while the other one matches the other part of your sample query. Call them anything you like, as simple as QryA and QryB. Make them both have the form:

SELECT 'TableA' As SrcTbl, CostCenter, Count(CostCenter) As ToTCnt, Sum( Values ) As TotAmt FROM TableA Group By CostCenter;

and do the same for table B. The AS clauses MUST be there for my proposed query to work. You had two of them, but I added one that HAS to be there for this to work properly. Now create a third query that does what we call a Cartesian JOIN - with a proper WHERE clause to keep the join from getting out of hand. DO NOT DO THIS WITHOUT THE WHERE CLAUSE!!!! I'll toss in an ORDER BY just to organize it a bit.

SELECT A.SrcTbl As ASrc, B.SrcTbl As BSrc, A.CostCenter As CosCent, A.TotCnt As ACnt, B.TotCnt As BCnt, A.TotAmt As AAmt, B.TotAmt As BAmt
FROM QryA as A, QryB as B WHERE A.CostCenter = B.CostCenter ORDER BY CostCenter ;

You only need the cost center from one of the tables because that will be the same for both due to the WHERE clause. HOWEVER, this method WILL NOT show you any records where a cost center was created or abolished between whatever it is that delineates between "Old" and "New" in your two data sets. If you need to do some math in this query, add fields like ", (AAmt - BAmt) As DifAmt" and whatever else you need for the comparison.

One other comment: Watch out for your field "VALUES" since that is also an SQL keyword used in the "INSERT INTO" syntax. It is not a good idea to name a field with a word that matches a part of syntax for SQL. Just sayin', not criticizing.

NOTE: Whoops, I see you used the UK spelling of "centre" and I was just typing off-the-cuff and used the USA spelling. Spell the fields like you want and take my suggestion as just an example.