Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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!

share|improve this question

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 ...
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .