Append Values from Query, While Incrementing Foreign Key Value

[kathleencampbell]

Is there any way to run an append query that duplicates table values from some fields and increments others, for example:

ID, SynIsDefault, SynID, Tag Value
1, -1, 1, AAA
2, -1, 1, BBB
3, -1, 1, CCC

ID is the primary key field and needs to increment. SynID is a grouping of the Tags that needs to increment by one for all tags. I would like to duplicate all the records and append the data to the table as such:
4, -1, 2, AAA
5, -1, 2, BBB
6, -1, 2, CCC



Thanks in advance.

[June7]

Assume the ID is an autonumber field.

Try something like:
INSERT INTO tablename(SynIsDefault, SynID, [Tag Value]) FROM SELECT SynIsDefault, SynID + 1 As NewSynID, [Tag Value];

[kathleencampbell]

Thank you for your reply, unfortunately, the ID field does not autonumber.

[June7]

Why not autonumber? Otherwise the code to sequentially number the records starting from the last number in table is complicated.

Review this http://allenbrowne.com/ranking.html

Are there multiple users?