Results 1 to 8 of 8
  1. #1
    skyatis is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Posts
    11

    Manually ordering rows in a table - using unique line numbers.

    I am creating a database where the user will enter a series of instructions using text and comboboxes. The user needs to be able to re-order the records manually, eg insert a record in a between two rows, or move a record.



    I started by giving each row a unique line number, but of course as soon as you insert a row in the middle of the table that idea falls apart.

    I tried using line numbers with lots of decimals; that way I can insert line 6.5 between 6 and 7, then insert 6.75 between 6.5 and 7 etc but that's an ugly solution.

    Does anybody know a more elegant way to insert a record between two others and update a unique key to keep them sorted correctly?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Why do you need this? example please.
    There is no inherent order in a table.

    You can add a "sort key" filed to a table, and order/reorder the data via a query against the table.

  3. #3
    skyatis is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Posts
    11
    Thanks for the quick reply Orange!

    The purpose of the database is to allow a technical trainer to create lesson plans. The trainer could use of course use a word processor, but the idea of using Access is that there are a limited number of actions, usually with a table of options attached.

    To give a simple example, let's say we are creating a series of exercises for training a football player (soccer if you're from the USA!). The trainer might want the player to practise dribbling, slalom, passing and then shooting.

    The trainer has a series of instruction types:
    Pass
    Dribble
    Shoot
    Spoken Instruction
    Note to Trainer
    etc

    Most of these instruction types have a list of options which appear in the next field eg for 'Pass':
    'High Pass'
    'Low Pass'
    'Short lob' etc.
    These options are stored in another table with detailed descriptions of how to train them.

    The lesson plan designer has entered the following instructions in sequence:
    Dribble / Slow
    Pass / High Pass
    Slalom / Fast
    Pass / Short
    Shoot / Penalty

    Now he wants to amend the lesson plan to:
    Dribble / Slow
    Pass / High Pass
    Slalom / Fast
    Dribble / Fast <---- inserted line
    Pass / Short
    Shoot / Penalty

    How can we sort the table so that the instructions remain in the desired sequence, even after instructions are inserted, moved or deleted?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't have the code, but assuming you have a table of records with a uniqueID, you can have a separate table like a many to many join table to store the sort value e.g.

    SortOrder...UniqueID
    1...............11
    2...............32
    3...............5


    then if you want to insert a unique ID between 1 and 2, rather than giving it a value of 2.5 use two sql statements

    currentdb.execute("UPDATE tblSortOrder Set Sortorder=SortOrder+1 WHERE Sortorder>=2")
    currendb,execute("INSERT INTO tblSortOrder (SortOrder, UniqueID) VALUES(2," & me.uniqueID & ")")


    to delete
    currendb,execute("DELETE * FROM tblSortOrder WHERE Sortorder=2")
    currentdb.execute("UPDATE tblSortOrder Set Sortorder=SortOrder-1 WHERE Sortorder>=2")

    to move - a combination of the above

  5. #5
    skyatis is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Posts
    11
    Thanks Ajax I can see how that will work.

    Sorry if this is a dumb question (I'm new to SQL/Access as Excel and vba have outlived their usefulness) but why does this need a separate table? Can we not just store the SortOrder field in the table of instructions?

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As Ajax has shown, you can order your data based on a separate sort field. This is the same concept as I suggested in the earlier post.

    Consider a general approach:

    You have a number of questions and answers, say 100. You want to create 10 separate tests/exams consisting of say 12 question/answers each.

    You can pick 10 (random or hand picked) sets of 12 questions and answers from your questions and answers table.

    So you have test1.....test 10 each with 12 questions/answers. If you want to order the questions in each test you can add a sort field and adjust the order based on the sort field.

    There is no need -and it is really against relational design -- to have records in a specific table in some known physical order. That is really the function of a query.

    Good luck.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can if the table only contains one 'set' of instructions.

    but if the trainer wants different instruction types and/or sort orders, perhaps for different ages/experience of players, or depending on the time of year, whatever, the sort order needs to be in a different table - you would have a third column for 'training profile' or suchlike.

  8. #8
    skyatis is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Posts
    11
    Thanks people that makes perfect sense!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 05-06-2015, 11:19 PM
  2. Replies: 1
    Last Post: 01-10-2015, 11:46 PM
  3. Dmax Help with unique numbers
    By baronqueefington in forum Access
    Replies: 5
    Last Post: 12-30-2014, 12:50 PM
  4. Create New Table with unique customer rows
    By jstopper in forum Access
    Replies: 2
    Last Post: 03-06-2014, 03:13 PM
  5. Replies: 5
    Last Post: 06-30-2009, 09:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums