formula help

[sam]

I would like to know that how can I get records with 3 or
more same numbers from a table.
for eg
1 3 4 6 8 9 10
2 3 4 6 8 9 10

two records with the same numbers
can anyone help me with this
Thank you

 

[John Vinson]

I would like to know that how can I get records with 3 or
more same numbers from a table.
for eg
1 3 4 6 8 9 10
2 3 4 6 8 9 10

two records with the same numbers
can anyone help me with this
Thank you

You'll have to explain a bit more.

What's the structure of the table? Are these multiple fields
(non-normalized) values going across? What combinations of values
would be "3 or more numbers the same" - neither row above has any two
numbers the same, so I presume you're comparing one record with
another record?

 

[sam]

Thanks for your reply sir
I have a table with the field name phone numbers
it looks like this
phone num
0408212630
0205101833
0609113234
1415162531
0308212630
above you can see that record no 1 and record 5 have 4
same numbers(08 21 26 30)
the output required is
0408212630
0308212630

there are thousands of records and there are many records
with the same numbers. Some have first 4 nos same, last
nos same, middle nos same, first and last 3 nos same etc.
What I would like to know is that how can I get these
records with the same 3 or more numbers reappearing in
other records in the table and there is only one filed in
the table.
I would really appreciate for your help.
thank you.

 

[John Vinson]

there are thousands of records and there are many records
with the same numbers. Some have first 4 nos same, last
nos same, middle nos same, first and last 3 nos same etc.
What I would like to know is that how can I get these
records with the same 3 or more numbers reappearing in
other records in the table and there is only one filed in
the table.

Ow. That's going to be REALLY slow, since you are trying to link on
portions of fields rather than on the field itself.

Here's a speculation: not guaranteed but should get you started.
Create a Query by adding your table *twice*, with no join line. Access
will alias the second table by adding _1 to the name. Select the Phone
Num field from both tables; on table_1.[Phone Num]'s criteria line put

<>

.[Phone Number]

Then put into a vacant Field cell:

Matches: Abs(
(Mid(

.[Phone Num], 1, 2) = Mid([table_1].[Phone Num], 1, 2))
+
(Mid(

.[Phone Num], 3, 2) = Mid([table_1].[Phone Num], 3, 2))
+
(Mid(

.[Phone Num], 5, 2) = Mid([table_1].[Phone Num], 5, 2))
+
(Mid(

.[Phone Num], 7, 2) = Mid([table_1].[Phone Num], 7, 2))
+
(Mid(

.[Phone Num], 9, 2) = Mid([table_1].[Phone Num], 9, 2))
)

and use a criterion of

= 3

Start this query running and go do something else for a while...