Creating Random Sequential Numbers...

[Guest]

I am in a situation where I need to create 5 random sequential numbers from a
base of 10. That works out to be 252 possible combinations.

In the past I've just had Access pick 5 unique numbers and then sorted the
individual picks sequentially. I'd like to get a little more sophisticated
and have the system pick a number between 1 and 252 and then calculate what
the sequential number would be.

I am relatively certain that it is possible, but am having trouble working
out the mathematical formula for doing so. I can do it by counting how many
times the last digit turns over, but this process would be extremely slow
when working with bigger numbers.

Any suggestions?

 

[Guest]

Here is an example of what I am talking about. I want to be able to pick a
number randomly for PID and then have it calculate what P1-P4 are without
having to use a reference table. So if the system were to pick number 11 it
would translate that into 1,3,4,5.

PID P1 P2 P3 P4
1 1 2 3 4
2 1 2 3 5
3 1 2 3 6
4 1 2 3 7
5 1 2 4 5
6 1 2 4 6
7 1 2 4 7
8 1 2 5 6
9 1 2 5 7
10 1 2 6 7
11 1 3 4 5
12 1 3 4 6
13 1 3 4 7
14 1 3 5 6
15 1 3 5 7
16 1 3 6 7
17 1 4 5 6
18 1 4 5 7
19 1 4 6 7
20 1 5 6 7
21 2 3 4 5
22 2 3 4 6
23 2 3 4 7
24 2 3 5 6
25 2 3 5 7
26 2 3 6 7
27 2 4 5 6
28 2 4 5 7
29 2 4 6 7
30 2 5 6 7
31 3 4 5 6
32 3 4 5 7
33 3 4 6 7
34 3 5 6 7
35 4 5 6 7

 

[James A. Fortune]

Here is an example of what I am talking about. I want to be able to pick a
number randomly for PID and then have it calculate what P1-P4 are without
having to use a reference table. So if the system were to pick number 11 it
would translate that into 1,3,4,5.

PID P1 P2 P3 P4
1 1 2 3 4
2 1 2 3 5
3 1 2 3 6
4 1 2 3 7
5 1 2 4 5
6 1 2 4 6
7 1 2 4 7
8 1 2 5 6
9 1 2 5 7
10 1 2 6 7
11 1 3 4 5
12 1 3 4 6
13 1 3 4 7
14 1 3 5 6
15 1 3 5 7
16 1 3 6 7
17 1 4 5 6
18 1 4 5 7
19 1 4 6 7
20 1 5 6 7
21 2 3 4 5
22 2 3 4 6
23 2 3 4 7
24 2 3 5 6
25 2 3 5 7
26 2 3 6 7
27 2 4 5 6
28 2 4 5 7
29 2 4 6 7
30 2 5 6 7
31 3 4 5 6
32 3 4 5 7
33 3 4 6 7
34 3 5 6 7
35 4 5 6 7


:

Problem Statement:

Given a set of monotonically increasing integers: I1, ..., In

Come up with a way to list all the distinct ordered m-tuples (a kind of
poset) in a meaningfully sorted way.

tblValues
VID theValue
1 1
2 2
3 3
4 4
5 5
6 6
7 7

n = RecordCount = 7

m = 4 for your example (four values listed: P1 to P4)

Count up in Base n - m + 1 (7 - 4 + 1 = 4) except where a digit column
goes from a higher value to a lower value.

For your example, generate all Base 4 values such that P1 <= P2 <= P3 <= P4

There are 35 Base 4 values that meet this condition.

Order the remaining records

Auxiliary Table of Integers:
tblIntegers
ID AutoNumber
theInt Integer
ID theInt
1 1
2 2
....
256 256

Here 256 (4 ^ 4) is chosen because 4 ^ 4 - 1 is the highest Base 4
number with four digits. The -1 in the SQL expression was used so that
tblIntegers will start with 1 for consistency with other places where I
use an auxiliary table.

