combine multiple tables with some identical columns

[Kellens]

Hello,

I am new to this forum so not sure if i should post here or with "Querries".
The problem i'm having is that i need to combine 12 tables with multiple columns and not all columns have the same headers.
So if a column is present it should be updated, if not it should be added.

The end result should be one column with all records and all columns that exists in all 12 tables.

The tables have an identifier that is the same for all columns (supplier reference).
Table 1 has 56 columns
Table 2 has 42 columns (of which some have the same header)
....

(--doing every column manually is something i would like to avoid if possible --)

I hope my explanation is clear, if not i'll be happy to explain further.



Thanks in advance for your time...

[recyan]

Doesn't UNION Query serve your purpose ?

Thanks

[Kellens]

yes was busy with a UNION but then all tables have to be the same
i then started to think about an enmpty table with all columns present and do an append it appears i have more than 255 columns (275 to be exact) and access can't handle that.
Dunno yet how i can /will solve this...

[NoellaG]

Hi,

seems to me you should have a look at your database design and relations between the tables. I take it the columns with the same header in different tables are your primary and foreign key fields?

[Kellens]

the tables are comming from Excel. I used a VBA script to split-up cells in different columns. Now the idea is to combine the data in duplicate columns.
Each table has an unique identifier (supplier number) and all the other columns MAY be doubles

[recyan]

Appears, there are two things involved :
1) Data Massaging of your Excel sheets.
2) The Access DB Design.

1) Can you post a couple of data for a couple of tables & the result you expect out of that data ?
2) Why do you have separate Excel sheets for Suppliers Records?
3) What does a single record for a supplier in your Excel sheet contain ?

Thanks