Solved Append Table and Auto Number Help

[wmix]

Hi Everyone,
I need help moving data into a table and I'm going crazy because no matter what I try it doesn't give me my desire results.

I have 322 records in an Excel file and the records are currently sorted in Ascending order by a value called ComplexID. This ID matches a bunch of other information that has been already imported into another table.

I want these 322 records to be added to an existing Access table, tblProperty, with an auto-number called PropID. Currently we have 535 records in this table.

Here's the tricky thing, I need the ComplexID and the PropID to match because I would really like to eliminate the ComplexID from the table (no point in having a redundant column).

So, the data in Excel is sorted as I want it, by ComplexID, starting at number 536.

So, before I did anything I backed up my database and made multiple copies of tblProperty (copy_tblProperty1, copy_tblProperty2, etc...). I did this so I could test everything and make sure all was correct.

The first thing I tried was to import from Excel into copy_tblProperty1. Import worked fine but nothing imported in the order I wanted. My ComplexID and the auto-number PropID are so random, it's not even ABC order by the second column.

The second thing I tried was to import from Excel making a brand new table, tblcomplex. Then I opened tblcomplex in a query and made sure everything was sorted Ascending based on ComplexID. It all looked good. UNTIL I tried to append the records to tblProperty. Again, they went in but the PropID that is auto-generated did not match the ComplexID. So even though I sorted the query prior and had everything the way I wanted it still didn't import into tblProperty correctly.

Does anyone know of a way I can force an append to a current table so the auto-number matches a number in a different column? OR so that the sort that I have in the query stays exactly the same when the append is run?

It's one of those things that's now frustrated me to the point that it's like a mission to figure out.

Thanks in advance for your help.

 

[theDBguy]

Hi. You could try the following steps:

1. Add an extra column in your Excel file and duplicate the ComplexID values in that column
2. Import the data into a new table
3. Append the data from the new table into the Property table but append the ComplexID column into the PropID column

Compare the results between the two columns (PropID and the duplicated ComplexID).

Hope that helps...

 

[wmix]

Hi. You could try the following steps:

1. Add an extra column in your Excel file and duplicate the ComplexID values in that column
2. Import the data into a new table
3. Append the data from the new table into the Property table but append the ComplexID column into the PropID column

Compare the results between the two columns (PropID and the duplicated ComplexID).

Hope that helps...

Thank you DBguy, it finally worked. No idea why it was being so darn stubborn but I duplicated the column as you suggested and it finally all went in correctly. I appreciate your insight.

 

[theDBguy]

Thank you DBguy, it finally worked. No idea why it was being so darn stubborn but I duplicated the column as you suggested and it finally all went in correctly. I appreciate your insight.

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.