Base4 is a public string function that returns the digits of the Base 4
representation of the input using a well-known technique. Since I only
needed four digits I used:

Public Function Base4(intN As Integer) As String
Dim I(3) As Integer
Dim intTemp As Integer
Dim intI As Integer

intTemp = intN
For intI = 0 To 3
I(intI) = intTemp - Int(intTemp / 4) * 4
intTemp = Int(intTemp / 4)
Next intI
Base4 = CStr(I(3)) & CStr(I(2)) & CStr(I(1)) & CStr(I(0))
End Function

qryPIs:
SELECT theInt-1 AS theIndex, Base4(theIndex) AS Base4, 1 +
Mid([Base4],1,1) AS P1, 2 + Mid([Base4],2,1) AS P2, 3 + Mid([Base4],3,1)
AS P3, 4 + Mid([Base4],4,1) AS P4
FROM tblIntegers
WHERE tblIntegers.theInt<=256 AND
Val(Mid(Base4([theInt]-1),1,1))<=Val(Mid(Base4([theInt]-1),2,1)) AND
Val(Mid(Base4([theInt]-1),2,1))<=Val(Mid(Base4([theInt]-1),3,1)) AND
Val(Mid(Base4([theInt]-1),3,1))<=Val(Mid(Base4([theInt]-1),4,1));

!qryPIs:
theIndex Base4 P1 P2 P3 P4
0 0000 1 2 3 4
1 0001 1 2 3 5
2 0002 1 2 3 6
3 0003 1 2 3 7
5 0011 1 2 4 5
6 0012 1 2 4 6
7 0013 1 2 4 7
10 0022 1 2 5 6
11 0023 1 2 5 7
15 0033 1 2 6 7
21 0111 1 3 4 5
22 0112 1 3 4 6
23 0113 1 3 4 7
26 0122 1 3 5 6
27 0123 1 3 5 7
31 0133 1 3 6 7
42 0222 1 4 5 6
43 0223 1 4 5 7
47 0233 1 4 6 7
63 0333 1 5 6 7
85 1111 2 3 4 5
86 1112 2 3 4 6
87 1113 2 3 4 7
90 1122 2 3 5 6
91 1123 2 3 5 7
95 1133 2 3 6 7
106 1222 2 4 5 6
107 1223 2 4 5 7
111 1233 2 4 6 7
127 1333 2 5 6 7
170 2222 3 4 5 6
171 2223 3 4 5 7
175 2233 3 4 6 7
191 2333 3 5 6 7
255 3333 4 5 6 7

qryPIDs:
SELECT (SELECT Count(*) FROM qryPIs AS A WHERE A.theIndex <
qryPIs.theIndex) + 1 AS PID, (SELECT A.theValue FROM tblValues AS A
WHERE VID = qryPIs.P1) AS P1, (SELECT A.theValue FROM tblValues AS A
WHERE VID = qryPIs.P2) AS P2, (SELECT A.theValue FROM tblValues AS A
WHERE VID = qryPIs.P3) AS P3, (SELECT A.theValue FROM tblValues AS A
WHERE VID = qryPIs.P4) AS P4
FROM qryPIs;

!qryPIDs:
PID P1 P2 P3 P4
1 1 2 3 4
2 1 2 3 5
3 1 2 3 6
....
35 4 5 6 7

Summing P1 to P4 could be used to help solve one of the puzzles I see in
the newspapers where m squares filled with distinct digits in a row or
column must add to prescribed values.

A generalized Base4 function might look like (untested):

Public Function BaseB(lngN As Long, b As Integer, mPlaces As Integer) As
String
Dim I() As Integer
Dim lngTemp As Long
Dim intI As Integer
Dim strTemp As String

