Results 1 to 5 of 5
  1. #1
    achammar is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2019

    Can I insert a record in the middle of a table with auto number as primary key

    I want to create a simple table. It's going to be an inventory table. It has an auto number field that is the primary key and will be the part number. It includes the size of each item in a separate field. This could be a single number in feet or inches, or something like 4' x 8' in feet or inches. I want the sizes to be in order as I view the table. Say I have 20 records entered, then need to add a new part that is in between a couple sizes that have already been entered. Is there any way to insert that item between 2 records that have already been entered? I don't care if the primary key field is in order? This is just something simple, quick and fast so I'd like to avoid writing queries if possible because that's a weak area for me anyway.

    Thank you for any advice!!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Nevada, USA
    Queries are an important tool that you should probably learn, but you could sort the table on your size field instead of the autonumber field.
    Paul (wino moderator)
    MS Access MVP 2007-2019

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    order is relative, however you want to sort it.
    autonum is auto, you cant add your own.

  4. #4
    achammar is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2019
    Ok, thank you both! It would be hard to sort by size I think because of the way different sizes are entered. Some are a single number with double quotes behind them (for inches) and some are 2 numbers like 4 x 8 etc.. I do know how to use some queries. In fact i just learned how to use a simple append query and an update query. It's just slow and a weak point for me. I know I need to learn them better lol I ended up adding a sequence field that I increment by 10 for each record and I will set that field to whatever number I need it to be to order the parts by ordering by the field. Thank you both for your replies!

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Just a thought.
    Add a field for length and a field for width. Then use a standardized dimension such as inches. Then sort on those 2 fields. You can keep and display your simplified dimensions ie. 4X8 if needed.
    Heres a link on sorting by 2 fields . . .

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

Similar Threads

  1. Replies: 3
    Last Post: 10-07-2018, 04:25 PM
  2. Replies: 3
    Last Post: 11-30-2016, 05:09 PM
  3. Replies: 5
    Last Post: 03-21-2015, 01:42 PM
  4. auto-number not used on insert
    By DKP in forum Queries
    Replies: 5
    Last Post: 10-06-2014, 11:04 AM
  5. Primary Key Auto Number not available in Excel for import
    By justphilip2003 in forum Import/Export Data
    Replies: 1
    Last Post: 04-30-2013, 07:52 PM

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