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

[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.

 

[KARL DEWEY]

Union query.

 

[torex]

How do I write/create the union statement?? thanks

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

 

[KARL DEWEY]

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.

How do I write/create the union statement?? thanks

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

Union query.

 

[John W. Vinson]

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.

See the online help for UNION.

For a more detailed answer please post a description of the tables.

 

[John W. Vinson]

How do I write/create the union statement?? thanks

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

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!