Append Query to the same table changing only the primary key

[Dave D]

I'm not sure this is possible, but I would like to select a group of records from Table1 and insert the same records back into Table1 using the same data with the exception of changing the primary key.

A sowId is the PK in Table1 and different SOWs sometimes use the same parts. So, instead of copying the records one-by-one (rs.AddNew /rs.Update), changing the sowId as I go, I would like to make the changes all at once with an Append Query.



Is what I'm trying to do doable? If so, any suggestions as how to go about doing it?

Thanks for any help you can provide.

[June7]

Should be able to. Review http://www.w3schools.com/sql/sql_insert_into_select.asp

What criteria would be used to select the records?

[Dave D]

I'm thinking something like this:

"INSERT INTO Table1 (sowId, partId, Qty)
SELECT * FROM Table1 WHERE sowID=123"

But, I can't figure out how to add the new sowId '456' in place of '123'.

[June7]

Is sowID an autonumber field? The table will automatically assign the next available number to the new record. Do not include sowID in the sql.

However, I tested with the * wildcard and it would not work.

INSERT INTO Table1(partID, Qty) SELECT partID, Qty FROM Table1 WHERE sowID=123

[Dave D]

The sowId is passed from another form, it's not an auto-increment. Thanks for the heads up on the wildcard element. It looks like I get to loop my way though a recordset.

[June7]

Correction, I did get the * wildcard to work if there was no unique ID field in the table. But since the fields have to be listed in the INTO clause, might as well list them in the VALUES part - which in this example is the SELECT subquery.