Compare Totals

[Dennis Chou]

I have two tables i'd like to compare

Table 1
IBM 100 shares
IBM 200 shares
APPLE 100 shares

Table 2
IBM 200 shares
DELL 100 shares

How can i get Table 3?

table 1 sum table 2 sum
IBM 300 shares 200 shares
AAPLE 200 shares 0
DELL 0 100 shares

 

[kc-mass]

First do a UNION query of the two tables.

Then do a summary query of that query; Group on CompanyName, Sum Shares.

Regards

Kevin

 

[Douglas J. Steele]

I'll ignore the obvious question of why on earth would you have two separate
tables like that...

Assuming the tables have fields CompanyName and ShareCount, you could create
two queries like:

SELECT CompanyName, Sum(ShareCount) AS TotalShares
FROM Table1
GROUP BY CompanyName

and

SELECT CompanyName, Sum(ShareCount) AS TotalShares
FROM Table2
GROUP BY CompanyName

Assuming you call those queries Query1 and Query2, you could then create a
third query:

SELECT Q1.CompanyName, Q1.TotalShares, Q2.TotalShares
FROM Query1 AS Q1 LEFT JOIN Query2 AS Q2
ON Q1.CompanyName = Q2.CompanyName
UNION
SELECT Q1.CompanyName, Q1.TotalShares, Q2.TotalShares
FROM Query2 AS Q2 LEFT JOIN Query1 AS Q1
ON Q1.CompanyName = Q2.CompanyName

 

[John Spencer]

Since Access does not support a full outer join you need to use a union query
to combine the information.

SELECT "table1" as TheSource
Company, Sum(Shares) as TotalShares
FROM [Table 1]
Group by "table 1", Company
UNION ALL
SELECT "table2" as TheSource
Company, Sum(Shares) as TotalShares
FROM [Table 2]
Group by "table 2", Company

Now you can use a Crosstab query for the final result
TRANSFORM Clng(Nz(First(TotalShares),0)) as ShareCount
SELECT Company
FROM [TheUnionQuery]
GROUP BY Company
PIVOT TheSource

Or you could use a different union query
SELECT Table1.Company, Sum(Table1.Shares) as T1Shares,
Sum(Table2.Shares) as t2Shares
FROM Table1 LEFT JOIN Table2
ON Table1.Company = Table2.Company
UNION ALL
SELECT Table2.Company, Sum(Table1.Shares) as T1Shares,
Sum(Table2.Shares) as t2Shares
FROM Table2 LEFT JOIN Table1
ON Table2.Company = Table1.Company
WHERE Table1.Company is Not Null


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County