Access 2010 Multi Table Report Combining Fields with same info

Question

I am creating a Microsoft Access database for my office and am trying to create a Report to show every purchase for the whole office. My office is split into 4 teams, and, due to the differing purchases per team, 4 tables were created for each team with fields pertaining to their purchases. There are a few fields however that span across all teams, such as: Purchase Number, Purchaser, etc..

My question is how do I create one report with a list of all the purchases in the office that will combine these fields from the separate tables into one field? None of the info in these fields will be duplicated across, I just need the field on the report to contain information from all 4 tables.

Thanks!

Answer 1

What could probably help in this case would be to use a UNION query. In this type of query you can combine the records of different tables, however each SELECT part of the UNION must yield the same fields, therefore provide empty dummy fields for tables that are missing these fields. An ORDER By clause can follow the last SELECT

SELECT Common1, Common2, SpecialA, null AS SpecialB FROM TableA
UNION ALL
SELECT Common1, Common2, null AS SpecialA, SpecialB FROM TableB
UNION ALL
SELECT ...
ORDER BY ...