Prev: Counting Ocurrances within a Time Frame
Next: Convert Currency to String but keep commas
From: shm135 on 2 Apr 2010 13:14
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!
From: Marshall Barton on 2 Apr 2010 14:07
shm135 wrote:
>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
>

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

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Counting Ocurrances within a Time Frame
Next: Convert Currency to String but keep commas