Select * From 3 tables (split and contain identical fields) into subform

[LadyDee]

Hello,
Using: Win 10/ Access 2019

We needed to increase the size of our dataset, the fields are 235 columns and the rows total around 880,000. The Rows did not change - only the fields/columns increased.
I successfully split the dataset from excel, into 3 Access tables, and linked them into my Access frontend.
Previously upon open, the entire dataset had displayed in a subform in Main. The data, although nearing 2GB, had fit in 1 table and the Query: "SELECT * FROM tblName;" worked.
Now -- I am unable to display the entire dataset (1 table will still work as before (done as a test)). I'm having difficulty in displaying all 3 tables. I write the SQL directly into the SQL View and this is what happens:



1. SELECT * FROM tblName1, tblName2, tblName3; errorMsg: Query is too complex
2. SELECT * FROM tblName1, tblName2; (as a test used only 2 tables for remainder) errorMsg: Too many fields defined
3. SELECT * FROM tblName1 JOIN tblName2; errorMsg: Syntax error in FROM clause
4. SELECT * FROM tblName1 UNION ALL SELECT * FROM tblName2; errorMsg: Too many fields defined

Also, each split table has it's own AutoNumber key definition. I just simply would like to: SELECT * from each table, and display in the subform.
Can someone pinpoint the correct way to achieve this? I don't think there is a need for complex JOINS because each file has the exact same data Fields - only rowcounts differ (summing to the fore mentioned 880,000)

Thank You

[madpiet]

[QUOTE=LadyDee;522442]Hello,
Using: Win 10/ Access 2019

We needed to increase the size of our dataset, the fields are 235 columns and the rows total around 880,000. The Rows did not change - only the fields/columns increased.

----

Don't be afraid to normalize!!! What entity requires 235 distinct attributes to describe it fully? Do any columns have numeric subscripts? one way to help us answer your questions would be to run some code to print out a field list in the table.

public sub ListFieldNames(byval strTableName as string)
dim tdf as tabledef
dim i as integer

set tdf = currentdb.tabledefs(strTableName)
for i = 0 to tdf.fields.count - 1
debug.print tdf.fields(i).name
next i

set tdf = nothing

end sub

[LadyDee]

Hello & thank you for your reply.

Unfortunately, I am under contract and cannot give sensitive info publicly. It contains 20 year forecasts of various categories, including GIS info, Land Values, $ and % changes
I feel like it hasn't surpassed the maximum of 255 columns, what is the best way to simply display from the 3 files?

[Gicu]

Look up union query.

Cheers,

[ArviLaanemets]

Unfortunately, I am under contract and cannot give sensitive info publicly. It contains 20 year forecasts of various categories, including GIS info, Land Values, $ and % changes
I feel like it hasn't surpassed the maximum of 255 columns, what is the best way to simply display from the 3 files?

So essentially you have 3 tables, where in every table are some fields which determine where the rest of record info belongs into (i.e. determines some object), and then are 20 different columns for some kind of data for every of those 20 years, and then other 20 columns for some other kind of data, etc.

Advices given here can help you for this year, but you (or someone else when you are clever enough) has to redesign this solution in next year anew (because there are new columns added), and then in next year again, etc. All this hassle until at some year in future the limit of 255 fields will be reached, and this DB will be finally not usable anymore!

The solution to get this done for now and for all future years is to redesign the database, and to import all historical data into new tables. And then design a new report based on new structure, where user can select a year, and get all necessary info about this year - without any need to redesign the report.

The structure may be something like:
tblObjects: ObjectId, ObjectName, ... (there may be any number of fields which characterize this object, and don't change over time - or only current values of them are needed);
And depending on data types for yearly info (text, or texts of different lengths for different info types, or numbers, or different number types for different info types, etc.) either
a single table of yearly info, like:
tblObjectYearlyInfo: ObjectYearlyInfoID, ObjectID, YearNo, InfoType, InfoValue;
or a table for every info type, like:
tblObjectYearlyInfoType1: ObjectYearlyInfoType1ID, ObjectID, YearNo, InfoValue;
tblObjectYearlyInfoType2: ObjectYearlyInfoType2ID, ObjectID, YearNo, InfoValue;
... etc. for every info type.

[Micron]

Limit for Access query is 255 fields and 1GB of data. IIRC, 2GB limit does not apply to queries, and where that limit applies, it includes overhead (thus also bloat).

Sql examples 1,2 and 4 look like Cartesian queries which would most certainly drive you over the 1GB limit with 800K+ records and so many fields. In the last case maybe UNION ALL isn't helping either, as opposed to just UNION. Is this Access sql, because the JOIN statement looks wrong to me? Another issue you may have introduced by splitting is now having too many joins and/or indexes for the new queries.

I'm with anyone who suspects the db is poorly designed, and if you are experienced (the fact that you're contracted would suggest that) I wonder why you would even take this on, unless it is not as bad as we suspect.

[madpiet]

Hello & thank you for your reply.

Unfortunately, I am under contract and cannot give sensitive info publicly. It contains 20 year forecasts of various categories, including GIS info, Land Values, $ and % changes
I feel like it hasn't surpassed the maximum of 255 columns, what is the best way to simply display from the 3 files?

I don't want your data​ just the table structure.

[LadyDee]

Thanks for your reply. The database will not grow, its data is static and for reference. A snapshot of future forecasts.

[madpiet]

