Record Numbering Advice

[MinnKota]

I have imported an Excel spreadsheet into Access (1000 records). I have decided to keep the Excel numbering for our customer service folks to reference. But I need to decide how to number the new records.

Autonumber would work fine for my purposes, as I don't need sequential numbers. The numbers are only used to reference records. I currently have a text field that has the numbering, but I can't figure out how to transition from the text field to the autonumbering.

Ought I use an append query?

 

[JeremyNYC]

You can use the append query to add records to the table, including inserting values to the autonumber field. The autonumber would then pick up from the highest number appended.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[MinnKota]

I'm not sure why, but when I append the two tables, it rearranges the records. For some reason, Access is arranging the records by the Date field...

 

[JeremyNYC]

Access tables do not store records in any particular order, so it's just a case of mistaken expectations. To see ordered records, build a query that sorts the records.

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[MinnKota]

If Access does not order records in any particular way, then how does one determine how an Append query will pair the fields???

 

[Golom]

What do you mean by "... pair the fields ..."?

An append query will append zero or more records and you can think of it as looping through the records provided in the source for the append and adding them, one at a time, to the destination table. It's just that, when you retrieve records from a table with a SELECT, there is no guarantee that they will be retrieved in any particular order if you don't use an ORDER BY clause.

 

[MinnKota]

Well, I figured it out. I didn't end up using an append query. I simply used the Order By clause (as Golom mentioned)to order by my numbered field. I then inserted a new Autonumber field. Works beautifully!

 

[MinnKota]

I lied, it is not figured out...for some reason I can't get an autonumber field appended to my table in the same order as the numbers from excel...

 

[Golom]

No you can't because an autonumber field places numbers on records in the order in which they were added to the table. That's not necessarily the order in which they appeared in Excel unless you force it to be by ordering the source query for the append operation. Even then, if there are discontinuities in the Excel numbering, that will not be carried over to the Access autonumber fields.

 

[lespaul]

Append will always add records to the table. if you are trying to change data in existing records you need to do an update query.

Why don't you tell us what you are trying to accomplish instead, maybe that will help us to help you better.

leslie

 

[MinnKota]

Well, I am fairly confident that I have now achieved my goal. I created a copy of the table that I wanted the autonumber added to. (Structure only) I added an autonumber field. I then created an Update query that added a Sorted query to the the table I just created. And everything was autonumber and the numbers matched!!