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

[LadyDee]

Hello Isladogs,



Thanks for the explanation. I'll try the SQL given in #1 and see if that changes anything.

[June7]

UNION might be appropriate for search operation. I've never tried a UNION with more than a dozen fields. Use of * in UNION only works if tables have same number of fields and they are all in same order in table design. Again, we know next to nothing of your schema.

However, with your volume of records, UNION performance will likely not be satisfactory anyway.

Colin's Option 1 is a Cartesian structure - expect it to fail with tables of 800K records.

[LadyDee]

errorMsg: Too many fields defined - the notation of adding ".*" per table did not change anything, Let me digest what is posted here and see what can be done.

Best Wishes everyone! - I'll be back with either more questions or the discovered resolution

[June7]

I suggest the only real solution is writing these 3 tables to a normalized table - which is basically what a UNION query would emulate.

With UNION, you would need to include a calculated field to provide a category value to identify the source set. First SELECT defines field names and data types and 50 SELECT lines are allowed. Example:

SELECT "A" AS Cat, Field1, Field2 FROM TableA
UNION SELECT "B", Field1, Field2 FROM TableB
UNION SELECT "C", Field1, Field2 FROM TableC;

Suggest you test performance with a limited selection of fields.

[isladogs]

errorMsg: Too many fields defined - the notation of adding ".*" per table did not change anything, Let me digest what is posted here and see what can be done.

Best Wishes everyone! - I'll be back with either more questions or the discovered resolution

I didn't suggest that #1 was in any way a good solution.
I also didn't suggest that rewriting it would be more likely to succeed. My comment was that the SQL would be clearer

Cartesian queries are only rarely a good solution.
Union queries can work with many fields if properly constructed but needing to use them is often a workaround for poor database design

The best solution of your 4 queries would be a properly constructed JOIN.

However, the reality is that you need to first learn how to create properly normalised tables, then how to construct queries correctly either using SQL or the query designer
What you have now isn't going to work as everyone so far has been saying to you.

[madpiet]

I suggest the only real solution is writing these 3 tables to a normalized table - which is basically what a UNION query would emulate.

With UNION, you would need to include a calculated field to provide a category value to identify the source set. First SELECT defines field names and data types and 50 SELECT lines are allowed. Example:

SELECT "A" AS Cat, Field1, Field2 FROM TableA
UNION SELECT "B", Field1, Field2 FROM TableB
UNION SELECT "C", Field1, Field2 FROM TableC;

Suggest you test performance with a limited selection of fields.

If you know there won't be any duplicate records, you should use UNION ALL instead. UNION removes duplicates (and I think sorts), and that's computationally expensive. Don't do it unless you really have to.

[LadyDee]

Thank you for all the feedback.
My ultimate solution was to import the three access tables into Sql Server and UNION ALL them onto one new table - - so simple and I was happy to see the Successful message when it was done.
Dropped the Access PK Column and then created a new PK in SSMS. I didn't link it to the Access frontend as yet though.

[madpiet]

You may have to use SQL Server for your tables. They're too wide for Access. SQL Server can handle (I think) 1024 columns per table. The free version can handle (I think) 10GB of data.

I think Rebecca Riordan wrote an article like 20 years ago about doing joins like you're describing. I'm pretty sure you need to have one "master" table and a bunch of 1:1 children. (I know, sounds odd, because a parent:child relationship is almost always one to many.) The "parent" or master table would have an incrementing primary key, and others would be unique and non-autonumber, because the relationship would determine the value stored. Then I think it would work.

If you were to do the same in SQL Server, you could just declare referential integrity within the table definition with foreign key constraints. (So you'd create the "parent" table first, then GO and then create the 1:1 children). The nice thing about SQL Server is that you can use CROSS APPLY to sort of unpivot repeating groups.

Not sure how to do the same in Access, though.