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)?
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)?
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
Change "Table1" and "NumberField" to your table and field names.Code:INSERT INTO Table1 ( NumberField ) Values( Nz(DMax("NumberField","Table1"),0)+1);
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
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.I have a table that consists of just one field, which is its primary key and an autonumber.
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.
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.
Glad to help. Good luck with your project!
Ready to mark this solved???