Find Duplicates - Transposed Numbers

[Neilbees]

Hi all

Apologies for the new thread, had a long search and solved most of my problems but one remains!

I'm working on a database that identifies duplicate invoice numbers. The one remaining issue I have is that the spec requests the ability for the database to find potential duplicates where an invoice number has been entered incorrectly by transposing numbers. So for example 123456 and 123465 would be potential duplicates. As would 123456 and 213456

Does anybody know whether this is even possible in Access and if so how I might start to solve this either with a query or with VBA?

My eternal thanks as always to those who take the time to read this!

 

[PNGBill]

Confused as to why you would want such a solution but one way would be to sum the digits and find all occurrences of the same sum.
Transposed numbers must sum the same.

In the early days of point of sale check digits were used but I don't think this is bothered with now.

 

[Khalid_Afridi]

Confused as to why you would want such a solution but one way would be to sum the digits and find all occurrences of the same sum.
Transposed numbers must sum the same.

In the early days of point of sale check digits were used but I don't think this is bothered with now.

Good point PNGBill,
but how to sum up transpose digits?

 

[PNGBill]

I guess you would extract the left digit 6 times and sum the 6 fields.
Not sure if there is a function that sums the individual digit values of a number - if there is, then the answer is quick and easy.

The real issue is once you have the "possible" duplicate records how do you Analise them and decide what are real numbers and what are errors, or more importantly, what could be errors in future, if such a thing is possible.

Check digits were supposed to do this by the last digit being some result of the previous digits to check if it is a "true" number but how this really worked is confusing to me.

Most of this sort of thing is dream't up by people who want to go back to ledger cards and or believe that computer accounts must be "perfect" as if the old system ever was!.

These are the same people who before you replace the ledger books you must have a facility to keep a backup computer data in a bank vault.
I have never heard of a company that locked a duplicate set of ledger books in a bank vault every night.
Sorry for the rant..

 

[PNGBill]

Text() will convert the 6 digit number to text.
MID() should extract the characters
Value() then allows you to sum the 6 fields.

I did think of using the 5 possible 2 digit occurrences but then the sum may not give you a result.

 

[Khalid_Afridi]

You are right bill!
but we always think that we should do everything by programing and coding. There are lot of way to handle things in a simple way.

I like your idea of transpose calculating, we can do that in excel quiet easily, copy all the invoice data from access to excel sheet and transpose it in columns:

for example:
123456 1 2 3 4 5 6 = 21
123457 1 2 3 4 5 7 = 22
123458 1 2 3 4 5 8 = 23
123459 1 2 3 4 5 9 = 24
123460 1 2 3 4 6 0 = 16

and find the duplicate, how's that????

 

[PNGBill]

So many ways to do something. Of course to use excel would also require additional knowledge to get it all working automatically.

What about the possible correct alternatives to 123456 that will show up?
Just assuming 123 stays current for some time then you still have 999 correct options of the last three digits.

Only real way to handle this is to restrict the input field to the invoices already raised and maybe also force the input of the customer ID and ensure they match.

Always some fool that will find a way around the system

Our ever quest for speed of data input conflicts with preventing errors.

 

[raskew]

Hi -

This little function will add up your individual digits:

Public Function AddStr(x As Variant) As Integer
Dim n As Integer
Dim t As Integer

   For n = 1 To Len(x)
      If IsNumeric(Mid(x, n, 1)) Then
         t = t + Mid(x, n, 1)
      End If
   Next
   AddStr = t

End Function

From a query, you could call it like this (I used SSN as the subject field)

SELECT tblClients1.SSN, [COLOR="Red"]AddStr([ssn])[/COLOR] AS Expr1
FROM tblClients1
ORDER BY AddStr([ssn]), tblClients1.SSN;

HTH - Bob

 

[Khalid_Afridi]

raskew thumbs up!

This little function made Neilbees's life easy
Neilbees! tell your coo-workers not to cheat next time and make all the invoices clear.

Good Luck.

 

[Brianwarnock]

I don't get it, how is this going to work?
assume that the current number is 123456 then 123465 will at some point be correct as will 123546 and 123564 but they all add up to the same. surely what is needed is an automatically assigned rising number to avoid duplicates. This has been answered before on the forums.

Brian

 

[Khalid_Afridi]

I don't get it, how is this going to work?
assume that the current number is 123456 then 123465 will at some point be correct as will 123546 and 123564 but they all add up to the same. surely what is needed is an automatically assigned rising number to avoid duplicates. This has been answered before on the forums.

Brian

Brain!
Neilbees has already have some invoice data which he doubts is wrong entries. so he wants to catch those entries. The function will calculate the digits and if there any duplicates found, he can further investigate through out some other office records I believe, any how.

 

[Brianwarnock]

If he currently has transposed data then he will have
a) Duplicates
and/or
b) Gaps in his number range

They are what he should be checking surely.


Brian

 

[Neilbees]

Hi Guys, thanks for all the replies - superb stuff!

I agree with everyone that this is a weird request and that a proper ascending invoice number series would have ensured that this never happened. Part of the problem is that the invoice numbers are completely random - not in series at all! Brian's point about my potential duplicate numbers actually being proper invoice numbers was what was really driving me mad.

I should have explained the situation a bit more. Essentially there are a load of spreadsheets that need to be loaded into Access to find potential duplicates. There are duplicate searches needed like same invoice number/same date or same amount/same date but then also some odd ones like the one on this thread!

In any case, I reckon this function should do the trick. Thanks again!

 

[gemma-the-husky]

how can you possibly do this?

lets say your query finds 20 duplicates, one of which is indeed 123456.

now there are 15 candidates for swap (6*5/2). Do you mean you want to examine each of the potential transpositions, and see if the the transposition is not on file. Or do you just mean to check contiguous digits, in which case there are not so many.

Personally, I would just manually edit these in some way, to remove the duplication, and investigate later - and then put a key on the fiel to prevent duplicates in future!

Just How many duplicates are there?

-------------
note that when dealing with money, a transposition ALWAYS results in a number divisible by 9. so in the old days if you were checking a non-balancing total, which disagreed by say £45 - you immediately looked for a transpositon that would produce this error eg 83 as 38 - to avoid having to check every item.

But I dont think this problem is susceptible to the same analysis.

 

[pascaly]

Sorry, I have nothing to add for the OP, but Dave, that is a fascinating bit of info there (regarding being divisible by 9). I just had to say something!

 

[gemma-the-husky]

well if you have a number that 10x + y (or even 100x + y)

and transpose it as 10Y + x

the difference between the 2 numbers is 9x - 9y or 9 (x-y)

which is therefore divisible by 9


---------------
on a similar basis,
any number where the digits add to a number divisible by 9, is divisble by 9
any number where the digits add to a number divisible by 3, is divisble by 3
any number where the digits add to a number divisible by 3 and is even, is divisble by 6