ReDim I(mPlaces)
lngTemp = lngN
For intI = 0 To mPlaces - 1
I(intI) = lngTemp - Int(lngTemp / b) * b
lngTemp = Int(lngTemp / b)
Next intI
strTemp = ""
For intI = mPlaces - 1 To 0 Step -1
strTemp = strTemp & CStr(I(intI))
Next intI
BaseB = strTemp
End Function

For n = 10 and m = 5, Base 6 => 6 ^ 5 = 7776 won't even blow up an
Integer variable. You can populate tblIntegers with 7776 values using
code. Something like (Dim's omitted):

Set MyDB = CurrentDb
strSQL = "SELECT * FROM tblIntegers;"
Set FillRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
For I = 1 To 7776
FillRS.AddNew
FillRS("theInt") = I
FillRS.Update
Next I
FillRS.Close
Set FillRS = Nothing
Set MyDB = Nothing
MsgBox ("Done.")

So, you'll need Base b = 6, mPlaces = 5, 7776 instead of 256 and P1
through P5. Hopefully this will help you solve your "puzzle."

James A. Fortune
(e-mail address removed)

 

[Guest]

Thank you, very helpful and you have remminded me that I should have paid
more attention in math class 20 years ago.

I see this getting a little complicated if I were to ever need to do one
that was 10 x 100, but I'll cross that bridge if I ever have a project that
takes me in that direction.

Here is an example of what I am talking about. I want to be able to pick a
number randomly for PID and then have it calculate what P1-P4 are without
having to use a reference table. So if the system were to pick number 11 it
would translate that into 1,3,4,5.

PID P1 P2 P3 P4
1 1 2 3 4
2 1 2 3 5
3 1 2 3 6
4 1 2 3 7
5 1 2 4 5
6 1 2 4 6
7 1 2 4 7
8 1 2 5 6
9 1 2 5 7
10 1 2 6 7
11 1 3 4 5
12 1 3 4 6
13 1 3 4 7
14 1 3 5 6
15 1 3 5 7
16 1 3 6 7
17 1 4 5 6
18 1 4 5 7
19 1 4 6 7
20 1 5 6 7
21 2 3 4 5
22 2 3 4 6
23 2 3 4 7
24 2 3 5 6
25 2 3 5 7
26 2 3 6 7
27 2 4 5 6
28 2 4 5 7
29 2 4 6 7
30 2 5 6 7
31 3 4 5 6
32 3 4 5 7
33 3 4 6 7
34 3 5 6 7
35 4 5 6 7


:

Problem Statement:

Given a set of monotonically increasing integers: I1, ..., In

Come up with a way to list all the distinct ordered m-tuples (a kind of
poset) in a meaningfully sorted way.

tblValues
VID theValue
1 1
2 2
3 3
4 4
5 5
6 6
7 7

n = RecordCount = 7

m = 4 for your example (four values listed: P1 to P4)

Count up in Base n - m + 1 (7 - 4 + 1 = 4) except where a digit column
goes from a higher value to a lower value.

For your example, generate all Base 4 values such that P1 <= P2 <= P3 <= P4

There are 35 Base 4 values that meet this condition.

Order the remaining records

Auxiliary Table of Integers:
tblIntegers
ID AutoNumber
theInt Integer
ID theInt
1 1
2 2
....
256 256

Here 256 (4 ^ 4) is chosen because 4 ^ 4 - 1 is the highest Base 4
number with four digits. The -1 in the SQL expression was used so that
tblIntegers will start with 1 for consistency with other places where I
use an auxiliary table.

Base4 is a public string function that returns the digits of the Base 4
representation of the input using a well-known technique. Since I only
needed four digits I used:

Public Function Base4(intN As Integer) As String
Dim I(3) As Integer
Dim intTemp As Integer
Dim intI As Integer

intTemp = intN
For intI = 0 To 3
I(intI) = intTemp - Int(intTemp / 4) * 4
intTemp = Int(intTemp / 4)
Next intI
Base4 = CStr(I(3)) & CStr(I(2)) & CStr(I(1)) & CStr(I(0))
End Function

qryPIs:
SELECT theInt-1 AS theIndex, Base4(theIndex) AS Base4, 1 +
Mid([Base4],1,1) AS P1, 2 + Mid([Base4],2,1) AS P2, 3 + Mid([Base4],3,1)
AS P3, 4 + Mid([Base4],4,1) AS P4
FROM tblIntegers
WHERE tblIntegers.theInt<=256 AND
Val(Mid(Base4([theInt]-1),1,1))<=Val(Mid(Base4([theInt]-1),2,1)) AND
Val(Mid(Base4([theInt]-1),2,1))<=Val(Mid(Base4([theInt]-1),3,1)) AND
Val(Mid(Base4([theInt]-1),3,1))<=Val(Mid(Base4([theInt]-1),4,1));

!qryPIs:
theIndex Base4 P1 P2 P3 P4
0 0000 1 2 3 4
1 0001 1 2 3 5
2 0002 1 2 3 6
3 0003 1 2 3 7
5 0011 1 2 4 5
6 0012 1 2 4 6
7 0013 1 2 4 7
10 0022 1 2 5 6
11 0023 1 2 5 7
15 0033 1 2 6 7
21 0111 1 3 4 5
22 0112 1 3 4 6
23 0113 1 3 4 7
26 0122 1 3 5 6
27 0123 1 3 5 7
31 0133 1 3 6 7
42 0222 1 4 5 6
43 0223 1 4 5 7
47 0233 1 4 6 7
63 0333 1 5 6 7
85 1111 2 3 4 5
86 1112 2 3 4 6
87 1113 2 3 4 7
90 1122 2 3 5 6
91 1123 2 3 5 7
95 1133 2 3 6 7
106 1222 2 4 5 6
107 1223 2 4 5 7
111 1233 2 4 6 7
127 1333 2 5 6 7
170 2222 3 4 5 6
171 2223 3 4 5 7
175 2233 3 4 6 7
191 2333 3 5 6 7
255 3333 4 5 6 7

qryPIDs:
SELECT (SELECT Count(*) FROM qryPIs AS A WHERE A.theIndex <
qryPIs.theIndex) + 1 AS PID, (SELECT A.theValue FROM tblValues AS A
WHERE VID = qryPIs.P1) AS P1, (SELECT A.theValue FROM tblValues AS A
WHERE VID = qryPIs.P2) AS P2, (SELECT A.theValue FROM tblValues AS A
WHERE VID = qryPIs.P3) AS P3, (SELECT A.theValue FROM tblValues AS A
WHERE VID = qryPIs.P4) AS P4
FROM qryPIs;

!qryPIDs:
PID P1 P2 P3 P4
1 1 2 3 4
2 1 2 3 5
3 1 2 3 6
....
35 4 5 6 7

Summing P1 to P4 could be used to help solve one of the puzzles I see in
the newspapers where m squares filled with distinct digits in a row or
column must add to prescribed values.

A generalized Base4 function might look like (untested):

Public Function BaseB(lngN As Long, b As Integer, mPlaces As Integer) As
String
Dim I() As Integer
Dim lngTemp As Long
Dim intI As Integer
Dim strTemp As String

ReDim I(mPlaces)
lngTemp = lngN
For intI = 0 To mPlaces - 1
I(intI) = lngTemp - Int(lngTemp / b) * b
lngTemp = Int(lngTemp / b)
Next intI
strTemp = ""
For intI = mPlaces - 1 To 0 Step -1
strTemp = strTemp & CStr(I(intI))
Next intI
BaseB = strTemp
End Function

For n = 10 and m = 5, Base 6 => 6 ^ 5 = 7776 won't even blow up an
Integer variable. You can populate tblIntegers with 7776 values using
code. Something like (Dim's omitted):

Set MyDB = CurrentDb
strSQL = "SELECT * FROM tblIntegers;"
Set FillRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
For I = 1 To 7776
FillRS.AddNew
FillRS("theInt") = I
FillRS.Update
Next I
FillRS.Close
Set FillRS = Nothing
Set MyDB = Nothing
MsgBox ("Done.")

So, you'll need Base b = 6, mPlaces = 5, 7776 instead of 256 and P1
through P5. Hopefully this will help you solve your "puzzle."

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Thank you, very helpful and you have remminded me that I should have paid
more attention in math class 20 years ago.

I think solving math related problems using Access is one of my strengths.

I see this getting a little complicated if I were to ever need to do one
that was 10 x 100, but I'll cross that bridge if I ever have a project that
takes me in that direction.

I agree. One possibility is to use VBA code exclusively instead of a
combination of VBA and SQL for problems that become unwieldly in SQL.

James A. Fortune
(e-mail address removed)

 

[Guest]

To make patterns of up to 10 digits I came up with my own code a few years
ago. There is without a doubt a smarter and probably faster way of doing so,
but for the numbers I work wth this code is more than sufficient so I have
never looked into doing it the right way. I can see it being a problem if I
go above 10 X 20 which so far is the largest pattern I have had to make.

Function Make_Patterns()

Dim PH(40), PE(40), InUse(20)

Base = 7
Digits = 5
StartNum = 1
tblName = "patterns"

For DC = 1 To Digits
MyFields = MyFields & ", P" & DC
Next DC
MyFields = "PID" & MyFields

DoCmd.RunSQL "delete from tbl" & tblName

For DC = 1 To 10
If DC <= Digits Then
PE(DC) = Base
InUse(DC) = 1
Else
InUse(DC) = 0
End If
Next DC
TotalCombos = Perm(Digits, Base)

StartTime = Now()
LastStart = Now()


PatternID = 0
For P1 = StartNum To PE(1)
PH(1) = P1
For P2 = (PH(1) + 1) * InUse(2) To PE(2) * InUse(2)
PH(2) = P2
For P3 = (PH(2) + 1) * InUse(3) To PE(3) * InUse(3)
PH(3) = P3
For P4 = (PH(3) + 1) * InUse(4) To PE(4) * InUse(4)
PH(4) = P4
For P5 = (PH(4) + 1) * InUse(5) To PE(5) * InUse(5)
PH(5) = P5
For P6 = (PH(5) + 1) * InUse(6) To PE(6) * InUse(6)
PH(6) = P6
For P7 = (PH(6) + 1) * InUse(7) To PE(7) * InUse(7)
PH(7) = P7
For P8 = (PH(7) + 1) * InUse(8) To PE(8) * InUse(8)
PH(8) = P8
For P9 = (PH(8) + 1) * InUse(9) To PE(9) * InUse(9)
PH(9) = P9
For P10 = (PH(9) + 1) * InUse(10) To PE(10) * InUse(10)
PH(10) = P10
MyNum = ""
For DC = 1 To Digits
MyNum = MyNum & ", " & PH(DC)
Next DC
PatternID = PatternID + 1
MyNum = PatternID & MyNum
'Debug.Print MyNum
DoCmd.RunSQL "insert into tbl" & tblName & " (" & MyFields & ") values
(" & MyNum & ")"
X = X + 1
If X = 10000 Then
X = 0
AllMinutes = DateDiff("N", StartTime, Now())
SegMinutes = DateDiff("S", LastStart, Now())
StatNum = " - Min: " & AllMinutes & " Hours: " & Round(AllMinutes /
60, 2) & " Time/Seg: " & SegMinutes
Debug.Print PatternID & " of " & TotalCombos & StatNum
LastStart = Now()
End If


Next P10
Next P9
Next P8
Next P7
Next P6
Next P5
Next P4
Next P3
Next P2
Next P1
End Function



Function Perm(Digits, Base)

BaseSum = 1
DigitsSum = 1
For BaseCount = 0 To Digits - 1
BaseSum = BaseSum * (Base - BaseCount)
Next BaseCount
For DigitsCount = 0 To Digits - 1
DigitsSum = DigitsSum * (Digits - DigitsCount)
Next DigitsCount
Perm = BaseSum / DigitsSum


End Function

 

[James A. Fortune]

To make patterns of up to 10 digits I came up with my own code a few years
ago. There is without a doubt a smarter and probably faster way of doing so,
but for the numbers I work wth this code is more than sufficient so I have
never looked into doing it the right way. I can see it being a problem if I
go above 10 X 20 which so far is the largest pattern I have had to make.

Function Make_Patterns()
...

I'm all for smarter and faster . This is an interesting problem.
Maybe within a week or two I'll have some time to look at this problem
more closely.

James A. Fortune
(e-mail address removed)

 

[Guest]

After a few hours of banging my head against a wall, two movies and a couple
hours computer gaming and 5 hours of non continous sleep I finally figured it
out. The answer was in my car's odometer. By replacing the for next loop with
a random number generator I can calculate what the number would be and if it
happens to return a number that does not match my criteria, I can have it run
again.

The function Basing was something I wrote a long time ago for another
purpose, but seem to find regular uses for. There is probaly a smarter way to
do it, but that was the way I figured out and haven't had a need to go back
and improve it.


Function Utility_Odometer()
StartNum = 0
LastNum = 4
Digits = 4
Base = 0
For DC = StartNum To LastNum
Base = Base + 1
Next DC

TotalCombos = Base ^ Digits

For PID = 1 To TotalCombos
MyNum = ""
For DC = LastNum - 1 To 0 Step -1
BaseN = Base ^ DC
NumberOfRot = Basing(BaseN, PID) / BaseN
N = (Base - (Basing(Base, NumberOfRot) - NumberOfRot)) - 1
MyNum = MyNum & "," & N
Next DC
MyNum = PID & MyNum
Debug.Print MyNum
Next PID




End Function




Function Basing(BaseN, N)


R1 = (Round(N / BaseN, 10)) * BaseN
R2 = (Round(N / BaseN, 0)) * BaseN
F = R2
If R1 > R2 Or N = 1 Or R2 = 0 Then F = R2 + BaseN

Basing = F

End Function

 

[James A. Fortune]

After a few hours of banging my head against a wall, two movies and a couple
hours computer gaming and 5 hours of non continous sleep I finally figured it
out. The answer was in my car's odometer. By replacing the for next loop with
a random number generator I can calculate what the number would be and if it
happens to return a number that does not match my criteria, I can have it run
again.

The function Basing was something I wrote a long time ago for another
purpose, but seem to find regular uses for. There is probaly a smarter way to
do it, but that was the way I figured out and haven't had a need to go back
and improve it.


Function Utility_Odometer()
StartNum = 0
LastNum = 4
Digits = 4
Base = 0
For DC = StartNum To LastNum
Base = Base + 1
Next DC

TotalCombos = Base ^ Digits

For PID = 1 To TotalCombos
MyNum = ""
For DC = LastNum - 1 To 0 Step -1
BaseN = Base ^ DC
NumberOfRot = Basing(BaseN, PID) / BaseN
N = (Base - (Basing(Base, NumberOfRot) - NumberOfRot)) - 1
MyNum = MyNum & "," & N
Next DC
MyNum = PID & MyNum
Debug.Print MyNum
Next PID




End Function




Function Basing(BaseN, N)


R1 = (Round(N / BaseN, 10)) * BaseN
R2 = (Round(N / BaseN, 0)) * BaseN
F = R2
If R1 > R2 Or N = 1 Or R2 = 0 Then F = R2 + BaseN

Basing = F

End Function






:

If I get a chance to work on this I'll examine the code as part of the
process. I'm glad you made it work for the more general case. Running
the random number generator multiple times doesn't seem like a good
thing to do but maybe it will be O.K. within your context.

James A. Fortune
(e-mail address removed)