selecting nos

[cliff]

HI Karl, I would like to give you details of structure and data. Mydata
contains say about 10 nos.
N1
12
23
31
17
16
8
5
10
19
24
2

out of which I want to select Five Nos at a time. While selected should meet
criteria set for various groups of nos. Rules are put in a table called
tbl_Rules and value in other table called tbl_Rule_values. Rules like :-

1. Of Five. Minimum odd nos should be 1 and Maximum 4 and likewise
2. of five , Mimumum 1 should be even and Maximum 3 etc

I have the following query to select five nos from mytable

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

I have following query to check wthether selected nos fulfill set rules

SELECT tbl_Rules.RuleID, tbl_Rules.RuleDesc,
tbl_Rules.RuleMin, tbl_Rules.RuleMax,
Count(qry_RndNum.[RndVal]) AS RuleCount
FROM (tbl_Rules INNER JOIN tbl_Rule_Values
ON tbl_Rules.RuleID=tbl_Rule_Values.RuleID)
INNER JOIN qry_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(qry_RndNum.[RndVal])<[RuleMin]
OR Count(qry_RndNum.[RndVal])>[RuleMax]

suppose randomly selected nos from mytable are 12 ,31,16,5,24 I want to
display message that "selected nos fulfilled all condtions" and I want save
nos in a table for future reference. In case, some of the conditions are
not met then, I want to display message say "7 condtions are not fulfilled"
etc and delete the selected nos and try again to select another set of nos..

can u tell me how to do it?



thanks lot

 

[KARL DEWEY]

Rules like :-
1. Of Five. Minimum odd nos should be 1 and Maximum 4 and likewise
2. of five , Mimumum 1 should be even and Maximum 3 etc

What field of tbl_Rules or tbl_Rule_values did you place the above?

You posted data of mytable but not for the other tables.

I am not familiar with randomizer() function.

 

[Michel Walsh]

You can try (although it will be probably slow):

SELECT a.n1 AS one, b.n1 AS two, c.n1 AS three, d.n1 AS four, e.n1 AS five
FROM ((( myTable AS a INNER JOIN myTable As b ON a.n1 >b.n1)
INNER JOIN mytable As c ON b.n1>c.n1)
INNER JOIN mytable As d ON c.n1>d.n1)
INNER JOIN mytable As e ON d.n1>e.n1
WHERE (((one MOD 2) + (two MOD 2)
+ ( three MOD 2) + (four MOD 2) + (five MOD 2))
BETWEEN 1 AND 4)


will give ALL the possibilities having between 1 and 4 odd numbers (or
between 1 and 4 even numbers, if you prefer)

You can add more restrictions in the WHERE clause as required:


AND (one MOD 2)
AND (NOT five MOD 2)


forces the highest number to be odd AND the lowest one to be even. (These
formulations are based on the fact that 0 is false and any other not null
value is true).



Vanderghast, Access MVP