Search for # with most occurances 1

[webware]

I need to create a search that will list a 3 digit # that is listed more than once. The catch is if the # found in the table was 302; I would also need the search to recognize that 203, 320, 230, 032, and 023 are the same as 302. So the results should show 6 occurances for that #. I hope I am making sense here. Sorry if I'm not. I am a web designer not a programmer. I have tried using COUNT and LIKE in a query but does not give me the results I am looking for...thanks for any help

 

[johncb]

This may help you to start:

assume your # is 302 in a numeric variable named intNumber

'create an array
DIM intNumbers(6) as integer
txtNumber = str(intNumber) ' convert to a string
intNumber(1) = txtNumber '302
intNumber(2) = left(txtNumber,1) & right(txtNumber,1) & mid(txtNumber,2,1) '320
intNumber(3) = mid(txtNumber,2,1) & left(txtNumber,1) & right(txtNumber,1) '032
intNumber(4) = mid(txtNumber,2,1) & right(txtNumber,1) & left(txtNumber,1) '023
intNumber(5) = right(txtNumber,1) & mid(txtNumber,2,1) & left(txtNumber,1) '203
intNumber(6) = right(txtNumber,1) & left(txtNumber,1) & mid(txtNumber,2,1) '230

You can then use those 6 variables to search your table for matching records.
I haven't tested this code so it may need some debugging.

John

 

[MichaelRed]

i'm just curious. what reason (ouside of 'intellectual' curosity) would there be for such a determiniation? i've looked at this at least three times, and cannout 'compute' a 'real world' application for the information.

It is (should be) a minor variation on the textbook exercise of doing a distribition frequency, except the weird wrinkle of the "de-normalized" triad.

To ACTUALLY do it in (MOSTLY) SQL, i would probably generate the calculated field with the "orderd" triad )or whatever STANDARD length), and then the standard frequency distribution on the calculated field.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over