MS Access UNION ALL conversion (1 Viewer)

imfarhan

New member
Local time
Today, 16:53
Joined
Aug 6, 2010
Messages
5
Hi All,
I've a two tables (new) and (Old) and like to compare the values the sql and output shown below

Code:
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 :
TableNameCost CentreTotalCount
TableA12345100
TableB12345110


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?
TableOldTableNewCostCentreOld Table CountNew Table CountDiffValue Diff
TableATableB1234510011010£9999

Hope make sense
Thanks for your help
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:53
Joined
Oct 29, 2018
Messages
21,358
Hi. Looks like you can use INNER JOIN on CostCentre, no?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:53
Joined
Feb 28, 2001
Messages
27,001
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:

Code:
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.

Code:
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.
 

Users who are viewing this thread

Top Bottom