Find Duplicates Query

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,705
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.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,705
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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.
Code:
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

Well-known Member
Joined
Jul 1, 2009
Messages
1,705
Cool, thanks Denis. That was very helpful and I followed it all.. now we're all go !!
Rgds, ABB
 

Watch MrExcel Video

Forum statistics

Threads
1,109,487
Messages
5,529,156
Members
409,851
Latest member
Ingar
Top