Duplicate Valued Costs

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

Answer 1

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...

Answer 2

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 [e-mail].

Answer 3

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

Answer 4

All three are excellent suggestions. Thanks team for all your help!

Answer 5

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.