Appending tables, Primary key problem

[civileng]

I would like to merge 5 huge tables together into one (same structure, each has date for one year).

I'm trying to do this with query - append fuction. But there is a error with appending after first tabel (Acces cant append all the record, conversion type failure). I assume there is a problem with ID field - primary key.

All the tables have IDs from 1 to number of records so they overlap. I tried to modify id field in each table with format and adding letter to id.
1st table format: &"a", 2nd format: &"b". But it does not work.

Also maybe the problem i'm trying to append data to another database into a table I created with copying one of mentioned tables and deleting all records. But when I append first table id field is just incremental numbers.

Any solutions?

 

[plog]

First, I'm not sold that it is your ID field, but let's pursue that for a while. What data type is the ID field? Is it in fact set to be a unique primary key? If so, make it so that it is not a unique primary key then try your append again.

Also, since you mentioned prefixing a letter to the id value I'm guessing that ID doesn't do anything. Are you linking to another table using the ID field? Does it serve a purpose other than 'I heard every table needs an ID field?'. If not, kill it altogether, get rid of that field.

Conversion errors can also exist on other fields--text going to numbers, non-date values going into dates, etc. The way to find exactly what field is breaking it is to test your append query field by field until you get that error. Set up your append query, but remove all but one field (I'd start with the ID field since that's your prime suspect). If it appends, bring down another field and try it again. Keep appending until you get that error, the last field is the culprit.

 

[Pat Hartman]

If it turns out to be the autonumber PK, you can solve the problem by adding a fixed value to each key.

Start by finding the maximum key value in tbl1. Let's say it is 2987. I would add 3000 to the autonumber for the second table. Use + because you want to actually add the value, you don't want to concatenate it. Then find the current max. Let's say it is 9042. Add 10000 to the third table, etc.

When you append the dependent child records, you won't need to change their PKs unless they have dependents. You can just let Access assign new key values. Keep track of what the main table increment is for each table and use it to calculate the new FK when appending child records.