finding a record with numeric digits in any order

[Guest]

hi. i'd like to know how to do a find for a record with a number combination,
example: "123" which could be in any order in a column.

so i'd be finding any record in a column containing: 123, 132, 213, 231,
312, or 321 without having to enter each different combination.

yep, this is for a lottery database!
THANKS for any help!! (^_^)

 

[MGFoster]

hi. i'd like to know how to do a find for a record with a number combination,
example: "123" which could be in any order in a column.

so i'd be finding any record in a column containing: 123, 132, 213, 231,
312, or 321 without having to enter each different combination.

yep, this is for a lottery database!
THANKS for any help!! (^_^)

You're talking about permutations. See this site for some formulas:

http://www.duncanwil.co.uk/permcom.html

 

[Andreas]

Top of the head, you could try something like:

Like "[123][123][123]"

Regards,
Andreas

 

[Andreas]

Just thought about this.
Probably need to ensure there are no duplicates?

Again, top of the head (which is very tired and looking for a pillow):

Like "[123][123][123]" AND Left(FieldName,1) <> Mid(FieldName,2,1) And
Left(FieldName,1) <> Right(FieldName,1) AND Mid(FieldName,2,1) <>
Right(FieldName,1)

Possibly easier to just extract the numbers into seperate columns first.
That way there would be less function calls.

Regards,
Andreas

Top of the head, you could try something like:

Like "[123][123][123]"

Regards,
Andreas

You're talking about permutations. See this site for some formulas:

http://www.duncanwil.co.uk/permcom.html

 

[Guest]

jeez, isn't there a way to just click on the "binoculars" (find), type in
"123" with & or % or * or something, and have Access find the combination
in any order that way?

Access seems to be very limited when it comes to this sort of thing.

THANKS! (^_^)

Just thought about this.
Probably need to ensure there are no duplicates?

Again, top of the head (which is very tired and looking for a pillow):

Like "[123][123][123]" AND Left(FieldName,1) <> Mid(FieldName,2,1) And
Left(FieldName,1) <> Right(FieldName,1) AND Mid(FieldName,2,1) <>
Right(FieldName,1)

Possibly easier to just extract the numbers into seperate columns first.
That way there would be less function calls.

Regards,
Andreas

Top of the head, you could try something like:

Like "[123][123][123]"

Regards,
Andreas

voxorganum wrote:

hi. i'd like to know how to do a find for a record with a number
combination, example: "123" which could be in any order in a column.

so i'd be finding any record in a column containing: 123, 132, 213,
231, 312, or 321 without having to enter each different combination.

yep, this is for a lottery database!
THANKS for any help!! (^_^)



You're talking about permutations. See this site for some formulas:

http://www.duncanwil.co.uk/permcom.html

 

[John Spencer (MVP)]

Well, yes. On the other hand, name the program that can do what you want the
way you want.

jeez, isn't there a way to just click on the "binoculars" (find), type in
"123" with & or % or * or something, and have Access find the combination
in any order that way?

Access seems to be very limited when it comes to this sort of thing.

THANKS! (^_^)

Just thought about this.
Probably need to ensure there are no duplicates?

Again, top of the head (which is very tired and looking for a pillow):

Like "[123][123][123]" AND Left(FieldName,1) <> Mid(FieldName,2,1) And
Left(FieldName,1) <> Right(FieldName,1) AND Mid(FieldName,2,1) <>
Right(FieldName,1)

Possibly easier to just extract the numbers into seperate columns first.
That way there would be less function calls.

Regards,
Andreas

Top of the head, you could try something like:

Like "[123][123][123]"

Regards,
Andreas


MGFoster wrote:

voxorganum wrote:

hi. i'd like to know how to do a find for a record with a number
combination, example: "123" which could be in any order in a column.

so i'd be finding any record in a column containing: 123, 132, 213,
231, 312, or 321 without having to enter each different combination.

yep, this is for a lottery database!
THANKS for any help!! (^_^)



You're talking about permutations. See this site for some formulas:

http://www.duncanwil.co.uk/permcom.html

 

[Andreas]

My thoughts exactly!

However, if this is important enough, you can set it up yourself to do
just that.

