selecting random nos

[cliff]

Hi, I have bit new to access. suppose I want to choose Five random nos from 1
to 31, but I want to put restriction selecting numbers the following nos

17,13,26,19,6 maximum Two can be selected
17,13,26,28,19 "
26,17,13,28,12 "
17,26,12,13,31 "
17,26,1,31,12 "
4,5,25,30,7,18,20,3,16,2,9,11,14,21,15,10,8,22, 9 minimum Two and Maximum
5 can be selected


can you help please

thanks
cliff

 

[Dale Fye]

Cliff,

I would approach this in this way.

1. Create a table (tbl_Rules) with fields (RuleID-Number, RuleDesc-Text,
RuleMin-Number, RuleMax-Number), and values like (taken from your example):

RuleID RuleDesc RuleMin RuleMax
1 a 0 2
2 b 0 2
....
6 f 2 5

2. Then create another table (tbl_Rule_Values) which contains two fields
(RuleID - Number, RuleVal - Number) and values like:
RuleID RuleVal
1 17
1 13
1 26
....
3 26
3 17
3 13
(you get the idea)

3. Create a third table (tbl_RndNum) with a single field (RndVal). This
will be the table where you store the random numbers you generate. There
should never be more than 5 values in this table

4. Add the following query (qry_RuleViolations) to your database. This
query will identify which of the rules have been violated.

SELECT tbl_Rules.RuleID, tbl_Rules.RuleDesc,
tbl_Rules.RuleMin, tbl_Rules.RuleMax,
Count(tbl_RndNum.[RndVal]) AS RuleCount
FROM (tbl_Rules INNER JOIN tbl_Rule_Values
ON tbl_Rules.RuleID=tbl_Rule_Values.RuleID)
INNER JOIN tbl_RndNum
ON tbl_Rule_Values.RuleVal=tbl_RndNum.RndVal
GROUP BY tbl_Rules.RuleID, tbl_Rules.RuleDesc,
tbl_Rules.RuleMin, tbl_Rules.RuleMax
HAVING Count(tbl_RndNum.[RndVal])<[RuleMin]
OR Count(tbl_RndNum.[RndVal])>[RuleMax]

5. Then, in your code, following where you insert the last value into
tbl_RndNum, use the DCOUNT("RuleID", "qry_RuleViolations") domain function to
count the number of records in this query. If it is zero, then none of the
rules have been violated. Otherwise, delete all the values from your random
number table and start over.

Using this technique, you could even force the system to generate a specific
number by setting the RuleMin=1, RuleMax=1, and then only having 1 record in
tbl_Rule_Values that contains the RuleID and the specific "random" number
that you want.

-
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[cliff]

Hi Dale Fye,

Thanks for your help. It is wonderful suggestion and working well. Now can
you tell me how to generate Five Random Numbers and copy the same to
tbl_RndNum automatically



thanks million

cliff

 

[Dale Fye]

Right after I hit send, I realized that I had assumed that you knew how to do
this.

Add a command button to a form. Call it cmd_Random. Then, put the
following in that controls click event.

Private Sub cmd_Random_Click()

'Generates random numbers between zero and some upper bound
Dim intCount As Integer
Dim intRndNum As Integer, intUpperBound As Integer
Dim strCriteria as string
Dim intViolations As Integer
dim strSQL as string, strMsg as string

'Set this value to your maximum value
intUpperBound = 10

'Loop until a set of random number meets the requirements or
'the user elects not to continue.
Do

Randomize
intCount = 0

'delete all the records from the random number table
CurrentDb.Execute "DELETE * FROM tbl_RndNum"

'Loop until 5 numbers that don't match each other have been selected
Do
intRndNum = Int(Rnd * intUpperBound) + 1
strCriteria = "RndVal = " & intRndNum
If DCount("RndVal", "tbl_RndNum", strCriteria) = 0 Then
intCount = intCount + 1
strSQL = "INSERT INTO tbl_RndNum (RndVal) " _
& "Values (" & intRndNum & ")"
currentdb.execute strSQL
End If
Loop While intCount < 5

intViolations = DCount("RuleID", "qry_RndNumRules")
If intViolations > 0 Then
strMsg = "Violated " & intViolations & " rules"
If MsgBox(strMsg, vbRetryCancel) = vbCancel then Exit Do
Else
Exit Do
End If
Loop

End Sub

Also, during testing, I realized that the query needs to be modified as:

SELECT tbl_Rules.RuleID, tbl_Rules.RuleDesc,
tbl_Rules.RuleMin, tbl_Rules.RuleMax,
Count(tbl_RndNum.RndVal) AS RuleCount
FROM (tbl_Rules LEFT JOIN tbl_Rule_Values
ON tbl_Rules.RuleID = tbl_Rule_Values.RuleID)
LEFT JOIN tbl_RndNum
ON tbl_Rule_Values.RuleVal = tbl_RndNum.RndVal
GROUP BY tbl_Rules.RuleID, tbl_Rules.RuleDesc,
tbl_Rules.RuleMin, tbl_Rules.RuleMax
HAVING Count(tbl_RndNum.RndVal)<[RuleMin]
OR Count(tbl_RndNum.RndVal)>[RuleMax]

This replaces the INNER JOINS with LEFT JOINS to make sure that all of the
rules are considered (in my case the other method didn't work when I added a
rule that the random number sequence must include a specific value). Once I
made this change, it worked properly.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[cliff]

Hi Dale Fye

thanks for reply. I applied suggestion and it is working well. thank you so
much .