I was only asking because you're seriously pushing the limits of Access. if you're going to just use Access for storage, it may work. (Although you could likely restructure and then use a query as the source for whatever reports/analysis you're doing.)

But you do what works for you.

[LadyDee]

Hi Micron & everyone,
This is intended to be Access SQL. So, Access Queries can only support up to 1GB of responses to a Query?? Am I understanding this correctly?
My app worked fine with 134 fields/Columns initially. More Fields were added (containing calculations). The dataset is static and will not grow. What was 1 table is now broken into 3 tables. What Access SQL statement will Select all from each table?? Please give me the proper syntax. I'm not a novice - but have never had this particular scenario before. It seemed simple: Select * from table1, table2, table3 -- and the subform calls this query, but that select statement does not work.
Can someone tell me the proper SQL to accomplish this? I'll place the data in SQL Server as well to give my end user options.
Thank you

[June7]

If one table has been split into 3 tables, is this a 1-to-1 relationship? To pull all three tables back into one dataset so multi-year data is side-by-side, that means JOIN of 2 tables to 3rd acting as the "parent". These 3 tables need a common record ID to JOIN ON. What you attempted is a Cartesian relation of records - every record of each table associates with every record of other table(s). If 3 tables have 3 records each that means the query returns 27 records.

So why was table split to begin with?

Again, example of data structure would be helpful. You can build sample tables in post with table builder on Advanced post editor toolbar. Can even copy/paste from Access or Excel. Or attach files (Access or Excel, etc).

Example of copy/paste Access table - this can be edited in post to change values:

ID	ColorName	ColorCodeDec	ColorCodeRGB	ColorCodeHex	HexGBR
1	Black	0	000,000,000	000000	&H0
2	Red	255	255,000,000	FF0000	&HFF
3	Green	65280	000,255,000	00FF00	&HFF0000
4	Yellow	65535	255,255,000	FFFF00	&HFF00FF
5	Blue	16711680	000,000,255	0000FF	&HFF00
6	Magenta	16711935	255,000,255	FF00FF	&HFFFF
7	Cyan	16776960	000,255,255	00FFFF	&HFFFF00
8	White	16777215	255,255,255	FFFFFF	&HFFFFFF

Madpiet, for some reason your quotes are not getting the QUOTE closing tag. And code is not between CODE tags.

[isladogs]

Agree with previous comments about your database structure.
Your suggested queries all have serious flaws:

1. SELECT * FROM tblName1, tblName2, tblName3; errorMsg: Query is too complex
This should work though it would be better written as
SELECT tblName.*, tblName2.*, tblName3.* FROM tblName1, tblName2, tblName3;

However it is a no join query AKA Cartesian join and will produce 1 record for each combination of records in the 3 tables.
So if you have 100, 20,000 and 500,000 rows in each, the query will output 100 * 20,000 * 500,000 = 1,000,000,000,000 records
Access will probably crash well before that completes
In any case, it will also produce a field for each and every field in the 3 tables which will in your case exceed the field limit

2. SELECT * FROM tblName1, tblName2; (as a test used only 2 tables for remainder) errorMsg: Too many fields defined
Same comments as for 1 - you now know why too many fields are defined

3. SELECT * FROM tblName1 JOIN tblName2; errorMsg: Syntax error in FROM clause
Joins have to be on matching fields in each table and you must specify the join type INNER/LEFT/RIGHT.
For example:
SELECT * FROM tblName1 INNER JOIN tblName2 ON Name1_ID = Name2_ID

4. SELECT * FROM tblName1 UNION ALL SELECT * FROM tblName2; errorMsg: Too many fields defined
UNION queries only run if the 2 tables have the same number of fields with matching datatypes for each pair of fields in the order written.
The field names don't need to be identical

[CJ_London]

You are effectively talking about a 1 to 1 relationship between your tables. Assuming you have a unique ID in your excel spreadsheet then you need to include that in each of your tables. In database parlance this is typically known as a Primary Key (PK)

So assuming you have done this, your query would be

SELECT * FROM (tblName1 INNER JOIN tblName2 ON tblName1.PK = tblName2.PK) INNER JOIN tblName3 ON tblName1.PK = tblName3.PK

In this case your PK should simply be a long datatype, indexed, no duplicates rather than an autonumber.

To reduce the number of columns, you say excel contains a number of calculations. Depends what they are but typically could be calculated in Access rather than importing.

You cannot get over the 255 limit, so if your excel sheet extends to over this amount you will not be able to pick up the ID in the first column and columns over the 255 limit. All you could do is replicate the ID column at the start of each 'section'

Either way, this does not seem to be a good way of using a database, you should consider normalising the data

[LadyDee]

Thank you June7,
Each split table has it's own AutoNumber key definition.
I didn't think there would be a need for complex JOINS because each file has the exact same data Fields. I'm wondering JOIN ON what? ID? There's nothing consistent to join on that I can see. Each row contains unique information across the board.
The data is 1 long grouping of information that is unique to each AccoutNumber. No sameness within data consistently. The User is meant to query for the desired info - and all of that worked.
Someone mentioned Union All - but is this the correct syntax?
SELECT * FROM tbl1 UNION ALL SELECT * FROM tbl2; errorMsg: Too many fields defined

[Micron]

If 3 tables have 3 records each that means the query returns 27 records.

Imagine how many records that would be when I mentioned it in post 6. If the fields of the original table all have 800K records and there are 3 tables, that's 800Kx800Kx800K records

This is intended to be Access SQL.

Then the join statement is incorrect. Perhaps you should used the query designer.

So, Access Queries can only support up to 1GB of responses to a Query?

See "Query" at this link
https://support.microsoft.com/en-gb/...__toc296343503