INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Insert field into table and populate with numbers

Insert field into table and populate with numbers

(OP)
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?

RE: Insert field into table and populate with numbers

You need an update query like:

CODE

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

Duane
Hook'D on Access
MS Access MVP

RE: Insert field into table and populate with numbers

(OP)
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.

RE: Insert field into table and populate with numbers

(OP)
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!

RE: Insert field into table and populate with numbers

Quote (SMHSleepy):

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

RE: Insert field into table and populate with numbers

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close