Create your own form which:
- allows entering of the characters you want to find in any order
- has a button to start the process
- uses code to create all possible combinations (mind bender and don't
try to do this with more than 11 digits unless you got a really grunty PC)
- uses the return of the above to create a SQL string or a temporary
table for use with a query
- displays the result in a form

It can be done, it's just a question of how badly you want it!

Regards,
Andreas

Well, yes. On the other hand, name the program that can do what you want the
way you want.

jeez, isn't there a way to just click on the "binoculars" (find), type in
"123" with & or % or * or something, and have Access find the combination
in any order that way?

Access seems to be very limited when it comes to this sort of thing.

THANKS! (^_^)

:

Just thought about this.
Probably need to ensure there are no duplicates?

Again, top of the head (which is very tired and looking for a pillow):

Like "[123][123][123]" AND Left(FieldName,1) <> Mid(FieldName,2,1) And
Left(FieldName,1) <> Right(FieldName,1) AND Mid(FieldName,2,1) <>
Right(FieldName,1)

Possibly easier to just extract the numbers into seperate columns first.
That way there would be less function calls.

Regards,
Andreas


Andreas wrote:

Top of the head, you could try something like:

Like "[123][123][123]"

Regards,
Andreas


MGFoster wrote:


voxorganum wrote:


hi. i'd like to know how to do a find for a record with a number
combination, example: "123" which could be in any order in a column.

so i'd be finding any record in a column containing: 123, 132, 213,
231, 312, or 321 without having to enter each different combination.

yep, this is for a lottery database!
THANKS for any help!! (^_^)



You're talking about permutations. See this site for some formulas:

http://www.duncanwil.co.uk/permcom.html

 

[Guest]

thanks for your patience and help. i'll try to explain as best i can.

first, this is about "Cash 3" of the Florida lottery: www.flalottery.com

i'm just trying to find draw-history patterns of the 3-number combinations
which can have same digits and can be any combination/order, 0-9.

so i was hoping to do "look-backs" in the history of the drawings to find
3-number combination frequencies without regard to their order as they're
drawn.

so i need to be able to "look-back" through the draw dates and find
occurences of 3-number combinations.

for example: recently, 479 was drawn. i want to look back and see when the
last time it was that 479 was drawn WITHOUT regard to the order that 479 was
drawn the last time it was drawn. it could be 974 or 497 or whichever.

i wish there was some way i could just click on the binoculars on the menu
above my table/draw-history, and make some instruction for the "FIND" along
with "479" and have Access return the last date or all the dates that 479 was
drawn WITHOUT regard to the order those three numbers were drawn.

i hope this makes things easier to understand. THANKS for any help!! (^_^)

My thoughts exactly!

However, if this is important enough, you can set it up yourself to do
just that.

Create your own form which:
- allows entering of the characters you want to find in any order
- has a button to start the process
- uses code to create all possible combinations (mind bender and don't
try to do this with more than 11 digits unless you got a really grunty PC)
- uses the return of the above to create a SQL string or a temporary
table for use with a query
- displays the result in a form

It can be done, it's just a question of how badly you want it!

Regards,
Andreas

Well, yes. On the other hand, name the program that can do what you want the
way you want.

jeez, isn't there a way to just click on the "binoculars" (find), type in
"123" with & or % or * or something, and have Access find the combination
in any order that way?

Access seems to be very limited when it comes to this sort of thing.

THANKS! (^_^)

:


Just thought about this.
Probably need to ensure there are no duplicates?

Again, top of the head (which is very tired and looking for a pillow):

Like "[123][123][123]" AND Left(FieldName,1) <> Mid(FieldName,2,1) And
Left(FieldName,1) <> Right(FieldName,1) AND Mid(FieldName,2,1) <>
Right(FieldName,1)

Possibly easier to just extract the numbers into seperate columns first.
That way there would be less function calls.

Regards,
Andreas


Andreas wrote:

Top of the head, you could try something like:

Like "[123][123][123]"

Regards,
Andreas


MGFoster wrote:


voxorganum wrote:


hi. i'd like to know how to do a find for a record with a number
combination, example: "123" which could be in any order in a column.

so i'd be finding any record in a column containing: 123, 132, 213,
231, 312, or 321 without having to enter each different combination.

yep, this is for a lottery database!
THANKS for any help!! (^_^)



