Results 1 to 5 of 5
  1. #1
    Kluaoha is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    20

    Append Query for a Table with a Single Field

    I have a table that consists of just one field, which is its primary key and an autonumber. How would I add a new record to this table, simply incrementing the only field (SQL)?

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum...

    I don't think it can be done. I created a table with one field, set it to Autonumber type and set it as the PK field.
    I manually tried to add a record, but there was no way to add a record. You cannot increment the autonumber field without having a field you can manually enter data into.

    However......

    I modified the table by
    - removing the PK
    - added a unique index on the single field.

    The table is named "Table1" and the field I named "NumberField".
    The query (SQL) to increment the field is
    Code:
    INSERT INTO Table1 ( NumberField ) Values( Nz(DMax("NumberField","Table1"),0)+1);
    Change "Table1" and "NumberField" to your table and field names.


    I am curious as to why you would want a table like this...


    You might want to see this http://baldyweb.com/CustomAutonumber.htm
    Last edited by ssanfu; 07-31-2017 at 05:01 PM. Reason: added link

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have a table that consists of just one field, which is its primary key and an autonumber.
    I dare say you are trying to use this sole autonumber field as meaningful data (such as an incremental value) somewhere. If I'm right about that, it is ill advised and I think you need some info on what autonumbers are and are not.
    http://www.utteraccess.com/wiki/Autonumbers
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Kluaoha is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    20
    Thanks ssanfu, this did the trick, and it also seems to work without removing the PK!
    Incase you are wondering why I have this table, it is the intermediate for a many-to-many relationship between tables A and B where the possible groupings of records in B to which records in A can form a relationship are enumerated, and restricted, by this table.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Glad to help. Good luck with your project!

    Ready to mark this solved???

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

Similar Threads

  1. update / append query to edit single record
    By zernon916 in forum Queries
    Replies: 4
    Last Post: 03-21-2017, 04:16 AM
  2. Update and append with single query
    By FinChase in forum Queries
    Replies: 1
    Last Post: 07-01-2014, 11:04 AM
  3. Replies: 2
    Last Post: 02-28-2013, 07:00 PM
  4. Append Query For Single Record
    By burrina in forum Forms
    Replies: 8
    Last Post: 01-04-2013, 05:12 PM
  5. Append Query For Single Record
    By burrina in forum Queries
    Replies: 3
    Last Post: 12-30-2012, 11:23 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