Hi

I am revising an existing database, and have re-designed from the ground up. My problem is importing the data from the old database - specifically, that I can't import/overwrite the new Primary Key...

The old database had a customer table with CustomerID as the primary key (using Autonumber). It also has a policy table (1 customer can have many policies) which had PolicyID as the primary key (autonumber), but then also CustomerID to link the policy record to the correct customer.

In the new database I have very similar tables for customer & policy - my question is, when I link to the old table & do an append query, I will only be able to import the old CustomerID into a created field such as OldID - how will I then ensure that when I import the old policy data, it is linked to the 'new' CustomerID?

Probably as clear as mud.... ;-)

TIA

Dale
Manchester, UK

In your append query, select the old autonumber and append it to the new autonumber field. An append query is the only place you can actually supply a value for an autonumber. Be sure to load the 1-side tables first so you won't run afoul of referential integrity.

How exactly do you perform an append query, when you want to import customers from an old database to the customers in a new database and keep the old customerID?
Thx/bevs

1. Go to the query tab of the database window and select the new query option.
2. Choose design view.
3. Choose the table that contains the source data (import it or link to it if it isn't already in this db)
4. Select all the columns you need.
5. Change the query type to Append.
6. Choose the name of the table you want to append to.
7. If the column names of the two tables are identical, Access will automatically populate the Append to row with the correct column name. If the column names are different, you'll need to choose the correct column name from the combo. Make sure that the AppendTo value is supplied for ALL columns.
8. Make sure that the old primary key is matched to the new autonumber field.
9. Save the query. Then run it.

that's the ticket... :)
thx Pat