Table conundrum

[aron.ridgway]

Firstly i have 4 supplier tables with identical field names but different databases in sql.

I want to have them all in one table and only want the information for reference i do not want to edit any of the data.

I need to create a new primary key number for the complete table. The data will need to be refreshed as the data comes from MMS Sage looking at company PLsuppliers.

I have tried a linked SQL union view- but this has no primary key.

I need to keep the Primary key consistant as the data saved in other tables will use this field.

The overall goal is creating a Purcahse order system and this list will be my complete supplier list.

I have attached a copy of my supplier table for reference.

Ive been going round in circles and cannot figure out the best way to accomplish this!:banghead:

Thanks in advance

 

[gemma-the-husky]

the truth is you do not want or need four supplier tables. you need one table, with a field to distinguish the type of supplier, if that is critical.

Even if you get the union query working, it will be non-updateable - which will be awkward to say the least.

 

[Cronk]

Create a new table with your autonumber PK field, as well as all the other fields you want to import. Make the company name a unique field. (Presumably, you will not have two different companies with the same name.) Create and run 4 append queries on the separate tables from which you want to import.

Or is there something I am not understanding?