Append Record with Next Consecutive Number (not Autonumber)

S

shm135

Hi,

I have a table with two columns: ID, Version.

I would like to make a query that will append a record with the same
ID, but the next consecutive version number.

Example:
ID-Version
1-1
2-1
2-2

If I want to create a new version for ID 1, then the query will create
a new record: 1-2
If I want to create a new version for ID 2, then the query will create
a new record: 2-3

Any help would be appreciated. Thanks!
 
M

Marshall Barton

shm135 said:
I have a table with two columns: ID, Version.

I would like to make a query that will append a record with the same
ID, but the next consecutive version number.

Example:
ID-Version
1-1
2-1
2-2

If I want to create a new version for ID 1, then the query will create
a new record: 1-2
If I want to create a new version for ID 2, then the query will create
a new record: 2-3
Click to expand...

INSERT INTO table VALUES([new record ID], Nz(DMax("Version",
"table", "ID = " & [new record ID])) + 1)
 
Top