Now I want to keep only those random nos which are not violated rules set
by generating ID for each. what are the steps to be followed to do that and
how to do that.


Thanks for help


cliff

 

[Dale Fye]

Cliff,

Not sure what you mean.

Do you mean that you want to save this series of 5 random values as a group,
and generate another five? If so, you will also have to check that each new
set of 5 numbers doesn't repeat a group that you have already created. Let
me know what you mean and I'll try to get back to you later this evening.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[cliff]

Hi Dale Fye,

Thanks for your prompt reply. Yes I want save series of 5 random values and
create generate another five and so on. But I want to save only those groups
( 5 series of 5 random values) which are not violated set rules. Further, I
do not want repeat the group already created.


Thanks a lot


cliff

 

[Dale Fye]

Cliff,

1. Create another table (tbl_RndNumStore) with fields (Iteration - number,
RndVal - Number).

2. Create a query (qry_RndNumDupSeries) that maps the new selections to the
ones in the Random Number Store table, and checks to see if there is an exact
match. That query would look like:

SELECT tbl_RndNumStore.Iteration,
Count(tbl_RndNum.RndVal) AS Matches
FROM tbl_RndNumStore
INNER JOIN tbl_RndNum
ON tbl_RndNumStore.RndVal = tbl_RndNum.RndVal
GROUP BY tbl_RndNumStore.Iteration
HAVING Count(tbl_RndNum.RndVal)=5

3. Create another query (qry_RndNumStore) that will add the number in
tbl_RndNum to tbl_RndNumStore:

INSERT INTO tbl_RndNumStore ( Iteration, RndVal )
SELECT NZ(DMax("Iteration","tbl_RndNumStore"),0)+1 AS Expr1, tbl_RndNum.RndVal
FROM tbl_RndNum;

4. Delete the code from the click event of the button that generates the
random number and replace it with (don't forget to save the upper bound):

Private Sub cmd_Random_Click()

'Generates random numbers between zero and some upper bound
Dim intCount As Integer
Dim intRndNum As Integer, intUpperBound As Integer
Dim intViolations As Integer

intUpperBound = 10

StartLoop:

Randomize
intCount = 0
CurrentDb.Execute "DELETE * FROM tbl_RndNum"
Do
intRndNum = Int(Rnd * intUpperBound) + 1
If DCount("RndVal", "tbl_RndNum", "RndVal = " & intRndNum) = 0 Then
intCount = intCount + 1
CurrentDb.Execute "INSERT INTO tbl_RndNum (RndVal) Values (" &
intRndNum & ")"
End If
Loop While intCount < 5

intViolations = DCount("RuleID", "qry_RndNumRules")
If intViolations > 0 Then
If MsgBox("Violated " & intViolations & " rules", vbRetryCancel) =
vbRetry Then
GoTo StartLoop
End If
ElseIf DCount("Iteration", "qry_RndNumDupSeries") > 0 Then
If MsgBox("Generated series has already been used!", vbRetryCancel)
= vbRetry Then
GoTo StartLoop
End If
Else
MsgBox "Success!"
CurrentDb.QueryDefs("qry_RndNumStore").Execute
End If

End Sub


I tested this with an upper bound of 10, and created several rules, one that
said there had to be at least 2 even numbers, one for at least 2 odd numbers,
and another that required the number 3. When I used these rules, and limited
the upper bound to 10, it took about 10 iterations before I started getting
messages that I had already generated that same number sequence.

I would probably also add a button to the form to clear out tbl_RndNumStore,
so you can start that from scratch, whenever you want.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[cliff]

Hi Dale Fye,


Thanks you very very very much for your help and prompt and time bound
response.You have stated that you have tried by setting rules for odd nos and
even nos. can you tell me how set the rules for odd/even/prime/small
odd/even etc?. Is it by typing values of odd and even or any other method .


Thanks million


cliff

 

[Dale Fye]

It was easy for me, because I was only using the numbers 1 - 10. To create a
rule that requires 2 even numbers, enter the following in the rules table

RuleID RuleDesc RuleMin RuleMax
X Even #'s 2 2

Then in the Rule Values table enter

RuleID RuleVal
X 2
X 4
X 6
X 8
X 10

Same thing for Odd numbers

A rule requiring 3 prime #'s might look like:

Y Primes 3 3

with the RuleValues of:
Y 2
Y 3
Y 5
Y 7

Note that the X and Y values are actually numbers.

--
HTH
Dale

Don''t forget to rate the post (bottom right corner of the following link)
if this was helpful!
http://www.microsoft.com/office/com...ries&mid=fa7d6671-bd98-4dc4-92d6-39e0efbc933e

email address is invalid
Please reply to newsgroup only.

 

[cliff]

Hi Dale Fye, thanks for help. Suppose I want to choose Five Nos from the
following table
mytable
n1
14
18
23
34
38
35
16
17

but I want to put restriction selecting numbers the following nos

17,13,26,19,6 maximum Two can be selected
17,13,26,28,19 "
26,17,13,28,12 "
17,26,12,13,31 "
17,26,1,31,12 "
4,5,25,30,7,18,20,3,16,2,9,11,14,21,15,10,8,22, 9 minimum Two and Maximum
5 can be selected.

I want to save the nos met the above into the table.

I have the following query to select five nos from mytable

SELECT TOP 5 mytable.n1
FROM mytable
WHERE (((randomizer())=0))
ORDER BY Rnd(IsNull(mytable.n1)*0+1);

Can you hep to join the various criterias to the selected five nos. If all
criteria are fullfilled then I want to save the selected nos in group


thansk in advance