INSERTing into the same table

[Gangrel]

I have a table in Access that i want to basically copy some rows, and add them into the table again while changing the information in one column. So, for example, I have rows of data like
A 1
B 1
C 1

and want to automatically copy them and change the 1's to 2's, so I would have this:

A 1
B 1
C 1
A 2
B 2
C 2


It was suggested to me to do an INSERT INTO, but Access doesn't like what I have come up with:

INSERT INTO [Pgm Rel Workloadbu]
VALUES [SELECT [Program ID],'9',[Program Status],[Programmer],[Ready Date],[Specing Hours],[Prog Hours],[Testing Hours],[Addnl Work Hours],[Online Help Date],[Rec Online Help Ind],[Mainframe Ind]]
FROM [PGM Rel Workloadbu]
WHERE [Release] = '8'

Does anyone have any suggestions or corrections on how to do this?

 

[PHV]

Have you tried to replace this:
INSERT INTO [Pgm Rel Workloadbu]
VALUES [SELECT
By this:
INSERT INTO [Pgm Rel Workloadbu]
SELECT

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

I think you need () not [] around your select statement:

INSERT INTO  [Pgm Rel Workloadbu] 
[COLOR=red]([/color]SELECT [Program ID],'9',[Program Status],[Programmer],[Ready Date],[Specing Hours],[Prog Hours],[Testing Hours],[Addnl Work Hours],[Online Help Date],[Rec Online Help Ind],[Mainframe Ind][COLOR=red])[/color]
FROM [PGM Rel Workloadbu]
WHERE [Release] = '8'

Leslie

 

[Gangrel]

Now I get the message "No Destination Field Name ('9')"

I've tried putting the field name = '9' but that doesn't work either.

the "9" is in the right position, as Release is the second column in the database, and 9 is what I'm trying to change the new rows to.

Any one have any ides? Thanks!

 

[lespaul]

are the fields listed in your select statements ALL the fields in the table or are there more fields in the table that you are not selecting because they don't have information that you need to transfer?

leslie

 

[Gangrel]

No, I've listed every field in the table.

 

[lespaul]

and the release field is a text field?

Leslie

 

[Gangrel]

Yes it is.

 

[lespaul]

try this:

INSERT INTO [Pgm Rel Workloadbu]
(SELECT [Program ID],'9',[Program Status],[Programmer],[Ready Date],[Specing Hours],[Prog Hours],[Testing Hours],[Addnl Work Hours],[Online Help Date],[Rec Online Help Ind],[Mainframe Ind]
FROM [PGM Rel Workloadbu]
WHERE [Release] = '8')

Leslie

 

[Gangrel]

Ok, I finally got it to work by specifying the field names in the INSERT clause, even though my book says all fields get selected if you don't specify it.

INSERT INTO [Pgm Rel Workloadbu] ( [Program ID], Release, [Program Status], Programmer, [Ready Date], [Specing Hours], [Prog Hours], [Testing Hours], [Addnl Work Hours], [Online Help Date], [Rec Online Help Ind], [Mainframe Ind] )
SELECT DISTINCT [Program ID], '2', [Program Status], [Programmer], [Ready Date], [Specing Hours], [Prog Hours], [Testing Hours], [Addnl Work Hours], [Online Help Date], [Rec Online Help Ind], [Mainframe Ind]
FROM [PGM Rel Workloadbu]
WHERE [Release] = '1';

Thanks for all your help guys!