none
Duplicate Valued Costs RRS feed

  • Question

  • Hello everyone,

    I am receiving a text file each day that contains cost information. I'm importing it into a MS Access 2010 database on a Win7 PC into a table called tbl_ValuedCost. The field named RecordID is supplied in the text file. The field named ValueCostID is an Auto Number in my database. The ValuedCost is duplicated where ever the RecordID is duplicated (WHAT I HAVE). I want to keep the ValuedCost on only one of each RecordID and set the rest of the ValuedCost to $0.00 for each duplicate RecordID (WHAT I NEED).

    Any help is appreciated. Thanks, Kevin

    Tuesday, July 18, 2017 8:30 PM

Answers

  • Try this:

    UPDATE tblValuedCost AS VC1
    SET ValuedCost = 0
    WHERE ValuedCostID <>
         (SELECT MIN(ValuedCostID)
          FROM tblValuedCost AS VC2
          WHERE VC2.RecordID = VC1.RecordID);

    I'm not sure if the use of an aggregation operator in the subquery will make the query non-updatable.  If it does, then try calling the VBA DMin function rather than using a subquery:

    UPDATE tblValuedCost
    SET ValuedCost = 0
    WHERE ValuedCostID <>
         DMIN("ValuedCostID","tblValuedCost",
         "RecordID = " & [RecordID]);

    Ken Sheridan, Stafford, England

    • Marked as answer by KevinATF Wednesday, July 19, 2017 12:33 PM
    Wednesday, July 19, 2017 11:27 AM

All replies

  • Hi Kevin,

    Here's one possible approach you could try:

    1. Create a temporary table, such as:

    SELECT RecordID, Min(ValuedCostID) As ID, ValuedCost
    FROM tbl_ValuedCost
    GROUP BY RecordID, ValuedCost
    INTO tblTemp

    2. Reset all your cost to $0 wth:

    UPDATE tbl_ValuedCost SET ValuedCost=0

    3. Reassign the original values using the temp table:

    UPDATE tbl_ValuedCost T1
    INNER JOIN tblTemp T2
    ON T1.RecordID=T2.RecordID
    AND T1.ValuedCostID=T2.ID
    SET T1.ValuedCost=T2.ValuedCost

    Make sure you have a backup copy of your table before trying the above.

    Hope it helps...

    Tuesday, July 18, 2017 8:44 PM
  • Hello Kevin,

    You could also test the following macro to edit the record.

    Sub Demo()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM tbl_ValuedCost")
    Dim tmID As Integer
    rst.MoveFirst
    Do Until rst.EOF = True
    If tmID = rst!RecordID Then
    rst.Edit
    rst!ValuedCost = "0"
    rst.Update
    End If
    tmID = rst!RecordID
    rst.MoveNext
    Loop
    End Sub

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 19, 2017 5:44 AM
    Moderator
  • Try this:

    UPDATE tblValuedCost AS VC1
    SET ValuedCost = 0
    WHERE ValuedCostID <>
         (SELECT MIN(ValuedCostID)
          FROM tblValuedCost AS VC2
          WHERE VC2.RecordID = VC1.RecordID);

    I'm not sure if the use of an aggregation operator in the subquery will make the query non-updatable.  If it does, then try calling the VBA DMin function rather than using a subquery:

    UPDATE tblValuedCost
    SET ValuedCost = 0
    WHERE ValuedCostID <>
         DMIN("ValuedCostID","tblValuedCost",
         "RecordID = " & [RecordID]);

    Ken Sheridan, Stafford, England

    • Marked as answer by KevinATF Wednesday, July 19, 2017 12:33 PM
    Wednesday, July 19, 2017 11:27 AM
  • All three are excellent suggestions. Thanks team for all your help!
    Wednesday, July 19, 2017 12:34 PM
  • Sub Demo()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM tbl_ValuedCost")
    Dim tmID As Integer
    rst.MoveFirst
    Do Until rst.EOF = True
    If tmID = rst!RecordID Then
    rst.Edit
    rst!ValuedCost = "0"
    rst.Update
    End If
    tmID = rst!RecordID
    rst.MoveNext
    Loop
    End Sub

    Hi Celeste,

    What is the advantage of the line:  rst.MoveFirst ?

    If the recordset has records, it is already positiond on the first record, else it will generate an error  "No current record".

    And what is the advantage of the line: Set dbs = CurrentDb  , if the variable dbs is pure local to the routine?

    Imb.

    Wednesday, July 19, 2017 1:01 PM