Eliminating Duplicates in MS Access while updating them

[Marlene]

Hi All

I have the following scenario, where I have found all the duplicates
in a table, based on an order number and a part number (item).I might
have something like this:
Order PODate Rec Qty Invoice# Item
Supplier Status POReceivedDate
570133 03/09/2004 50 0 DMEDIUM L0010 PENDING 03/09/2004
570133 03/09/2004 50 0 DMEDIUM L0010 PENDING 03/09/2004
570133 03/09/2004 50 0 DMEDIUM L0010 PENDING 03/09/2004

OR:

Order PODate RecQTY Inv Item Supplier Status POReceivedDate
571056 20/09/2004 1 0 D26032284 D0001 PENDING 20/09/2004
571056 20/09/2004 10 0 D8970654390 D0001 PENDING 20/09/2004
571056 21/09/2004 10 0 D8970654390 D0001 PENDING 21/09/2004
571056 20/09/2004 1 0 D26032284 D0001 PENDING 20/09/2004

Now I have to read through them line by line and then check if the
part is the same and then add the quantities and use the latest date.
So for example one I would have the following in the end:

Order PODate Rec Qty Invoice# Item
Supplier Status POReceivedDate
570133 03/09/2004 150 0 DMEDIUM L0010 PENDING 03/09/2004

And for example 2:
Order PODate RecQTY Inv Item Supplier Status POReceivedDate
571056 20/09/2004 2 0 D26032284 D0001 PENDING 20/09/2004
571056 21/09/2004 20 0 D8970654390 D0001 PENDING 21/09/2004

Could any one out there be able to help me??

Thank you very much!
Marlene

[PaulT]

Are there any primary keys? I'm thinking the better way is to
pre-check for an existing record, and then update the quantity if one
is found; otherwise, add a new entry.

