Duplicates

[FireStrike]

I have a table that has country_code, Mfr_num, Item_num, catalog_code, mfr_obsolete_code. the key is on country_code, mfr_num, and item_num. I need to find duplicates of mfr_num and catalog_code. Can anyone point me in the right direction on this?

 

[ByteMyzer]

SELECT T1.*
FROM [b]MyTable[/b] AS T1
WHERE EXISTS
 (SELECT T2.mfr_num, T2.catalog_code
  FROM [b]MyTable[/b] AS T2
  GROUP BY T2.mfr_num, T2.catalog_code
  HAVING T2.mfr_num=T1.mfr_num
  AND T2.catalog_code=T1.catalog_code
  AND COUNT(T2.catalog_code)>1)

(substitute MyTable with the actual table name)

 

[FireStrike]

everytime I try this access crashes. I have a suspion that the number of records that I have could cause a problem. there are about 2.5 million records in my table. When I use the duplicate query wizard, it just times out.

 

[ByteMyzer]

1) Do you have indexes on the fields upon which you are grouping/querying?
2) Have you run a compact/repair on the database?
3) 2.5 million records??? Have you considered upsizing to MSDE?

 

[FireStrike]

my table is indexed. The problem was that the table is running on a sequel server<sp> I think I was able to get it working. I imported the table to my access, and it has been running my query for an hour and a half now. Unfortunately, because I am at work, getting software is very tough. Once it is completed I will let you know for sure if it worked. This may take a while though.

 

[ByteMyzer]

JIMINY! If the backend is on SQL Server, you should be able to run the query as a Pass-Through query. Don't use Jet for such a cumbersome set of data; use a Pass-Through query and let the server do the work. I think you'll find it to be much faster.

 

[FireStrike]

would you be able to tell me how that can be done? Also do you know if this would cause problems on the server? It is a production server and to bring it down could have adverse affects on my current employment status.

 

[ByteMyzer]

1) In the Queries tab, double-click on Create query in Design view
2) Close the Show Table dialog box
3) From the Menu Bar, select Query > SQL Specific > Pass-Through
4) In the SQL Pane, paste the text of the SQL query from my earlier post
5) Click on the ! to run the query

 

[ByteMyzer]

Whoops! Almost forgot to mention: before running the query (Step 5), select the query properties (ALT-ENTER), then in the ODBC Connect Str field, enter: ODBC;DSN=MYDSN (substitute MYDSN with the actual DSN name for your SQL Database)

 

[FireStrike]

I tried it, and it did not work. It gave me a -3807 error saying that the table does not exist. But I double checked the spelling, and it does exist. Could that be a general type error, if the server does not allow pass-through?

 

[RV]

SELECT T1.*
FROM [b]MyTable[/b] AS T1
WHERE EXISTS
 (SELECT T2.mfr_num, T2.catalog_code
  FROM [b]MyTable[/b] AS T2
  GROUP BY T2.mfr_num, T2.catalog_code
  HAVING T2.mfr_num=T1.mfr_num
  AND T2.catalog_code=T1.catalog_code
  AND COUNT(T2.catalog_code)>1)

(substitute MyTable with the actual table name)

Seems like a bit of overkill to me
This will do fine:

SELECT T1.*
FROM MyTable AS T1
WHERE EXISTS
(
SELECT *
FROM MyTable AS T2
WHERE T2.mfr_num=T1.mfr_num
AND T2.catalog_code=T1.catalog_code
)

This statement will also run a bit faster

RV

 

[ByteMyzer]

RV:
Your query will not determine which records are duplicates. All your query does is check for the existance of a record in table alias T1 in that exists in table alias T2 with matching mfr_num and catalog_code. Since both table aliases are for the same table, it will simply return all the records!

FireStrike:
Did you pay attention to the other part of my post?

(substitute MyTable with the actual table name)

 

[RV]

RV:
Your query will not determine which records are duplicates. All your query does is check for the existance of a record in table alias T1 in that exists in table alias T2 with matching mfr_num and catalog_code. Since both table aliases are for the same table, it will simply return all the records!

You're quite right.
I totally overlooked that you're joining the table with itself

RV

 

[FireStrike]

ok this was a complete disaster. I tried just running the queries given, and had to kill them after they were running for over 18 hours. I decided to create a form with a label, and a command button. The code behind this form Created my results in less than an hour. I am posting my code here incase anybody else has a similar problem. Just a note before hand, my sim_mfr_no is a 6 digit number, that I sotred as a string, because it may have a leading zero.

/code

Private Sub cmdDoStuff_Click()
Dim rcdDup As Recordset
Dim lngIndex As Long
Dim strSql As String
Dim arrData() As String * 30
Dim I As Long
Dim T As Integer
Dim bolImpNext As Boolean

DoCmd.SetWarnings False

For T = 0 To 9
DoEvents
lblNum.Caption = T
bolImpNext = False
strSql = "select * from tblDupBob Where sim_mfr_no Like '" & T & "*' ORDER BY tblDupBob.sim_mfr_no, tblDupBob.catalog_no"
Set rcdDup = CurrentDb.OpenRecordset(strSql)
DoEvents
If rcdDup.RecordCount > 0 Then
rcdDup.MoveLast
lngIndex = rcdDup.RecordCount
ReDim arrData(4, lngIndex) As String * 30
rcdDup.MoveFirst
For I = 1 To lngIndex
arrData(1, I) = rcdDup!sim_mfr_no
arrData(2, I) = rcdDup!catalog_no
arrData(3, I) = rcdDup!mfr_obsolete_code
arrData(4, I) = rcdDup!sim_item_no
While InStrRev(arrData(2, I), "'")
DoEvents
arrData(2, I) = Mid(arrData(2, I), 1, (InStrRev(arrData(2, I), "'") - 1)) & Mid(arrData(2, I), (InStrRev(arrData(2, I), "'") + 1))
Wend
While InStrRev(arrData(2, I), Chr(34))
arrData(2, I) = Mid(arrData(2, I), 1, (InStrRev(arrData(2, I), Chr(34)) - 1)) & Mid(arrData(2, I), (InStrRev(arrData(2, I), Chr(34)) + 1))
Wend
rcdDup.MoveNext
Next I

For I = 1 To lngIndex
If I < lngIndex Then
If (arrData(1, I) = arrData(1, I + 1) And arrData(2, I) = arrData(2, I + 1)) Or bolImpNext Then
strSql = "insert into tblDup (sim_mfr_no, catalog_no, mfr_obsolete_code, sim_item_no) " & _
"select '" & arrData(1, I) & "','" & arrData(2, I) & "','" & arrData(3, I) & "','" & arrData(4, I) & "'"
DoCmd.RunSQL (strSql)
If Not bolImpNext Then
bolImpNext = True
Else
bolImpNext = False
End If
Else
bolImpNext = False
End If
ElseIf bolImpNext And I = lngIndex Then
strSql = "insert into tbldup (sim_mfr_no, catalog_no, mfr_obsolete_code, sim_item_no) " & _
"select '" & arrData(1, I) & "','" & arrData(2, I) & "','" & arrData(3, I) & "','" & arrData(4, I) & "'"
DoCmd.RunSQL (strSql)
End If
Next I
End If
rcdDup.Close
Next T
MsgBox "done"
End Sub

/code

If you have any question, please feel free to let me know. And thank you all for all your help. It is VERY much appriciated.

 

[FireStrike]

hmm I though /code worked, can someone tell me what it is supposed to be?