Results 1 to 6 of 6
  1. #1
    JamesR is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    5

    Question Access - renumber - auto sorting by Primary Key

    Hi,



    I am having some basic problems with access. Essentially I am numbering "shelving" from 1-600+ which are being imported into access from an architectural program.

    I solved one problem of renumbering them if i delete some halfway through, but im coming up to another problem now.

    My architectural program auto increments and adds the primary key in ascending order as the shelving units are placed in the drawing. The problem is the order they go in the drawing will not be the numerical order of them by their "mark".

    So, when i export the drawing to access the DB is auto sorting my shelving by the Primary Key, so when i delete some and renumber in the DB it is all over the place as it renumbers them (via a new "autonumber" field) by ascending primary key order.

    is there a way i can stop this?

  2. #2
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481

    IMHO, you shouldn't think of the PK as having a meaningful order,

    but as a DB tool to make storage and relationships operate efficiently.
    You don't specify in your post, but it sounds as if you are working directly on your tables instead of using queries with which you could specify the sort order.
    One sentence in your posts suggests that you are altering a PK. If so, don't do it.

  3. #3
    JamesR is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    5
    hi,

    Im not editing the PK as it would be impossible to do so as it has so many relationships within my drawing model. the problem at the moment is when i open this part of the database (shelving) it is sorting it via the PK value.

    essentially, each shelving unit has 2 values, the PK and a Mark number.

    when i add new shelving units/or they are placed within the drawing the PK number will not be in relation to the Mark number.

    so, when renumbering using autonumber field within access it is renumbering via the ascending PK.

    this is what im trying to work out, i will get some screenshots to explain it better as im not exactly an access boff, but i also want it so the guys in the office can use it with ease.

  4. #4
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481

    Why aren't you sorting according to 'Mark'?

    Are you looking at a table or a query? The table can be in any order, but when opened will usually be by PK and you will not have control of the sort order. With a query, you should be sorting on 'Mark'.
    You shouldn't be concerned with the sort order of the table.

  5. #5
    JamesR is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    5
    i have uploaded a video of what i am doing. sadly my access knowledge is very limited so im probably doing this a really awkward way.

    http://www.youtube.com/watch?v=CSOAoJu97dc

  6. #6
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481
    I'm sorry. I couldn't follow your video well enough to get any insight as to where you are getting off track.

    Anyone...Help???

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

Similar Threads

  1. Replies: 3
    Last Post: 11-04-2011, 01:50 PM
  2. Replies: 1
    Last Post: 10-19-2011, 07:51 PM
  3. Replies: 1
    Last Post: 07-10-2011, 07:09 PM
  4. Sorting on field other than primary key
    By usmcgrunt in forum Access
    Replies: 24
    Last Post: 08-30-2010, 07:15 PM
  5. Access report with 3 tables -grouping,sorting
    By kartechy in forum Reports
    Replies: 0
    Last Post: 07-22-2009, 09:07 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