pseudo-codingly something like this: (the query given here could be
considerably smaller depending on any primary key(s))
<start>
1. query for existing order...
select (order# [and/or part# ???])
from yourTable
where POdate = givenDate ['given' means from a form or other
input]
and invoice = (givenInvoice (or zero ??))
and supplier = givenSupplier
and status = givenStatus
and [anything else to get a SINGLE match]
2. check the recordcount from the query result
3. if the recordcount = 1,
then update the quantity...
else add a new record
<end>

I'll give you more assistance, if needed, but others may give
additional info. before this post. I would like to know about any
primay key(s). Also, what Access version do you have/use?

Thanks,
-PT

ma******@comh.co.za (Marlene) wrote in message news:<cd**************************@posting.google. com>...

Hi All

I have the following scenario, where I have found all the duplicates
in a table, based on an order number and a part number (item).I might
have something like this:
Order PODate Rec Qty Invoice# Item
Supplier Status POReceivedDate
570133 03/09/2004 50 0 DMEDIUM L0010 PENDING 03/09/2004
570133 03/09/2004 50 0 DMEDIUM L0010 PENDING 03/09/2004
570133 03/09/2004 50 0 DMEDIUM L0010 PENDING 03/09/2004

OR:

Order PODate RecQTY Inv Item Supplier Status POReceivedDate
571056 20/09/2004 1 0 D26032284 D0001 PENDING 20/09/2004
571056 20/09/2004 10 0 D8970654390 D0001 PENDING 20/09/2004
571056 21/09/2004 10 0 D8970654390 D0001 PENDING 21/09/2004
571056 20/09/2004 1 0 D26032284 D0001 PENDING 20/09/2004

Now I have to read through them line by line and then check if the
part is the same and then add the quantities and use the latest date.
So for example one I would have the following in the end:

Order PODate Rec Qty Invoice# Item
Supplier Status POReceivedDate
570133 03/09/2004 150 0 DMEDIUM L0010 PENDING 03/09/2004

And for example 2:
Order PODate RecQTY Inv Item Supplier Status POReceivedDate
571056 20/09/2004 2 0 D26032284 D0001 PENDING 20/09/2004
571056 21/09/2004 20 0 D8970654390 D0001 PENDING 21/09/2004

Could any one out there be able to help me??

Thank you very much!
Marlene

[marlene]

Dear PaulT

Thank you very much for your help so far! I have a problem though with
your queries, because what I am trying to do, is automate the whole
order 'cleanse' process. In other words, that would have to run at
night without any user intervention. The form where the code will
reside will be loaded automatically with a macro, that was set in
scheduled tasks to run at a time. When the form runs it will have to go
through that table and eliminate the duplicates. At the moment that
table (All Purchases) do not have any primary keys, because of all the
duplicates, but after the cleanse the unique records will be appended to
another table called: tbl_SupplierOrderLines, where the primary key is
the PurchaseOrderNumber and the ItemCode. Hope that this information
could help you a bit more! I am so depressed about this, I have spend
like 2 days and more on it doing only that, and not getting anywhere. I
shall add my 'developed code - so far' at the bottom of this message if
you would like to see what I have been doing so far. It is not working
correctly yet, because I am struggeling with the fact of when I have 4
or more lines of the same data...... then it just loops and loops...

Thanks again for your interest and help!

Kind Regards
Marlene

N.S. Here is the code (hope it makes sense at all!):

Option Compare Database
Option Explicit

Private Sub cmdEliminate_Click()
'Recordsets
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim dbsMax As DAO.Database
Dim rstMax As DAO.Recordset
Dim dbsMaxCount As DAO.Database
Dim rstMaxCount As DAO.Recordset
'Keeping track of the order lines as gets read
Dim ordlineone As String 'Order Numbers
Dim ordlinetwo As String
Dim intqtyone As Integer 'Quantities
Dim intqtytwo As Integer
Dim inttotal As Integer 'Total of Quantities
Dim dateone As Date 'Dates
Dim datetwo As Date
Dim invnumberone As String 'Invoice Numbers
Dim invnumbertwo As String
Dim statone As String 'Statusses
Dim stattwo As String
Dim partone As String 'Parts
Dim parttwo As String
Dim supone As String 'Suppliers
Dim suptwo As String
Dim rdateone As Date 'Received Dates
Dim rdatetwo As Date
Dim intMax As Integer 'Max count of an order in the file
Dim intMaxCount As Integer
Dim valid As Boolean 'Amount of times to loop
'counters
Dim intcountord As Integer 'Count appearance of a order number in
the file
Dim intcounter As Long 'Count amount of records in file
Dim fourlineqty As Integer
Dim i As Integer 'used in loop 2 maybe
Dim backord As String 'To keep track of order if more than 2
occurances maybe
intcountord = 0
intcounter = 0
intqtyone = 0
intqtytwo = 0
inttotal = 0
ordlineone = ""
ordlinetwo = ""
invnumberone = ""
invnumbertwo = ""
dateone = 0
datetwo = 0
rdateone = 0
rdatetwo = 0
statone = ""
stattwo = ""
partone = ""
parttwo = ""
supone = ""
suptwo = ""
valid = False
i = 0
backord = ""

'====================
'Database connections
'====================

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT [All
Purchases].PurchaseOrderNumber, [All Purchases].PurchaseOrderDate, [All
Purchases].ReceivedQuantity, [All Purchases].InvoiceNumber, [All
Purchases].ItemCode, [All Purchases].SupplierCode, [All
Purchases].Status , [All Purchases].[PurchaseOrderReceivedDate]" & _
"FROM [All Purchases] " & _
"WHERE ((([All
Purchases].PurchaseOrderNumber) In " & _
"(SELECT
[PurchaseOrderNumber] " & _
"FROM [All Purchases] As
Tmp " & _
"GROUP BY
[PurchaseOrderNumber],[ItemCode],[SupplierCode],[Status] " & _
"HAVING Count(*)>1 " & _
"And [ItemCode] = [All
Purchases].[ItemCode] " & _
"And [SupplierCode] =
[All Purchases].[SupplierCode] " & _
"And [Status] = [All
Purchases].[Status]))) " & _
"ORDER BY [All
Purchases].PurchaseOrderNumber" & _
", [All Purchases].ItemCode, [All
Purchases].Status")

Do While Not rst.EOF
If rst("PurchaseOrderNumber") = "570372" Then

MsgBox rst("PurchaseOrderNumber")
End If

