query Double Id's on partial match

[b092840]

Good afternoon all,

I have a database with a lot of fields.
One of the fields is a UserId. That should be unique and is also the only key in the table.
Unfortunately multiple Id's are created for the same person.
Example Data:

B092840  Bert Kruis
4444         P.Plank
92840        B. Kruis
12345_EP C.Baker
EP92840  Kruis B.
4444_FC  Plank P.

A want to have a list that shows only the double UserIds

UserId   UserName1   UserId2  UserName2
92840    B. Kruis    B092840  Bert Kruis
92840    B. Kruis    EP92840  Kruis B.
4444     P.Plank     4444_FC  Plank P.

I've created a query/ SQL that didnot work.
Or it showed every record (approx 90.000 users) or it showed nothing. So i duplicated the table and tried to write a query again. That didnot work either.

Here is what I've created.

SELECT TblUSR02.BNAME, USR02.BNAME
FROM USR02 INNER JOIN TblUSR02 ON USR02.BNAME = TblUSR02.BNAME
WHERE (((USR02.BNAME) Like "*” & [TblUSR02]![BNAME] &”*" And (USR02.BNAME) In (SELECT [BNAME] FROM [USR02] As Tmp GROUP BY [BNAME] HAVING Count(*)>1 )))
ORDER BY USR02.BNAME;

Can anyone advise how to solve this. (hopefully without having duplicating the table).

Many Thanks

[Seth Schrock]

The problem is that, while we humans know that Bert Kruis is probably the same person as Kruis B. and B. Kruis, there isn't a way for the computer to know that. You would need some other field like SSN so that the computer can perform the match.

[NeoPa]

As Seth has said, the way you've stored the data means there is no reliable way to identify which items match.

In order to program a computer to do any work, you must first be able to explain how the work can be done - even as a human. I see no way from the information and data supplied that I could even do it reliably as a human, so telling the computer how to do it would be impossible.

I notice that the IDs include a numeric value that would match, but that is stored in a way that is not predictable or able to be described precisely. if you had this information stored in a practical way then the task would be easy. Do you have that number stored elsewhere? If so, then that would have been helpful and important to include in your question. If not then you've probably just learned the importance of proper table design the hard way.

You will surely benefit from checking out Database Normalisation and Table Structures.

[PhilOfWalton]

How many users?
What is the significance of the UserID with a version of a name and a number with or without letters? What is each bit for?

My instinct says that a user should be picked from a combo box and that the rest of your UserID should be in a separate field.

Phil

[NeoPa]

As a further point, your use of ” in your SQL, instead of ", indicates that you may have been working on it in a word processor - rather than a text editor. That is a very bad idea as these characters, and various others that may be substituted, may look similar but are not recognised by SQL - of pretty well any flavour - as meaning the same thing.

Also, the & concatenation character needs to have spaces around it to be recognised for what it is. Many spaces are optional in SQL. These aren't.

[b092840]

Thanks Phil and NeoPa.
I just extracted the list (about 90.000 users) from a application to identify the access the users have. By analyzing the duplicate Id's(numbers)I'm able to analyze any SoD. So i didnot build a access database but I'm using a database for querying and cleansing Users (Id's).

[PhilOfWalton]

Sorry to say I agree with Seth & NeoPa. I don't think it can be done without some form of structure.

Phil

[jforbes]

This is a situation where a computer program can only help with solving a problem. When you are attempting to scrub information that is this dissimilar, the only reliable way I know to perform this is to have a routine find possible matches and then display them to a user who makes the decision on what to do with the information (Merge, Delete, Add, or Ignore).

Also, it's much easier to start with a list of reliable known values and then compare against it with the data to be scrubbed. In your case, there is most likely a reliable users table available, that you can compare the 90,000 records against. If not, then I would create one, from your data, like by extracting all the rows with a UserID that is strictly numeric.

To determine what records to show the user, you would need a select statement to pull out anything that could be a match.
You could try some basic SQL like statements or some variations based on patterns you can predetermine. You could also look at these two articles as they might find results in a manner that a basic pattern wouldn't:
Levenshtein Approximate String Matching
NGram Approximate String Matching

[PhilOfWalton]

Having re-read the posts and mulled things over, I think there is some confusion.
I had a feeling that the original post had no spacing (or possibly 1 space) between the fields. I have a feeling this has been edited by placing it between the code tags and adding space. If so can we have the unedited version.
Secondly at one point you say there are about 90,000 records and elswhere you say there are about 90,000 users.
In the example given of 6 records there appear to be only 3 users - Kruis, Plank & Baker.
I note also on the example given that a person's initial is always followed by a full stop (sometimes with a space as well) Is this always the situation.
Can you tell us a bit more about the non-name part of the field i.e.
4444, EP92840, 12345_EP. They can't just be random numbers and letters: there must be some rules and logic in how they are derived.

IMO there is no hope of making progress until we have a reasonable chance of detecting the user's name.

Phil