Insert field into table and populate with numbers 1

[SMHSleepy]

Arrgh, this should be easy but I can't figure it out. I have a tblMAIN with an autonumber MainID as the primary index. I've been using this field to sort my forms as it gives me the information in the order it was entered. However, I've decided to add a SortOrder field into my table so that I can make changes to the viewing order. It was easy enough to add the SortOrder field but how do I now populate it with numbers? With the table sorted ascending by MainID, I want to populate SortOrder starting at 1 and increasing by 1 until the last record. I tried making a temp table with the chronologic numbers and using an update query to insert them into tblMAIN, but it just appended the numbers instead of placing them into the existing records. Any suggestions?

 

[dhookom]

You need an update query like:

UPDATE tblMAIN
SET [SortOrder] = DCount("*","tblMain","MainID <=" & [MainID]);

Duane
Hook'D on Access
MS Access MVP

 

[SMHSleepy]

Hey Duane, I still can't figure it out. Firstly, I don't know where to put that code. Secondly, don't I need some sort of a loop to do this? I don't get the "MainID <=" & [MainID] part.

 

[SMHSleepy]

Okay, I did it the hard way. I exported my tblMAIN as an Excel file, deleted all fields except MainID and SortOrder, then populated SortOrder with chronological numbers. Then I imported that Excel sheet as an Access table and used an update query to add the SortOrder numbers into tblMAIN. The reason it appended, instead of inserting before was because I didn't have the proper relationships in place. That's why I needed to make a separate table with the same MainID field in order to match it up properly. Oh well, just glad it's done!

 

[dhookom]

didn't have the proper relationships in place

I thought your question involved only one table that you needed to update a single field with a sequence of numbers. That's what my update query was designed to do.

Duane
Hook'D on Access
MS Access MVP

 

[SMHSleepy]

Yes, you are absolutely correct. I only needed to update one table with numbers but creating another table was the only way I could figure out how to do it. Your query would have probably done exactly what I wanted in one simple step. Oh well, thanks though!