querying numbers

[Guest]

I want to query a database of numbers and retrieve a set of numbers that are
the same but in different order for example 2534 and 5234 and 3452. what
should I type in the criteria to do this?

 

[Sylvain Lafontaine]

Add a column (or use a VBA function) that will contains (or return) the
absolute anagram of each number then make Group By query on this column and
add an Having Count (*) > 1 statement.

The absolute anagram of a number is simply the anagram of each number where
each digit have been sorted; for example 2534 --> 2345.

S. L.

 

[Guest]

Can you simplify your explanation? I'm not quite tracking

 

[Sylvain Lafontaine]

Suppose you have this:

- N -
2534
9241
5234
3452
9876

Add a column to have this:

- N - - A -
2534 2345
9241 1249
5234 2345
3452 2345
9876 6789

You can see that your three number 2534, 5234 and 3452 now have the same
value 2345 in the second colonne. All you have to do is to make a Group By
on this second column and add the condition Having Count (*) > 1 to find
them:

Select N from Table where A in (Select A from Table Group By A Having Count
(*) > 1)

I leave you the transformation 2534 --> 2345 as an exercice. The most
easiest way would be to convert the number into a character string and then
sort this stream of caracters in ascending order. You can also replace the
second column with a VBA function.

S. L.