You're talking about permutations. See this site for some formulas:

http://www.duncanwil.co.uk/permcom.html

 

[Andreas]

If it is a fixed number of numbers situation and there are only 3
numbers then:
1)
- Create a new query and go to the SQL view
- Delete everything that may be showing
- Paste the following statement
(substitute your table name for

, substitute your field name
containing the draw result for [Field], substitute the field name
containing the draw date for [Date]):

SELECT * FROM

WHERE [Field] = (Forms!frmFind!txtN1 &
Forms!frmFind!txtN2 & Forms!frmFind!txtN3) OR (Forms!frmFind!txtN1 &
Forms!frmFind!txtN3 & Forms!frmFind!txtN2) OR (Forms!frmFind!txtN2 &
Forms!frmFind!txtN1 & Forms!frmFind!txtN3) OR (Forms!frmFind!txtN2 &
Forms!frmFind!txtN3 & Forms!frmFind!txtN1) OR (Forms!frmFind!txtN3 &
Forms!frmFind!txtN1 & Forms!frmFind!txtN2) OR (Forms!frmFind!txtN3 &
Forms!frmFind!txtN2 & Forms!frmFind!txtN1) ORDER BY [Date] DESC;

2)
- Create a form
- Place 3 texboxes on it
- Name them as txtN1, txtN2, txtN3
- Place a command button the form that runs your query (you can use the
wizard for that)

You are ready to go.
Type each individual number in the individual textboxes and press the
button.

Regards,
Andreas

thanks for your patience and help. i'll try to explain as best i can.

first, this is about "Cash 3" of the Florida lottery: www.flalottery.com

i'm just trying to find draw-history patterns of the 3-number combinations
which can have same digits and can be any combination/order, 0-9.

so i was hoping to do "look-backs" in the history of the drawings to find
3-number combination frequencies without regard to their order as they're
drawn.

so i need to be able to "look-back" through the draw dates and find
occurences of 3-number combinations.

for example: recently, 479 was drawn. i want to look back and see when the
last time it was that 479 was drawn WITHOUT regard to the order that 479 was
drawn the last time it was drawn. it could be 974 or 497 or whichever.

i wish there was some way i could just click on the binoculars on the menu
above my table/draw-history, and make some instruction for the "FIND" along
with "479" and have Access return the last date or all the dates that 479 was
drawn WITHOUT regard to the order those three numbers were drawn.

i hope this makes things easier to understand. THANKS for any help!! (^_^)

:

My thoughts exactly!

However, if this is important enough, you can set it up yourself to do
just that.

Create your own form which:
- allows entering of the characters you want to find in any order
- has a button to start the process
- uses code to create all possible combinations (mind bender and don't
try to do this with more than 11 digits unless you got a really grunty PC)
- uses the return of the above to create a SQL string or a temporary
table for use with a query
- displays the result in a form

It can be done, it's just a question of how badly you want it!

Regards,
Andreas

Well, yes. On the other hand, name the program that can do what you want the
way you want.

voxorganum wrote:


jeez, isn't there a way to just click on the "binoculars" (find), type in
"123" with & or % or * or something, and have Access find the combination
in any order that way?

Access seems to be very limited when it comes to this sort of thing.

THANKS! (^_^)

:



Just thought about this.
Probably need to ensure there are no duplicates?

Again, top of the head (which is very tired and looking for a pillow):

Like "[123][123][123]" AND Left(FieldName,1) <> Mid(FieldName,2,1) And
Left(FieldName,1) <> Right(FieldName,1) AND Mid(FieldName,2,1) <>
Right(FieldName,1)

Possibly easier to just extract the numbers into seperate columns first.
That way there would be less function calls.

Regards,
Andreas


Andreas wrote:


Top of the head, you could try something like:

Like "[123][123][123]"

Regards,
Andreas


MGFoster wrote:



voxorganum wrote:



hi. i'd like to know how to do a find for a record with a number
combination, example: "123" which could be in any order in a column.

so i'd be finding any record in a column containing: 123, 132, 213,
231, 312, or 321 without having to enter each different combination.

yep, this is for a lottery database!
THANKS for any help!! (^_^)



You're talking about permutations. See this site for some formulas:

http://www.duncanwil.co.uk/permcom.html