Merge Tables with Varying Column Names

[emzipoo4u]

Hi,

I am trying to achieve a union between 44 different tables, with varying number of columns between them and ordering of columns.
(around 120 columns per table). I Thinking about a union through SQL but that is going to be a huge task given the amount of columns and tables.
I have tried importing the access database into excel to do some kind of append but it times out due to the amount of data.
Any ideas on how to handle such a task ? Example below.

Table A

Part Number	Description	Colour	Length	Connector
PartA	This is part A	Red	20	square

Table B

Part Number	Description	No of Wires	Colour	Length	Width
PartB	This is part B	6	Blue	10	50

Required Result

Part Number	Description	Colour	Length	Connector	No of Wires	Width
PartA	This is part A	Red	20	square
PartB	This is part B	Blue	10		6	50

[June7]

Use dummy data or placeholder Null.

SELECT [Part Number], Description, Colour, Length, Connector, Null AS [No of Wires], Null AS Width FROM TableA
UNION SELECT [Part Number], Description, Colour, Length, Null, [No of Wires], Width FROM TableB;

120 fields will be a tedious slog but once and done.

[CJ_London]

Might be quicker using VBA to build your union query, perhaps use a metadata table to indicate what field goes where.

A union query of that size is likely to be quite slow so may be better to create a table and use multiple insert queries

[orange]

I haven't seen a table in a normalized database with "around 120 columns per table".
I agree with Ajax that some vba/automation to set up and test the query seems prudent, and less error prone.
Specifically, create the desired table structure, and a series of INSERT queries, based on a common structure and some parameters, to achieve your "merge".

[Gicu]

Agree with Ajax and Orange, loop through the TableDefs collection and then for each table loop through its field collection and add it to a table that has a FIELD_NAME field set to unique (at the same time at the data type to a FIELD_TYPE field); at the end of this loops you will have all the info to build in code (using a Create Table DDL statement or DAO tdf.Fields.Append) you "comprehensive" table holding all possible columns. Finally loop through the TableDefs a second time and use Insert Into statements with * as all columns of each source table would be present in the target table.

Please post back with your attempts if you get stuck.

Cheers,

[ArviLaanemets]

It looks like OP has 44 separate tables of parts - a table for every part type, and is trying now to create a single registry of parts.

Some 15 years ago I created an IT Registry app. Maybe approach I used there will be of use:

There was a single table where all material and immaterial IT objects of various types (like desktop computers, laptops, displays, program licences, contracts services, etc.) were registered. Most of fields in table were used for all types of objects in same way (fields like Name, Registering/Purchase Date, Type, Producer, Lease Agreement ID, etc.), but there were some fields like Text1, Text2, Numeric1, Numeric2, Numeric3. These fields were reserved for type-specific parameters of registered objects (like screen type and diagonal for displays, or processor type and power for laptops, desktops, or servers, etc.). There was a separate table, where for every object type all additional fields used were registered - having the label text for corresponding registry field entered. The form where objects were added/edited/displayed was filtered by object type, and those type-specific fields were only then visible, when the label text was registered for this field for this object type, with proper label text displayed. And any reports got entries from those fields combined with proper label text too.