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

[Vir77]

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]

The first twelve characters of a field will be returned using (in a query)

MyFirst12Chars: Left([YourVeryLongFieldNameWithSpaces],12)

So use that as the comparison and grouping field.

 

[Ranman256]

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]

They are not cells either, they are fields.

 

[Pat Hartman]

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]

The first twelve characters of a field will be returned using (in a query)

MyFirst12Chars: Left([YourVeryLongFieldNameWithSpaces],12)

So use that as the comparison and grouping field.

Yess! This solution works! Thank you so much!

 

[Vir77]

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]

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.