Querying 3 or more tables at once

[isegui2004]

I have three tables that have identical fields with data from different time periods (ie 2021, 2022, 2023). Is there a way to join all three tables to be able to create a query that pulls from all tables?

[Micron]

If the info is basically the same but you have a table for each year, then this is the wrong design approach and that needs to be fixed. Otherwise join each table in a query, using whatever fields contain the same related information in at least pairs of tables (i.e. tblA can be related to tblB and tblB to tblC, or perhaps tblA to tblC). I expect that the design is incorrect, thus you might have to post some data samples (copy/paste from Excel is much better than pics) in order to provide focused answers.

[xps35]

I agree with Micron that this is bad design. But I don't think his solution is right. Do not join the tables but union them. Something like:

SELECT * FROM T1
UNION 
SELECT * FROM T2 
UNION 
SELECT * FROM T3

[CJ_London]

And I agree with xps35 except I would use UNION ALL. UNION on it's own will remove duplicates. It is quite possible you have no duplicated data within the same year or across the years, but on the other hand, you might.

Note that union queries prevent the use of indexing so using this as a base query which other queries reference will be slow compared with a normalised structure (which in principle simply requires a 'year' column in a table - or perhaps you already have a date column from which a year can be determined)

[isladogs]

As stated above, this is a poor design choice

I don't think using UNION/UNION ALL query or a query with 2 JOINS is the best solution.
Apart from anything else, the UNION queries will be read only (as it probably would be using JOINS)

The tables have identical fields so the data should be in one table with an additional field if necessary to denote the year.
If the 3 tables already contain a date field, doing that isn't even necessary.

By all means, use a UNION ALL query as the first step, then use a MAKE TABLE or append query to create a new single table from that

[isegui2004]

These are very big datasets. I used to have a master table but it kept crashing because of size. I’m trying to find an alternative where I can query the data from multiple tables without combining all the data into one table.

[Micron]

I don't think your db crashed because of the size of one table. The limit is 2Gb including overhead. 3 tables can reach the size limit sooner than one table with all the records because each table adds overhead. Your design likely caused the crashing, but there's no way to know exactly what the cause might have been - unless maybe you got a helpful error message when it crashed.

As for the way to get the data out, I have to admit I likely missed the mark.