Query for random values multiple records

[Clddleopard]

I have a table of names (lots of names! Too many to write individual queries
for...). I want three random non-repeating numbers between 1 and 7 for each
of these names. Output example:
Name1 3
Name1 2
Name1 5
Name2 1
Name2 5
Name2 4

From reading on the boards I was able to come up with a way to generate
output such as this:
Name1 3
Name1 2
Name1 5
Name2 3
Name2 2
Name2 5

However, that will not work for what I want the random numbers for. I need a
different set of each random non-repeated number set for each name.
Thanks!

 

[John W. Vinson]

However, that will not work for what I want the random numbers for. I need a
different set of each random non-repeated number set for each name.
Thanks!

You can use the Top Values property of a query, with help
from a little VBA. Put this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Sort the query by Shuffle, and set its Top Values property
to the number of records you want to see. In your case this will need to be a
Subquery.

John W. Vinson [MVP]

 

[Clddleopard]

Ok, that worked well, except I get decimal values and I need whole numbers
between 1 and 7. What modifications should I make?

However, that will not work for what I want the random numbers for. I need a
different set of each random non-repeated number set for each name.
Thanks!

You can use the Top Values property of a query, with help
from a little VBA. Put this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Sort the query by Shuffle, and set its Top Values property
to the number of records you want to see. In your case this will need to be a
Subquery.

John W. Vinson [MVP]

 

[John W. Vinson]

Ok, that worked well, except I get decimal values and I need whole numbers
between 1 and 7. What modifications should I make?

Well... that's a bit different. If you really mean RANDOM numbers, then you
can and will get duplicates!

I'm going to have to think about this and post back later...

John W. Vinson [MVP]

 

[Clddleopard]

Yeah, the random part was pretty easy (I actually found your module on the
board and tried to use it before). It's the non-repeating part that's getting
me! I need to tell it to check the previous values and try again if it gets
an equal value. Thanks for working on it!

 

[Clddleopard]

Also, BTW, the numbers need to be whole numbers.

 

[John W. Vinson]

Yeah, the random part was pretty easy (I actually found your module on the
board and tried to use it before). It's the non-repeating part that's getting
me! I need to tell it to check the previous values and try again if it gets
an equal value. Thanks for working on it!

I'd suggest using a table of sequential numbers, and "shuffling" it using the
techniques in the code. How to associate the sequential numbers with your
record isn't quite clear to me though!

John W. Vinson [MVP]

 

[Clddleopard]

One thought I had was to write some sort of code to run the following query
multiple times, replacing the Where information inside the quotes with a
different name each time, just going down the row of the table.
INSERT INTO [Enrichment Assignments] ( [Animal Name] )
SELECT [Animal ID information].[Animal Name]
FROM Query4, [Animal ID information]
WHERE ((([Animal ID information].[Animal Name])="Name"));
Could you help me with that? It seems like it ought to be doable. The names
are already in a convenient list, it seems like there ought to be a way to
tell the query to run until it gets to the end of the list.

"> I'd suggest using a table of sequential numbers, and "shuffling" it using
the

 

[John W. Vinson]

One thought I had was to write some sort of code to run the following query
multiple times, replacing the Where information inside the quotes with a
different name each time, just going down the row of the table.

You're thinking procedurally. Queries AREN'T procedural - the work on sets!

Just use a JOIN to join the two tables:

INSERT INTO [Enrichment Assignments] ( [Animal Name] )
SELECT [Animal ID information].[Animal Name]
FROM Query4 INNER JOIN [Animal ID information]
ON [Animal ID information].[Animal Name] = Query4.[Animal Name];

assuming that the animal name exists in Query4 (which I cannot see).

John W. Vinson [MVP]

 

[Clddleopard]

Query 4 is my randomizing query. If I JOIN it with the other query, I get the
same three random results for each name. The randomizing query needs to be
run separately for every name, somehow. Or, I just need a completely
different way of doing this...

One thought I had was to write some sort of code to run the following query
multiple times, replacing the Where information inside the quotes with a
different name each time, just going down the row of the table.

You're thinking procedurally. Queries AREN'T procedural - the work on sets!

Just use a JOIN to join the two tables:

INSERT INTO [Enrichment Assignments] ( [Animal Name] )
SELECT [Animal ID information].[Animal Name]
FROM Query4 INNER JOIN [Animal ID information]
ON [Animal ID information].[Animal Name] = Query4.[Animal Name];

assuming that the animal name exists in Query4 (which I cannot see).

John W. Vinson [MVP]