I want to randomly select records.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to randomly select records in ACCESS 2003. In a prior version I
used the following in my query: "PlusOne([fieldname]) Mod 2", however, I now
get an error message as an undefined function: Plus One.
 
I am trying to randomly select records in ACCESS 2003. In a prior version I
used the following in my query: "PlusOne([fieldname]) Mod 2", however, I now
get an error message as an undefined function: Plus One.
Click to expand...

Well, the prior version must have had a user defined function named
PlusOne. That's not (and has never been, to my knowledge) a built in
Access function.

To select a random record, you can use a Query which shuffles the
records into random order, and pick the first card off the shuffled
deck:

SELECT TOP 1 <fields>
FROM yourtable
ORDER BY Rnd([ID]);

where ID is some non-negative numeric field in the table. You do need
to pass a parameter to Rnd(), otherwise it just calls Rnd() once and
gives the same value for each record, defeating the purpose of the
technique.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
I am not clear as to where I add this to my query. Here is my complete SQL
view:

SELECT DISTINCTROW Children.Child_LName, Children.Child_FName,
Children.Child_MName, Children.Child_Admission_Date, Children.Child_DOB,
Enterprise_Organizations.Org_Name
FROM Enterprise_Organizations INNER JOIN Children ON
Enterprise_Organizations.Org_ID = Children.Home_ID
GROUP BY Children.Child_LName, Children.Child_FName, Children.Child_MName,
Children.Child_Admission_Date, Children.Child_DOB,
Enterprise_Organizations.Org_Name, Children.Child_ID, ([Child_id]) Mod 19,
Children.Campus_ID, Children.Child_ID, Children.Child_Status
HAVING (((([Child_id]) Mod 19)=0) AND ((Children.Campus_ID)=2) AND
((Children.Child_Status)="resident"))
ORDER BY Children.Child_LName, Children.Child_FName;

This query pulls me every 19th record from my list, but does not randomly
select them or change them when I re-run the query.

John Vinson said:
I am trying to randomly select records in ACCESS 2003. In a prior version I
used the following in my query: "PlusOne([fieldname]) Mod 2", however, I now
get an error message as an undefined function: Plus One.
Click to expand...

Well, the prior version must have had a user defined function named
PlusOne. That's not (and has never been, to my knowledge) a built in
Access function.

To select a random record, you can use a Query which shuffles the
records into random order, and pick the first card off the shuffled
deck:

SELECT TOP 1 <fields>
FROM yourtable
ORDER BY Rnd([ID]);

where ID is some non-negative numeric field in the table. You do need
to pass a parameter to Rnd(), otherwise it just calls Rnd() once and
gives the same value for each record, defeating the purpose of the
technique.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
I am not clear as to where I add this to my query. Here is my complete SQL
view:

SELECT DISTINCTROW Children.Child_LName, Children.Child_FName,
Children.Child_MName, Children.Child_Admission_Date, Children.Child_DOB,
Enterprise_Organizations.Org_Name
FROM Enterprise_Organizations INNER JOIN Children ON
Enterprise_Organizations.Org_ID = Children.Home_ID
GROUP BY Children.Child_LName, Children.Child_FName, Children.Child_MName,
Children.Child_Admission_Date, Children.Child_DOB,
Enterprise_Organizations.Org_Name, Children.Child_ID, ([Child_id]) Mod 19,
Children.Campus_ID, Children.Child_ID, Children.Child_Status
HAVING (((([Child_id]) Mod 19)=0) AND ((Children.Campus_ID)=2) AND
((Children.Child_Status)="resident"))
ORDER BY Children.Child_LName, Children.Child_FName;

This query pulls me every 19th record from my list, but does not randomly
select them or change them when I re-run the query.
Click to expand...

Well... no, it doesn't; there's no reason why it should. It also does
not use anything resembling my answer to your question, nor does it
reflect your original question (which had to do with retrieving a
single randomly selected record).

Try instead

SELECT TOP 5.26 PERCENT Children.Child_LName, Children.Child_FName,
Children.Child_MName, Children.Child_Admission_Date,
Children.Child_DOB,
Enterprise_Organizations.Org_Name
FROM Enterprise_Organizations INNER JOIN Children ON
Enterprise_Organizations.Org_ID = Children.Home_ID
GROUP BY Children.Child_LName, Children.Child_FName,
Children.Child_MName,
Children.Child_Admission_Date, Children.Child_DOB,
Enterprise_Organizations.Org_Name, Children.Child_ID, ([Child_id]) Mod
19,
Children.Campus_ID, Children.Child_ID, Children.Child_Status
HAVING (((([Child_id]) Mod 19)=0) AND ((Children.Campus_ID)=2) AND
((Children.Child_Status)="resident"))
ORDER BY Rnd([Child_ID]);

or - for more thorough randomization - create a new Module named
basRandom, with the following code:

Public Function RndNum(vIn As Variant) As Double
Static bRandomized As Boolean
If Not bRandomized Then ' Reset the random number
' generator, only the first time the function is called
bRandomized = True
Randomize
End If
RndNum = Rnd()
End Function

and use RndNum([Child_ID]) in the Order By clause.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top