Random Selection by Group

[Micki]

Hello, all. I have the following key fields in a table called Line Welds
Lot
WeldNo

I also have a table Lot Status with a yes/no field [Closed]

I need to group any Lots NOT closed and then randomly select one WeldNo per
group.

Can anyone help? Thanks in advance . . .

 

[Piet Linden]

Hello, all.  I have the following key fields in a table called Line Welds
   Lot
   WeldNo

I also have a table Lot Status with a yes/no field [Closed]

I need to group any Lots NOT closed and then randomly select one WeldNo per
group.

Can anyone help?  Thanks in advance . . .

start here:
http://www.mvps.org/access/queries/qry0011.htm

Then just select the top value or whatever of the random number column
per group.

 

[Micki]

I saw that, but it just confused me. I ended up using an auto id in my
table, and inserting a random number creator off of that auto id into the
query that writes to the table. Then I saved my table sorted by the random
number. Finally, I created a query that groups the lots & takes the first of
the random numbers. I won't get a new random selection everytime I run the
query, but at least I have "predestined" some records to be randomly chossen.

Hello, all. I have the following key fields in a table called Line Welds
Lot
WeldNo

I also have a table Lot Status with a yes/no field [Closed]

I need to group any Lots NOT closed and then randomly select one WeldNo per
group.

Can anyone help? Thanks in advance . . .

start here:
http://www.mvps.org/access/queries/qry0011.htm

Then just select the top value or whatever of the random number column
per group.

 

[Jackson, Lucas]

What does this do?

I saw that, but it just confused me. I ended up using an auto id in my
table, and inserting a random number creator off of that auto id into the
query that writes to the table. Then I saved my table sorted by the random
number. Finally, I created a query that groups the lots & takes the first of
the random numbers. I won't get a new random selection everytime I run the
query, but at least I have "predestined" some records to be randomly chossen.

Hello, all. I have the following key fields in a table called Line Welds
Lot
WeldNo

I also have a table Lot Status with a yes/no field [Closed]

I need to group any Lots NOT closed and then randomly select one WeldNo per
group.

Can anyone help? Thanks in advance . . .

start here:
http://www.mvps.org/access/queries/qry0011.htm

Then just select the top value or whatever of the random number column
per group.

 

[Micki]

It ensures that a different "count" of each group gets selected. I might get
the 8th record of group one & the 16th of group two. Each new record has the
opportunity of being the next random sampling in its group because it may be
assigned a random number greater than the last greatest random number in the
group.

What does this do?

I saw that, but it just confused me. I ended up using an auto id in my
table, and inserting a random number creator off of that auto id into the
query that writes to the table. Then I saved my table sorted by the random
number. Finally, I created a query that groups the lots & takes the first of
the random numbers. I won't get a new random selection everytime I run the
query, but at least I have "predestined" some records to be randomly chossen.

Hello, all. I have the following key fields in a table called Line Welds
Lot
WeldNo

I also have a table Lot Status with a yes/no field [Closed]

I need to group any Lots NOT closed and then randomly select one WeldNo per
group.

Can anyone help? Thanks in advance . . .

start here:
http://www.mvps.org/access/queries/qry0011.htm

Then just select the top value or whatever of the random number column
per group.