Rob said:
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)