Random picking of record in a table

[pistolpinoy72]

Hi!

I'm working on a raffle draw project. Someone may be of help. I have a table with the names of employees in our office. I want the system to pick an employee name in the table RANDOMLY. Selected employee name cannot be picked anymore.

Appreciate reply from you guys/gals. Thanks.

 

[jadams0173]

Assuming that each employee was stored with an autonumber field, you could have a look at the RND function to randomly pick one of the autonumbers. After that number was used and either set a flag in that table so you know that person has been picked or store the number in another table and compare the random number to the numbers in that table to see if it has been used.

 

[mp9]

And if you haven't got a numeric ID field in your table, you could use a query something like this to pick a winner - this example assumes your table is called tblEmployee and that you're using a text field called EmpName to identify each record by.

SELECT tblEmployee.EmpName
FROM tblEmployee
WHERE Int((Rnd()*DCount("*","tblEmployee"))+0.5))=((Select Count (*) FROM [tblEmployee] as Temp 
WHERE [Temp].[EmpName] < [tblEmployee].[EmpName])+1;

This is hideous but works, randonly returning a record every time the query is run..... HOWEVER your best bet is to add a primary key field of type AutoNumber and use that with the Rnd() function, as discussed in the previous post.