Query to combine the records of 3 identical structure tables of DB.

  • Thread starter Thread starter torex
  • Start date Start date
T

torex

Hi, in a table I have got 3 tables with identical structure but different
data. How do I create a query to aggregate all the records of the 3 tables?
Thanks.
 
How do I write/create the union statement?? thanks

If Table 1 is [spring]
Table 2 is [summer]
Table 3 is [winter]
 
Create a query using first table, open in deign view, click on SQL View, edit
to add other tables like this --
SELECT [field1], [field2], [field3]
FROM [spring]
UNION ALL SELECT [field1], [field2], [field3]
FROM [summer]
UNION ALL SELECT [field1], [field2], [field3]
FROM [winter];

You will not be able to look at it in design view when complete.

torex said:
How do I write/create the union statement?? thanks

If Table 1 is [spring]
Table 2 is [summer]
Table 3 is [winter]


KARL DEWEY said:
Union query.
Click to expand...
Click to expand...
 
Hi, in a table I have got 3 tables with identical structure but different
data. How do I create a query to aggregate all the records of the 3 tables?
Thanks.
Click to expand...

See the online help for UNION.

For a more detailed answer please post a description of the tables.
 
How do I write/create the union statement?? thanks

If Table 1 is [spring]
Table 2 is [summer]
Table 3 is [winter]
Click to expand...

SELECT * FROM [Spring]
UNION
SELECT * FROM [Summer]
UNION
SELECT * FROM [Winter]

Note that UNION will remove duplicates - i.e. if there are records in Spring
and Summer which are identical in all fields, you'll see only one of those
records. Use UNION ALL if you want to avoid this duplicate checking (and if
you're sure there will never be duplicates, use UNION ALL to make the query
run faster since Access won't need to check).

I hope you're in the process of combining these tables into one - storing data
(a season) in tablenames is very bad design, for exactly the reason you're now
discovering!
 
Back
Top