Amalgamate tables

[sam]

Hi all,
I have a query problem that has puzzled me all
morning.
I have two tables: Invoices and Receipts. I am
using two fields Batch_number and amount (this is the sum
for each batch). What I need is to amalgamate them in some
way. For instance I want to bring them together and where
there is a common Batch_number add it.
Hope I've explained this ok!
Thanks
Sam

 

[Michel Walsh]

Hi,


Access 2000 or more recent:

SELECT BatchID, SUM( amount )

FROM ( SELECT batchID, amount FROM receipts
UNION ALL
SEELCT batchID, - amount FROM invoices ) As a

GROUP BY BatchID



For Access 97, make a save query with the UNION ALL query, and use

SELECT BatchID, SUM(amount)
FROM mySavedQuery
GROUP BY BatchID


rather that the virtual table as illustrated here up.




Hoping it may help,
Vanderghast, Access MVP