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?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Insert field into table and populate with numbers 1
- Thread starter SMHSleepy
- Start date
-
1
- #2
You need an update query like:
Duane
Hook'D on Access
MS Access MVP
Code:
UPDATE tblMAIN
SET [SortOrder] = DCount("*","tblMain","MainID <=" & [MainID]);Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #3
- Thread starter
- #4
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!
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.SMHSleepy said:
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #6
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!
- Status
Similar threads
- Locked
- Question
- Locked
- Question