Set dbsMaxCount = CurrentDb()
Set rstMaxCount = dbsMaxCount.OpenRecordset("SELECT [Find
duplicates on PO#,Item,Status,Supplier for All
Purchases].PurchaseOrderNumber, [Find duplicates on
PO#,Item,Status,Supplier for All Purchases].ItemCode, Count([Find
duplicates on PO#,Item,Status,Supplier for All
Purchases].PurchaseOrderNumber) AS [Counter]" & _
"FROM [Find
duplicates on PO#,Item,Status,Supplier for All Purchases] " & _
"where [Find
duplicates on PO#,Item,Status,Supplier for All
Purchases].PurchaseOrderNumber = '" & rst("PurchaseOrderNumber") & "'" &
_
" AND [Find
duplicates on PO#,Item,Status,Supplier for All Purchases].ItemCode = '"
& rst("ItemCode") & "'" & _
"GROUP BY [Find
duplicates on PO#,Item,Status,Supplier for All
Purchases].PurchaseOrderNumber, [Find duplicates on
PO#,Item,Status,Supplier for All Purchases].ItemCode " & _
"HAVING
(((Count([Find duplicates on PO#,Item,Status,Supplier for All
Purchases].PurchaseOrderNumber))>2))")
If Not rstMaxCount.EOF Then
intMaxCount = rstMaxCount("Counter")
End If
'MsgBox rst("PurchaseOrderNumber") & " " & rst("ItemCode")

If intMaxCount = 3 And intcountord = 0 Then 'will work for 3
If fourlineqty <> 0 Then
rst.Edit
rst("ReceivedQuantity") = fourlineqty +
rst("ReceivedQuantity")
rst.Update

End If
ordlinetwo = ""
ordlineone = ""
intqtytwo = 0
intqtyone = 0
datetwo = 0
dateone = 0
invnumbertwo = ""
invnumberone = ""
stattwo = ""
statone = ""
parttwo = ""
partone = ""
suptwo = ""
supone = ""
rdatetwo = 0
rdateone = 0
intcountord = 0
backord = ""
intMaxCount = 0

rst.MoveNext

End If

If intcountord = 0 Then
'backord = ordlineone
invnumberone = ""
invnumbertwo = ""
partone = ""
parttwo = ""
ordlineone = ""
ordlinetwo = ""
statone = ""
stattwo = ""
dateone = 0
datetwo = 0
rdateone = 0
rdatetwo = 0
supone = ""
suptwo = ""
intqtyone = 0
intqtytwo = 0
'backord = ""
End If

intcountord = intcountord + 1 'Count appearance of a order
number in the file
intcounter = intcounter + 1 'Count amount of records in
file

If intcounter = 1 Or intcountord = 0 Then 'the first read
of the file
ordlineone = rst("PurchaseOrderNumber")
intqtyone = rst("ReceivedQuantity")
dateone = rst("PurchaseOrderDate")
invnumberone = rst("InvoiceNumber")
statone = rst("Status")
partone = rst("ItemCode")
supone = rst("SupplierCode")
statone = rst("Status")
rdateone = rst("PurchaseOrderReceivedDate")
rst.Edit
rst.Delete
rst.MoveNext
Else 'intcounter > 1

'=========================
'Going to a new order now
'=========================

If (invnumberone = "" And partone = "" And ordlineone = ""
And statone = "" And dateone = 0 And supone = "" And intqtyone = 0) Then
ordlineone = rst("PurchaseOrderNumber")
intqtyone = rst("ReceivedQuantity")
dateone = rst("PurchaseOrderDate")
invnumberone = rst("InvoiceNumber")
statone = rst("Status")
partone = rst("ItemCode")
supone = rst("SupplierCode")
statone = rst("Status")
rdateone = rst("PurchaseOrderReceivedDate")

rst.Edit
rst.Delete

'MsgBox "ordline one " & ordlineone & " ordlinetwo " &
ordlinetwo & "invone " & invnumberone

If ordlineone <> ordlinetwo Then
ordlinetwo = ""
intqtytwo = 0
datetwo = 0
invnumbertwo = ""
stattwo = ""
parttwo = ""
suptwo = ""
rdatetwo = 0

intcountord = 1
rst.MoveNext
End If
Else
If (invnumberone = "" And partone = "" And ordlineone =
"" And statone = "" And dateone = 0 And supone = "" And intqtyone = 0)
Or (intcounter = 2) Or (intcountord = 2) Then
'or intcountord = 2 and all twos are blank!!

ordlinetwo = rst("PurchaseOrderNumber")
intqtytwo = rst("ReceivedQuantity")
datetwo = rst("PurchaseOrderDate")
invnumbertwo = rst("InvoiceNumber")
stattwo = rst("Status")
parttwo = rst("ItemCode")
suptwo = rst("SupplierCode")
rdatetwo = rst("PurchaseOrderReceivedDate")

If ordlinetwo = ordlineone Then
If partone = parttwo Then
If supone = suptwo Then
If statone = stattwo Then
'===============
'invoice numbers
'===============
If invnumberone = 0 And invnumbertwo
<> 0 Then

If dateone > datetwo Then
rst.Edit
rst("PurchaseOrderDate") =
dateone
rst.Update
End If
If rdateone > rdatetwo Then
rst.Edit

rst("PurchaseOrderReceivedDate") = rdateone
rst.Update
End If
'every field was checked now
update qty
'total of quantities
inttotal = intqtyone + intqtytwo
'MsgBox inttotal
rst.Edit
rst("ReceivedQuantity") = inttotal
rst.Update

invnumberone = ""
partone = ""
ordlineone = ""
statone = ""
dateone = 0
supone = ""
intqtyone = 0
rdateone = 0

intcountord = 0
backord = ordlinetwo
rst.MoveNext
Else
If invnumberone = 0 And
invnumbertwo = 0 Then
If dateone > datetwo Then
rst.Edit
rst("PurchaseOrderDate") =
dateone
rst.Update
End If
If rdateone > rdatetwo Then
rst.Edit

rst("PurchaseOrderReceivedDate") = rdateone
rst.Update
End If
Else
If invnumberone <> 0 And
invnumbertwo = 0 Then
rst.Edit
rst("InvoiceNumber") =
invnumberone
rst.Update
End If
End If
'every field was checked now
update qty
'total of quantities
inttotal = intqtyone + intqtytwo

rst.Edit
rst("ReceivedQuantity") = inttotal
rst.Update
backord = ordlinetwo
intcountord = 0
If intMaxCount = 3 Then
fourlineqty =
rst("ReceivedQuantity")
'fourlineqty = 6
rst.Edit
rst.Delete
'rst.Update

invnumberone = ""
invnumbertwo = ""
partone = ""
parttwo = ""
ordlineone = ""
ordlinetwo = ""
statone = ""
stattwo = ""
dateone = 0
datetwo = 0
rdateone = 0
rdatetwo = 0
supone = ""
suptwo = ""
intqtyone = 0
intqtytwo = 0
Else
fourlineqty = 0
End If

rst.MoveNext
End If
'END invoice numbers
'===================

'Statusses NOT the Same
Else
If statone = "RETURN" And stattwo =
"pending" Then
backord = ordlinetwo
intcountord = 0
rst.MoveNext 'line one should have
been deleted already so ignore this second line
Else
If stattwo = "RETURN" And statone
= "PENDING" Then
'update the whole line two
with line one's details
rst.Edit
rst("ReceivedQuantity") =
intqtyone
rst("PurchaseOrderDate") =
dateone
rst("InvoiceNumber") =
invnumberone
rst("Status") = statone
'should be pending

rst("PurchaseOrderReceivedDate") = rdateone
rst.Update

invnumberone = ""
partone = ""
ordlineone = ""
statone = ""
dateone = 0
supone = ""
intqtyone = 0
rdateone = 0

intcountord = 0
backord = invnumbertwo
rst.MoveNext
End If 'should only have pending
and return statusses
End If

End If
'END Statusses
'=============

'Suppliers NOT the same
'NO CASE LIKE THIS for this query
End If
'=======================================
Else 'might be same number but diff part
ordlineone = rst("PurchaseOrderNumber")
intqtyone = rst("ReceivedQuantity")
dateone = rst("PurchaseOrderDate")
invnumberone = rst("InvoiceNumber")
statone = rst("Status")
partone = rst("ItemCode")
supone = rst("SupplierCode")
statone = rst("Status")
rdateone = rst("PurchaseOrderReceivedDate")
rst.Edit
rst.Delete

End If 'parts
End If 'orderlines the same
End If
End If
End If
'rst.MoveNext
Loop

MsgBox "Records Updated: " & Str(intcounter), vbInformation,
"Updated PurchaseOrderNumbers"
rst.Requery
MsgBox rst.RecordCount

If rst.RecordCount = 0 Then
rstMaxCount.Close
dbsMaxCount.Close
rst.Close
dbs.Close
End If
'====================
'Close DB Connections
'====================
End Sub
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[PaulT]

Sorry that I didn't get to this yesterday, but I should be able to do
so today.

So, at least now I understand that you wish to work this from a
back-end perspective (after all records have been written), but what I
initially was trying to suggest was a front-end (on-going) approach,
where a matching entry is searched and updated (if one exists) and
create entries that don't exist. [The on-going approach would
eliminate the 'hassle' you're experiencing now...]

But, it doesn't matter to me, we'll try to develop a solution your
way. I haven't fully looked at your source yet, but that is the plan
today.

-PT

[PaulT]

Ok!, are we ready for the short version???

I first created a table with your short list of duplicate orders (from
your initial post), and now to the fun part...

1. I first created a query to get a list of distinct rows from the
table of duplicates, which looks like this [I saved it as
'qryDistinctOrderRows' that is opened by the function in #2]:

SELECT DISTINCTROW Table2.Order, Table2.POdate, Table2.RecQty,
Table2.InvNo, Table2.Item, Table2.Supplier, Table2.Status,
Table2.PORecDate
FROM Table2
GROUP BY Table2.Order, Table2.POdate, Table2.RecQty, Table2.InvNo,
Table2.Item, Table2.Supplier, Table2.Status, Table2.PORecDate;

2. Then I created a function that uses this query as a list to do a
domain-sum for each entry in the list:, a.k.a. DSum(<field-2-sum>,
<duplicatesTable>, <where-condition>),

Const distinctQuery As String = "qryDistinctOrderRows"
Const outputTable As String = "Table3" ' whatever your output table is

Public Function sumOrders()
Dim db As DAO.Database
Dim distinctGroupList As DAO.Recordset
Dim groupTotaled As DAO.Recordset
Dim whereClause As String
Dim x, recCount, recQtyTotal As Integer ' 3 vars. here

recCount = 0
Set db = CurrentDb
Set distinctGroupList = db.OpenRecordset(distinctQuery)
Set groupTotaled = db.OpenRecordset(outputTable)

recCount = distinctGroupList.RecordCount

If recCount > 0 Then
distinctGroupList.MoveFirst

With distinctGroupList
For x = 1 To recCount
' NOTE 1: I don't add invoice# to whereClause...
' NOTE 2: build whereClause for DSum function that matches
' each distinct group list row values.
whereClause = _
"[order] = '" & distinctGroupList!order & _
"' And [podate] = #" & distinctGroupList!podate & _
"# And [item] = '" & distinctGroupList!Item & _
"' And [supplier] = '" & distinctGroupList!supplier &
_
"' And [status] = '" & distinctGroupList!Status & _
"' And [porecdate] = #" & distinctGroupList!porecdate
& "#"

' sum the recQty field, restricted to the whereClause
' see the help on DSum [there are some "gotchas" to be
aware about]
recQtyTotal = _
DSum("[recQty]", "Table2", whereClause)

' Now that we've got the sum you want, write the
record
' to your table...
With groupTotaled
.AddNew ' use insert...into, if you'd prefer.
![order] = distinctGroupList!order
![podate] = distinctGroupList!podate
![recQty] = recQtyTotal ' the total is written
here

' NOTE: I don't have invoice# here...

![Item] = distinctGroupList!Item
![supplier] = distinctGroupList!supplier
![Status] = distinctGroupList!Status
![porecdate] = distinctGroupList!porecdate
.Update ' write it!
End With ' end groupTotaled

distinctGroupList.MoveNext
Next x
End With 'end distinctgrouplist
Else
MsgBox "Hey, I didn't find a distinct listing."
End If

distinctGroupList.Close
groupTotaled.Close
db.Close
End Function

3. just some other minor notes:
3.1 you may have to modify the (')(")(#) pairing depending on what
data types you have
3.2 Make sure the qty field is a number type, or wrap it with Val()
3.3 I leave you with adding the invoice# (see the comments)
3.4 if you have ANY question about this, let me know.

[marlene]

Dear PaulT

I realy want to thank you for all the effort and time you have put in
with my problem! I have not realy had a look at the code, yet, but will
definately do so today sometime. I appreciate all your help! I did not
think that there would be anyone out there realy worrying about my
problem! Thanks again! Will let you know if I have a problem
understanding or somethingn like that!

Cheers
Marlene

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[PaulT]

Test post. I don't see my post from last night, and it's been over 9
hrs.

If this goes through, I'll immediately re-post the query and function
I posted last night...

basically, this is what I did to achieve your result (again, I'll
repost the query & function tonight -- if this goes through...)

1. a queried a DistinctRow list of your duplicates...
2. used #1 in a function to loop through each entry which does two
things:
2.1. pre-builds a where clause for DSum() with values from the
distinctrow list.
2.2. gets the total from DSum
2.3. writes a single entry to another table with the new sum.

that's about it. The function is only around 30-40 lines (I think).

sorry that hasn't come through yet... :-(

-PT