Union Query

[mikesal57]

Hi All...

I have 3 linked tables , with many fields , that I need to query......

In the query below ,it has a few fields..

I need query to check other fields besides the ones listed

My question is can there be a "ALL" fields or do I need to list every one?

Thx and hope you under stand


I would like my

 

[Joe4]

I think you may be able to use "*" if all the fields in each of the 3 tables are exactly the same.

What happens if you try?, i.e.

SELECT Table1.*
FROM Table1

UNION ALL

SELECT Table2.*
FROM Table2

UNION ALL

SELECT Table3.*
FROM Table3

Also, if all three tables are exactly the same, why do you have the data spread out over three tables instead of all in one table?
Seems unnecessary and redundant, and makes the data much harder to work with, as you can see.

 

[mikesal57]

I think you may be able to use "*" if all the fields in each of the 3 tables are exactly the same.

What happens if you try?, i.e.

SELECT Table1.*
FROM Table1

UNION ALL

SELECT Table2.*
FROM Table2

UNION ALL

SELECT Table3.*
FROM Table3

Also, if all three tables are exactly the same, why do you have the data spread out over three tables instead of all in one table?
Seems unnecessary and redundant, and makes the data much harder to work with, as you can see.

Each Table has same fields but the data is different in all 3

and you correct , using 2 or 3 of these tables caused a "too many fields defined"

but it did work with one

thxs for your help

 

[Joe4]

So, why do you have 3 separate tables instead of all the data in one table?

 

[mikesal57]

So, why do you have 3 separate tables instead of all the data in one table?

I can fit like 2 years of data before the 2 gig limit
I have data from 2016
So its 5 tables I would like to query

 

[Joe4]

The 2 GIG limit is per database, so I don't see how splitting it over multiple tables would work, unless they are in different databases.
If they are, and you are linking them all in to one database, I expect big performance issues, as you try to work across a Union of multiple huge tables, that will not be Indexed.

You might be better off saving the data out to an external data source, like SQL, Oracle, or MySQL, and then just use Access as a front-end to the data source.

Access is great for housing data for small projects, but not for something of that magnitude.

 

[mikesal57]

The 2 GIG limit is per database, so I don't see how splitting it over multiple tables would work, unless they are in different databases.
If they are, and you are linking them all in to one database, I expect big performance issues, as you try to work across a Union of multiple huge tables, that will not be Indexed.

You might be better off saving the data out to an external data source, like SQL, Oracle, or MySQL, and then just use Access as a front-end to the data source.

Access is great for housing data for small projects, but not for something of that magnitude.

Understood ...thank you

 

[Micron]

Just going by the pics, you have a major design issue. I see reserved words used as object names, repeating fields, repeating data (e.g. you have portions of date data as fields), repeating tables, and in that query, no joins even though you say the tables are linked (??). That is a Cartesian query, and those can impose major demands on the pc's resources. The results of such a query without criteria are like this:
If table1, table2 and table3 each have 200 records the query result will be 200*200*200 or 8,000,000 records.

The db limit encompasses a lot of things besides data. Union queries are a sign of db design issues as well, but they are provided as a tool because sometimes you can't control the way data is given to you. If that's not the case for you, then you really ought to start over with the design, otherwise many things will always be a challenge.

For your consideration

Best approach for multiple look ups?

So, I want create a form that will utilise multiple datasets and am looking for some advice on the best way to do this. In as basic an explanation as possible… Step 1: I enter a postcode Step 2: It automatically looks up the LSOA from a very large dataset (too large for one sheet, spreads...

www.mrexcel.com