Find Duplicates Query

[AlexanderBB]

I tried the query wizard for this, but it works on one field. I need to join 2 fields together then check for duplicates. I did change the Field in query design to 'LC: [Label] & [Number]'
then used LC as the field name, but no luck.
Is there another way without making a new table with those 2 fields already joined?

Thanks.

 

[SydneyGeek]

See if this helps. It requires an ID field and a subquery, to count the duplicates. You also get the option to delete the dups if required.
Deleting Duplicate Records in Access

Denis

 

[AlexanderBB]

Thanks Denis. Interesting... I did end up finding this


SELECT f2, f3 FROM myTable GROUP BY f2, f3 HAVING COUNT(*) >1

seems to work OK. But now struggling over another problem.
A field called "Serial" in my table has a underscore delimiter. I want to select where the left Part length is 2.
In query design I'm putting 2 in Criteria and in Field

Len(Split([Serial],"_")(0))

Its going to error, but the syntax is OK and gives right answer testing it in immediate window.

Any thought, workaround for that ?

Thanks.




</pre>

 

[SydneyGeek]

Glad to see you found something. The differences between what I posted and the answer that you found are that the subquery method: (1) Shows the whole record and (2) lets you do something to the duplicates, leaving the original in the table.

As for the second question, Access can't use Split directly in a query. It's a VBA function, so you need to create a function to use it.

Two options to try:
1. Using other text functions -- Len(Left([Serial],InStr(1,[Serial],"_")-1))

2. Creating a custom function --
Put this in a new module.

Function StringParse(strIn As String, strDelim As String, intWhich As Integer) As String
    Dim x
    x = Split(strIn, strDelim)
    StringParse = x(intWhich)
End Function

Now use it like this in the query
Len(StringParse([Serial],"_",0))

This function lets you choose your delimiter, and which chunk you want. 0 is the first.

Denis

 

[AlexanderBB]

Cool, thanks Denis. That was very helpful and I followed it all.. now we're all go !!
Rgds, ABB