Combine data from 4 tables in one.

I

impacter

I have four tables with identical fields. (Journalist, Publication,
Consultant, Client, Date and Remarks) The reason for having this is
that they cover different media, e.g. weekly magazines, monthly
magazines, trade publications and newspapers. Can anybody advise me
how I can combine all the data in one table, query or report with the
same fields?
 
J

John Spencer

First, you should just add one more field to your table field Media and then
use that to identify the specific medium associated with each record. Then
you could put all the records in one table and if another medium needed to
be added you wouldn't have to create a whole new table.

A union query will solve your problem for combining the four tables
together. The union would look something like the following. The result of
this is what your ONE table should end up looking like

SELECT NewsPapers.*, "Newspapers" as Media
FROM Newspapers
UNION ALL
SELECT Monthlies.*, "Monthlies" as Media
FROM Monthlies
UNION ALL
SELECT Weeklies.*, "Weeklies" as Media
FROM Weeklies
UNION ALL
SELECT Trades.*, "Trades" as Media
FROM Trades
Order By Media, Publication
 
J

John Spencer

Help on on UNION queries should be available in the on line help. In the
database window, select help from the menubar. In the Answer wizard type what
is a union query. In Access 2000 that should come up with What is an SQL query
and when should you use one. Click on that and then you should see a link to
Union queries.
 
Top