How to Insert duplicate data?

[Igawa29]

So I am not sure the best way to approach this problem. I want to update a table the following way:

I want to create a duplicate record but put a tag on the old record, for example:


Name: John
Role: Manager
Rate: 22.00

Name: John
Role: Historical______Manager
Rate: 22.00


I was thinking this for the code but I was running into issues:

INSERT INTO Portfolio SELECT * FROM Portfolio
Where [Role] = 'Manager' and [Rate] = 22;

However I am getting errors when trying to run. Plus I am not sure how to append on the Historical______ prefix.

 

[SkipVought]

Hi,

Of what purpose is [highlight #FCE94F]Historical______[/highlight] particularly the UNDERSCORE string of characters?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Igawa29]

Sometime the Role name changes or the Role rate changes, so I wanted to put Historical in the record to distinguish the two. That way we will have a track record of old records.

 

[SkipVought]

You ought to have a status change date on each row and maybe a change status

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Igawa29]

That wouldn't be a bad idea, a time stamp on each row. Yeah I will give that a try. But my other question still stands on how I can take the rows that equal "Manager" and duplicate them and insert them back into the table with the time stamp.

 

[SkipVought]

What is the error message and the exact SQL you used.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Igawa29]

Code:
INSERT INTO Portfolio SELECT * FROM Portfolio
Where [Role] = 'Manager' and [Rate] = 22;



Error:

Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 78 record(s) to the table due to key violations. 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.

 

[SkipVought]

How was your table constructed?
Does it have Keys: primary, foreign?
Referential integrity constraints?
Did any rows get added?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

due to key violations" looks like you are trying to copy Primary Key values again to the new records. You may want to list all fields (except the PK) in your Insert and Select part:

[pre]
INSERT ([Name], [Role], {Rate]) INTO Portfolio
VALUES (SELECT [Name], [Role], {Rate] FROM Portfolio
Where [Role] = 'Manager' and [Rate] = 22
[/pre]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Igawa29]

Ahhh you are right I didn't think of the primary key. Ha, I guess I take that auto number for granted. I will give that a try.

I will update the thread with my results

 

[Igawa29]

I am getting a "Syntax error in INSERT INTO statement

 

[Igawa29]

I got it!

INSERT INTO Portfolio ([Mem_Name], [Role], [Rate])

SELECT [Mem_Name], [Role], [Rate] FROM Portfolio

Where [Role] = 'Manager' and [Rate] = 22;


Thanks for the help Skip and Andy!

 

[Andrzejek]

You are welcome, but that still does not solve your "Historical______" issue.
I would use Skip's suggestion (the way I understand it) and add a field into your table, like "Historical" with Yes/No, Y/N, True/False, -1/0, etc. and mark those "Historical" records that way.

Have fun.

---- Andy

There is a great need for a sarcasm font.