How you find duplicates but only just for the first 12 characters in a cell? (1 Viewer)

Vir77

New member
Local time
Today, 06:47
Joined
May 23, 2022
Messages
5
Hi people,

I want to make a query where I can find duplicate cells. But the query must not check the complete cell. Just the first 12 characters in the cells must be checked on duplicate values. Below you can see my tabel. For example:
NL_1102CW_150___AMSTERDAM
NL_1102CW_150___AMSTERDAM ZUIDOOST
must be marked as duplicate.


NL_1105AG_10___AMSTERDAM
NL_1105AG_100___AMSTERDAM ZUIDOOST
NL_1105AG_100A___AMSTERDAM ZUIDOOST
must be marked as duplicate


TABEL

Name
NL_1101ED_20Vri___AMSTERDAM
NL_1102BR____AMSTERDAM
NL_1102CW_150___AMSTERDAM
NL_1102CW_150___AMSTERDAM ZUIDOOST
NL_1105AG_10___AMSTERDAM
NL_1105AG_100___AMSTERDAM ZUIDOOST
NL_1105AG_100A___AMSTERDAM ZUIDOOST
NL_1105AG_1___AMSTERDAM ZUIDOOST


I tried criteria:
In (SELECT [Name] FROM [locations gemeente amsterdam - EXPERIMENT] As Tmp GROUP BY [Name] HAVING "Duplicate" )
But this one is checking the complete cell. That's not what I want, I just want a first 12 characters check.
The Query wizard is not giving that option.

Do you have any idea?
 

Minty

AWF VIP
Local time
Today, 05:47
Joined
Jul 26, 2013
Messages
9,286
The first twelve characters of a field will be returned using (in a query)

Code:
MyFirst12Chars: Left([YourVeryLongFieldNameWithSpaces],12)

So use that as the comparison and grouping field.
 

Ranman256

Well-known member
Local time
Today, 00:47
Joined
Apr 9, 2015
Messages
4,102
make a query with MINTY's field, say Q1,
then use the query wizard, to make FIND DUPLICATES QUERY,
choose Q1 as the source.
voila!
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:47
Joined
Sep 21, 2011
Messages
10,544
They are not cells either, they are fields. :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:47
Joined
Feb 19, 2002
Messages
36,296
But, welcome aboard.

In a relational database, we don't mush values together. The first 12 probably belong in a separate field and that will make working with them ever so much easier.
 

Vir77

New member
Local time
Today, 06:47
Joined
May 23, 2022
Messages
5
The first twelve characters of a field will be returned using (in a query)

Code:
MyFirst12Chars: Left([YourVeryLongFieldNameWithSpaces],12)

So use that as the comparison and grouping field.
Yess! This solution works! Thank you so much!
 

Vir77

New member
Local time
Today, 06:47
Joined
May 23, 2022
Messages
5
make a query with MINTY's field, say Q1,
then use the query wizard, to make FIND DUPLICATES QUERY,
choose Q1 as the source.
voila!
Yess! This solution works! Thank you so much!
 

Vir77

New member
Local time
Today, 06:47
Joined
May 23, 2022
Messages
5
But, welcome aboard.

In a relational database, we don't mush values together. The first 12 probably belong in a separate field and that will make working with them ever so much easier.
Thank you. I understand what you're saying. To separate the first 12 characters in a separated field/column (which also can be done in the excel-sourcefile) will also solve the problem. Then it will be easy with the Query wizard.
 

Users who are viewing this thread

Top Bottom