Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Oct 2006
    Location
    Albany, New York, USA
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Assign every third record (2003)

    Hi Folks,
    I have a table with 3 fields. In the third field, I want to assign the names Ed, Dave, Jim successively on every third record. In other words I want record 1 to be assigned to Ed, Record 2 to be assigned to Dave and Record 3 to be assigned to Jim. Record 4 Assigned to Ed, record 5 assigned to Dave and Record 6 assigned to Jim and on and on. The real database has 36000 records. I canít figure out how to do this. Anyone?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assign every third record (2003)

    Does your table have a unique key, for example an AutoNumber field? If so, what is its name?

  3. #3
    Star Lounger
    Join Date
    Oct 2006
    Location
    Albany, New York, USA
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assign every third record (2003)

    It does have a unique field called ID but it's not designated as a Key field. Let me explain, it's not my database I was just called in to try to get it working again.
    The user has exported the table to Excel and added the three names and then imported it back into Access and it changed some of the data types from Date/Time to Text and changed all the Text fields to 255 characters. Now the database doesn't work any more.
    I thought it would be better to add the names in Access, but can't find a way to do it.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assign every third record (2003)

    As long as ID is unique, it'll work. You can create a select query based on the table that calculates a sequence number based on the value of ID, and an update query based on the select query that will assign the names using the sequence number to count.

    See the attached very simple demo.
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Oct 2006
    Location
    Albany, New York, USA
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assign every third record (2003)

    Hi Hans,
    As always, works like a charm. Thank you muchly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •