quering combinations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I do a query and retrieve numbers from a table in any order? Example,
input 2345 and retrieve 2435, 5234, and 4352 other combinations not
available.
 
Hi,


You mean digits of a number re-arranged in any manner? No.... but maybe yes

A TABLE is a concept where HORIZONTALLY, the ordering matter and where
VERTICALLY, it DOES NOT.

If you need 2345 with digits re-arranged in any manner, the DIGITS must be
available, VERTICALLY.


Rather than

Who Code
JoeBlow 2345
MaryBlow 4325



Try


Who Code
JoeBlow 2
JoeBlow 3
JowBlow 4
JoeBlow 5
MaryBlow 4
MaryBlow 3
MaryBlow 2
MaryBlow 5
PeterBlow 5
PeterBlow 2
PeterBlow 3

Then, you can get who ever has 2, 3 and 4 with:

SELECT Who
FROM myNewTable
WHERE Code IN(2, 3, 4)
GROUP BY Who
HAVING COUNT(*)=3


(Note that Peter not having 4 won't be picked up).


Since vertically, it does not matter if MARY has (4, 3, 2, 5); or (2, 3,
4, 5), or whatever. The vertical disposition does not matter. The horizontal
disposition does: "Who" cannot be exchanged for "Code", their meaning is
different. But vertically, being the "first" record, or being the last one,
it is irrelevant.




Hoping it may help,
Vanderghast, Access MVP
 
Assuming you input the number in a textbox named MyInputNumber on a from
named MyForm,

Put this expression in the first criteria row:
Like Mid(Forms!MyForm!MyInputNumber,1,1) & "*"
Put this expression in the second criteria row:
Like "*" & Mid(Forms!MyForm!MyInputNumber,2,1) & "*"
Put this expression in the third criteria row:
Like "*" & Mid(Forms!MyForm!MyInputNumber,3,1) & "*"
Put this expression in the fourth criteria row:
Like "*" & Mid(Forms!MyForm!MyInputNumber,4,1)
 
Dear Natty:

I would like to suggest you write a function that accepts two strings. One
I'll call the "target string" should contain the "pattern" for which you are
searching. The other should contain the "subject string" from the table.
This function should return a boolean (true/false) that signifies whether
the subject string matches the combination from the pattern.

Before writing the function, you must determine whether a match occurs under
certain conditions. Must the subject string contain only characters in the
target? Must the subject string contail all the characters in the target?
May the subject string repeat characters in the target only the exact number
of times they are repeated in the target?

The above questions are exampled below, in the order I asked them:

target subject
12 123
123 12
11 1
1 11

The last two are different ways of looking at my last question.

After you are certain of your answers to these questions, there may be
alternate ways of performing the test.

Are there any characters to be ignored? Is a space in the subject or target
to be considered in the matching? Or perhaps both are always digits only.

If none of the above cases is considered a match, then this suggests that
the two strings must be of the exact same length. Then, sorting the string
by moving the characters in ascending order, the two strings must then
exactly match. Is that what you're after?

Tom Ellison
Microsoft Access MVP
 
Alternative might be to have the function construct the criteria string. For
instance if looking for 2335 or variations of it, you could use

Like "[2335][2335][2335][2335]"

Using a VBA function to build that string would fairly simple. UNTESTED AIRCODE follows.

Public Function fBuildSearch(strIn) as String
Dim i as Integer, strOut as String

If IsNull(StrIn) then
fBuildSearch = vbnullstring
Else
For i = 1 to Len(strIN)
strOut = StrOut & "[" & Mid(StrIn,i,1) & "]"
Next i

fBuildSearch = strOut

End if

End Function

So the criteria in the query would be

Where NumberField & "" Like fBuildSearch("2335")

Of course, that all assumes that the field is a string. More complex would be
searching a number field for number values or optimizing this search for speed
using indexes.
 
Sure, John. I'd point out there's no need to repeat a member of the set.
[235] is the same as [2335] isn't it?

This ignores whether a member can be repeated in the target string without
being repeated in the source string. This question is, as yet, unanswered.

As long as the question remains ambiguous, we cannot do any more with it.

Tom Ellison
Microsoft Access MVP


John Spencer (MVP) said:
Alternative might be to have the function construct the criteria string.
For
instance if looking for 2335 or variations of it, you could use

Like "[2335][2335][2335][2335]"

Using a VBA function to build that string would fairly simple. UNTESTED
AIRCODE follows.

Public Function fBuildSearch(strIn) as String
Dim i as Integer, strOut as String

If IsNull(StrIn) then
fBuildSearch = vbnullstring
Else
For i = 1 to Len(strIN)
strOut = StrOut & "[" & Mid(StrIn,i,1) & "]"
Next i

fBuildSearch = strOut

End if

End Function

So the criteria in the query would be

Where NumberField & "" Like fBuildSearch("2335")

Of course, that all assumes that the field is a string. More complex
would be
searching a number field for number values or optimizing this search for
speed
using indexes.

Tom said:
Dear Natty:

I would like to suggest you write a function that accepts two strings.
One
I'll call the "target string" should contain the "pattern" for which you
are
searching. The other should contain the "subject string" from the table.
This function should return a boolean (true/false) that signifies whether
the subject string matches the combination from the pattern.

Before writing the function, you must determine whether a match occurs
under
certain conditions. Must the subject string contain only characters in
the
target? Must the subject string contail all the characters in the
target?
May the subject string repeat characters in the target only the exact
number
of times they are repeated in the target?

The above questions are exampled below, in the order I asked them:

target subject
12 123
123 12
11 1
1 11

The last two are different ways of looking at my last question.

After you are certain of your answers to these questions, there may be
alternate ways of performing the test.

Are there any characters to be ignored? Is a space in the subject or
target
to be considered in the matching? Or perhaps both are always digits
only.

If none of the above cases is considered a match, then this suggests that
the two strings must be of the exact same length. Then, sorting the
string
by moving the characters in ascending order, the two strings must then
exactly match. Is that what you're after?

Tom Ellison
Microsoft Access MVP
Click to expand...
Click to expand...
 
Yes, the set is basically the same. However, since the set may be 2345 or 5139
or ... I just decided to be lazy in writing the vba function and not bother
checking for duplicates.



Tom said:
Sure, John. I'd point out there's no need to repeat a member of the set.
[235] is the same as [2335] isn't it?

This ignores whether a member can be repeated in the target string without
being repeated in the source string. This question is, as yet, unanswered.

As long as the question remains ambiguous, we cannot do any more with it.

Tom Ellison
Microsoft Access MVP

John Spencer (MVP) said:
Alternative might be to have the function construct the criteria string.
For
instance if looking for 2335 or variations of it, you could use

Like "[2335][2335][2335][2335]"

Using a VBA function to build that string would fairly simple. UNTESTED
AIRCODE follows.

Public Function fBuildSearch(strIn) as String
Dim i as Integer, strOut as String

If IsNull(StrIn) then
fBuildSearch = vbnullstring
Else
For i = 1 to Len(strIN)
strOut = StrOut & "[" & Mid(StrIn,i,1) & "]"
Next i

fBuildSearch = strOut

End if

End Function

So the criteria in the query would be

Where NumberField & "" Like fBuildSearch("2335")

Of course, that all assumes that the field is a string. More complex
would be
searching a number field for number values or optimizing this search for
speed
using indexes.

Tom said:
Dear Natty:

I would like to suggest you write a function that accepts two strings.
One
I'll call the "target string" should contain the "pattern" for which you
are
searching. The other should contain the "subject string" from the table.
This function should return a boolean (true/false) that signifies whether
the subject string matches the combination from the pattern.

Before writing the function, you must determine whether a match occurs
under
certain conditions. Must the subject string contain only characters in
the
target? Must the subject string contail all the characters in the
target?
May the subject string repeat characters in the target only the exact
number
of times they are repeated in the target?

The above questions are exampled below, in the order I asked them:

target subject
12 123
123 12
11 1
1 11

The last two are different ways of looking at my last question.

After you are certain of your answers to these questions, there may be
alternate ways of performing the test.

Are there any characters to be ignored? Is a space in the subject or
target
to be considered in the matching? Or perhaps both are always digits
only.

If none of the above cases is considered a match, then this suggests that
the two strings must be of the exact same length. Then, sorting the
string
by moving the characters in ascending order, the two strings must then
exactly match. Is that what you're after?

Tom Ellison
Microsoft Access MVP

How can I do a query and retrieve numbers from a table in any order?
Example,
input 2345 and retrieve 2435, 5234, and 4352 other combinations not
available.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top