Results 1 to 3 of 3

Thread: Append

  1. #1
    Join Date
    May 2004
    Posts
    9

    Unanswered: Append

    Hi

    I have a table titled "Custtran" containing 100 records and the fields say "Cust ID, Dept, Amount" and the values in the Dept field are "001". I would like to duplicate the records after changing the Dept to "002" and the Value to -Value (i.e. value * -1) and append into Custtran. The resultant Custtran will have 200 records with the value as Zero. Could you please suggest an easier method.

    Your help would be very much appreciated.

    Thanks

    Suresh

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    interesting question...

    I would do this by creating a disconnected dataset (I prefer ADO), looping through the dataset and executing an insert statement for each row in the current table.

    If you want an afro-engineered solution...
    You could duplicate the table, then run an update statement setting YourValue = YourValue * -1, then run another update statement setting Dept = 002, then run an insert statement copying the "duplicate" table back to the source.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    interesting question...

    I would do this by creating a disconnected dataset (I prefer ADO), looping through the dataset and executing an insert statement for each row in the current table.

    If you want an afro-engineered solution...
    You could duplicate the table, then run an update statement setting YourValue = YourValue * -1, then run another update statement setting Dept = 002, then run an insert statement copying the "duplicate" table back to the source.
    ... And as a special added bonus: An example of what Ted's talking about...

    Code:
        Dim TrgRecSet As ADODB.Recordset
        
        Set TrgRecSet = New ADODB.Recordset
        
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
    
        TableLbl.Caption = "Miscellaneous Expenses"
        ' Process Miscellaneous Expenses
        SQLString = "SELECT [Miscellaneous Expenses].*, [Work Orders II].[Material Markup]"
        SQLString = SQLString & " FROM [Work Orders II] INNER JOIN [Miscellaneous Expenses] ON [Work Orders II].WorkOrderNumber = [Miscellaneous Expenses].WorkOrderNumber;"
        MyRecSet.Open SQLString, CurrentProject.Connection
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM [Expense History] WHERE (1=0);"
            TrgRecSet.Open SQLString, CurrentProject.Connection
            While MyRecSet.EOF = False
                RecordLbl.Caption = MyRecSet.Fields(0).Value
                DoEvents
                TrgRecSet.AddNew
                ' Miscellaneous Expense ID
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
                ' W.O. #
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value & ""
                ' SI #
                TrgRecSet.Fields(2).Value = MyRecSet.Fields(2).Value
                ' Department ID
                TrgRecSet.Fields(3).Value = MyRecSet.Fields(3).Value
                ' Expense Description
                TrgRecSet.Fields(4).Value = MyRecSet.Fields(4).Value & ""
                ' Notes
                TrgRecSet.Fields(5).Value = MyRecSet.Fields(5).Value & ""
                ' Amount
                TrgRecSet.Fields(6).Value = MyRecSet.Fields(6).Value
                ' ExpenseReport Flag
                TrgRecSet.Fields(7).Value = MyRecSet.Fields(7).Value
                ' Material Markup
                TrgRecSet.Fields(8).Value = MyRecSet.Fields(10).Value
                ' Last Modified
                TrgRecSet.Fields(9).Value = MyRecSet.Fields(8).Value
                ' User ID
                TrgRecSet.Fields(10).Value = MyRecSet.Fields(9).Value & ""
                TrgRecSet.Update
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        End If
        MyRecSet.Close
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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