Append from multiple tables

Question

I have an Access 2007 database, with 5 joined table.  I need to append the data to another database with identical tables, but different data, thus merging the two files into one.  I am not sure how to proceed.  I tried Append queries, but the data relations made no sense.

Table names are tblMain, tblSche, tblProjMgr, tblConj, and tblAdM.  I can furnish more info if  needed.

Thanks

Kelli

Answer 1

You may well have a problem here if you have used autonumbers as keys.  You can append rows from a referenced table (i.e. the table on the 'one-side' of a one-to-many relationship) to the corresponding referenced table in the other database provided that you omit the autonumber key column, in which case new values will automatically assigned to the autonumber key column in the rows inserted.  However, if you then append rows from a referencing ('many-side') table these will retain the values of the foreign key column which reference the autonumber key of the first table.  As the rows inserted into the referenced table in the other database will now have new values in these rows, the foreign key values in the referencing table will no longer map to the correct row of the referenced table.

There are a number of ways you can handle this.  Probably the simplest is to create new primary and foreign key columns in the tables in the first database, and fill these with values which will not match be duplicated in the second database.  You can do this by means of update queries to add a fixed number to the  values of the current primary and foreign key columns.  The fixed number should be such that the lowest resulting number will be greater than the highest value in the autoumber key column in the second database.  If this is 12345 say then you'd populate the new columns in both the referencing and referenced tables in the first database with:

UPDATE MyTable

SET NewColumn = OldColumn+12345;

You can then append rows from a table in the first database to the corresponding table in the second database, but you must append the NewColumn  to the OldColumn.  Its sometimes not realised that you can assign a literal value to an autonumber column in this way when inserting a new row, but it is in fact perfectly feasible.  When inserting a value into the corresponding foreign key column in a referencing table there is no problem of course.

You need to bear in mind a couple of things:

1.  You cannot make a column the primary key of a table until it contains values, so you must fill the NewColumn column in a referenced table with an  update query as above before you can make the column the primary key.  In fact there is no real necessity to make the column the primary key at all as it's only being used to provide values which can be appended to the other table, so whether it's defined as a key or not is immaterial.

2.  When appending the data you must append rows from and to a referenced table before you can append rows from and to a referencing table.  Otherwise referential integrity would be violated.

Another way of doing this is to allow new values to be assigned to a referenced table's primary key when appending the new rows.  The assign the relevant value to the corresponding foreign key column in a referencing table by joining multiple tables, using another column, or more usually combination of columns as a a 'candidate key'.  The following is an example, which inserts rows into a Cities table and assigns values to its foreign key RegionID column by joining the MasterTable (in this case a table derived from an Excel worksheet from which the data is being imported) to the Countries and Regions tables on the Region, Country, RegionID and CountryID columns:

INSERT INTO Cities (City, RegionID)

SELECT DISTINCT MasterTable.City, Regions.RegionID

FROM Countries INNER JOIN (MasterTable INNER JOIN Regions

ON MasterTable.Region=Regions.Region)

ON (MasterTable.Country=Countries.Country)

AND (Countries.CountryID=Regions.CountryID);

This is not too difficult once you get your teeth around the logic, but needs a little more thought than the cruder, but nevertheless effective, approach outlined above.

---

Ken Sheridan, Stafford, England

Answer 2

Append queries is the only viable way to do this. Link the tables from db2 to db1, then in db1 write the queries. Look at the relatioships between the tables to determine which tables to do first, which next. Parent tables first, Child tables next.

---

-Tom. Microsoft Access MVP

Answer 3

Thank you gentlemen, I will give the various suggestions a try.