Having Count duplicate names (1 Viewer)

azhar2006

Registered User.
Local time
Today, 06:00
Joined
Feb 8, 2012
Messages
202
Hello guys
I have a query that searches for duplicate names and uses this expression. But this expression finds the repeater and matches 100%. ..what I want is for the expression to count only the first 10 characters of the name. how can I do that .
Code:
In (SELECT [F] FROM [tbl] As Tmp GROUP BY [F] HAVING Count(*)>1 )
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:00
Joined
Sep 21, 2011
Messages
14,052
Use the Left() function.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:00
Joined
Oct 29, 2018
Messages
21,358
Always a good idea to present a set of sample data to better illustrate the issue. Me thinks.
 

moke123

AWF VIP
Local time
Today, 09:00
Joined
Jan 11, 2013
Messages
3,852
But this expression finds the repeater and matches 100%. ..what I want is for the expression to count only the first 10 characters of the name. how can I do that .
Are you trying to also match similiar names or miss-spellings?
If so you can use functions utilizing such things as Levenschtein Distance and soundex.
 

azhar2006

Registered User.
Local time
Today, 06:00
Joined
Feb 8, 2012
Messages
202
Are you trying to also match similiar names or miss-spellings?
If so you can use functions utilizing such things as Levenschtein Distance and soundex.
good morning . This is exactly what I want to do. But I'm having a problem with that
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:00
Joined
Jan 23, 2006
Messages
15,364
What exactly is the problem/issue? Please give an example or two.
 

azhar2006

Registered User.
Local time
Today, 06:00
Joined
Feb 8, 2012
Messages
202
What exactly is the problem/issue? Please give an example or two.
Michael Thomas
Michael Toma
Thank you very much jdraw
for the response and for looking into my post. If we look at the employee named Michael Thomas. If we find that his name is repeated twice, but Access does not consider it to be repeated twice because in the first name his name is Michael Thomas and in the second name Michael Thoma ... there is no last letter. Here, the entry employee may have entered the name and did not pay attention to writing the last letter. I want Access to search for a repeater for Michael Touma. If we notice that the number of characters with the ruler on the keyboard is 10, Access will show me the repeated names of up to 10 characters. This is what I meant.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:00
Joined
Feb 19, 2002
Messages
42,981
Leaving off the last letter is only one of many types of possible mistakes.. You might want to try using Soundex. I don't have the code handy but you should find several versions here or using google. Soundex is an algorithm that eliminates vowels and assigns unique values to consonants and diphthongs to come up with a mathematical value for each name. It can equalize transpositions and some spelling variations.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:00
Joined
Jan 23, 2006
Messages
15,364
Do you have an authoritative table somewhere that assigns a unique identifier to each Employee? It seems that is what you are lacking, and maybe what you are trying to accomplish. If you provide a list of names in a table/database, I'm sure you'll get some responses to deal with different patterns --last character/first character/3characters....
 

moke123

AWF VIP
Local time
Today, 09:00
Joined
Jan 11, 2013
Messages
3,852
Adding to what Pat suggested, you can use soundex and Levenshtein distance to find matches and near matches. Levenshtein Distance measures how many steps it takes to turn one word into the other, or something like that. Google it.

You didnt say whether this is a one time exercise or something that will be recurring. Hopefully you have a table of employees and use the PKey in any other tables. Your real issue is doing the work up front and not allowing the users to make those mistakes.

Here's an old example which is close to what I do with names.
 

Attachments

  • searchdb3.zip
    106.5 KB · Views: 262

azhar2006

Registered User.
Local time
Today, 06:00
Joined
Feb 8, 2012
Messages
202
My dear friends and dear teachers. Attached is an example database. If we run the query, we will see the name of one of the scientists that has been repeated, and it is actually repeated in the table. But the scientist Leonhard Euler also repeated, but it did not appear that there is a separator between the name of the person and the name of his father. The question is how can I show the duplicate even if there are errors in writing??
 

Attachments

  • ANo.accdb
    408 KB · Views: 296

moke123

AWF VIP
Local time
Today, 09:00
Joined
Jan 11, 2013
Messages
3,852
One record of Leonhard Euler has 2 spaces between the names and the other has one.

You should store the first and last names seperately and have a secondary way of showing the distiction such as Jr. or Sr, or date of birth. Use trim() to make sure there are no extra spaces.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:00
Joined
Jan 23, 2006
Messages
15,364
I agree with moke123 re use of Trim. You might start by replace(Fullname," "," "). You know your data and the purpose of the table and database, readers do not. Might be helpful if you provided some context.
Depending on how many records you have, you may want to, and should, break fullname into FirstName and LastName; then sort on Lastname, Firstname. Shouldn't be too tedious to resolve.
 

Users who are viewing this thread

Top Bottom