Results 1 to 5 of 5

Thread: Access 2007 - Copying Records Within The Same Table

  1. #1
    Join Date
    Dec 2009
    Posts
    79

    Access 2007 - Copying Records Within The Same Table

    I have 2,433 records in a table (with many many fields).

    I need to make an exact copy of each record, with one exception. I need a change made in each newly created record which will give it a unqiue name. These records present various funding scenarios for various companys. Essentially this table holds the results of "what if" games played by accountants.

    Each of the existing 2,433 records now have a "scenario" name of "Original Scenario" (the field is even called Scenario) - all of the remaining fields have number representing a variety of things.

    I need to make a copy of all 2,433 records but with a scenario name of "New Scenario". Everything would be indentical except that one field. The end result would be that I now have 4,866 records with 2,433 having "Original Scenario" in the Scenario field and 2, 433 records with "New Scenario" in the Scenario field. This is what I've tried, but all it does is duplicate everything, even the scenario name.
    Code:
    Public Sub m_AddNewScenarioRecords()
    'the purpose of this sub is to insert a fresh set of records
    'for a newly created scenario.   this scenario is created and
    'saved on the PCMH_Data form
    Dim db As DAO.Database
    Set db = Access.Application.CurrentDb
    Dim rsOriginal As DAO.Recordset, rsNew As DAO.Recordset
    Dim i As Long
    
        Set rsOriginal = CurrentDb.OpenRecordset("SELECT * FROM source_scenarios_results WHERE scenario = 'Original Scenario' ")
        Set rsNew = CurrentDb.OpenRecordset("SELECT * FROM source_scenarios_results WHERE scenario = 'Original Scenario' ")
        Do While Not rsOriginal.EOF
            With rsNew
                .AddNew
                For i = 0 To .Fields.count - 1
                  If .Fields(i).Value = "Scenario" Then 'insert new scenario name in scenario field
                      .Fields("Scenerio") = "New Scenario"
                  Else 'otherwise insert existing value into new field
                    .Fields(i).Value = rsOriginal.Fields(i).Value
                  End If
                Next
                .Update
            End With
        rsOriginal.MoveNext
        Loop
    End Sub
    What am I doing wrong?

  2. #2
    Join Date
    May 2006
    Posts
    407
    You misspelled "Scenerio" within your program. The correct spelling is "Scenario" To help you find it: ScenArio. Should be the A, not the E that is in your program.

    A much better way to do this would be to write an append query where every field name is the output value of it's own field, except for Scenario. You would give the "New Scenario" as the field value for that field. It will even run in about 1/10 of the time or less than your program.

  3. #3
    Join Date
    Dec 2009
    Posts
    79
    An "append query"? Ya lost me....

    I googled it and it looks like the same thing as an INSERT query. I know how to write an INSERT INTO blah SELECT blah FROM otherbla, and I would assume that it would work the same way even though I'm not dealing with different tables, but I don't get how to change the one field after the inseration of each record.

    Lets make is simple. Lets say I have three fields: field1, field2, field3

    I want to copy all records in the table to the same table but change the value of field3 from "white" to "black" during the copy. I will end up with exactly double the number of records I started with, with half having "white" in field3 and the other half having "black" in field3.

    Using the append query, how would I do that?

    I'm thinking maybe I should just copy the whole darn table to a temp table, and update the temp table to change that one field, then copy all the records from the temp table over to my real table, and drop the temp table. It sounds like a lot of steps, but I'm pretty sure it would work. I guess I'm just wondering if there is an easier way.

    (BTW: I am constantly misspelling Scenario for some reason....don't know why )

  4. #4
    Join Date
    May 2006
    Posts
    407
    here is the SQL for the query you described:
    Code:
    INSERT INTO Table1 ( field1, field2, field3 )
    SELECT Table1.field1, Table1.field2, "BLACK" AS Expr1
    FROM Table1
    WHERE (((Table1.field3)="white"));
    I tested it on three records in a dummy (Table1) table. It worked.
    By the way, Access calls this an Append query, even though the SQL says to INSERT INTO. While in Query Design view, Append is one of the kinds of query to ask for. The others would be "Select", "Make Table", "Update", "CrossTab", and "Delete".

  5. #5
    Join Date
    Dec 2009
    Posts
    79
    Once again my VBA Knight in shining armour comes through.

    Thank you sir.....as the Brits are fond of saying...."Works a treat!"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •