How to assign incrementing numbers for range and random sample that range?

[JonHuff]

looking for MS Access VBA code to do the following: I am trying to assign incrementing numbers ( ie - 1-10) to records in an existing table by date and pull a 10% random sample from that table based on the numbers assigned. Any help is appreciated. Existing Example Table:

number date person
45001 1/19/2011 Ray
21020 1/19/2011 Steve
49009 1/19/2011 Bob
72005 1/19/2011 Ray
62203 1/19/2011 Bob
83406 1/19/2011 Ray
88700 1/19/2011 Tom
99174 1/19/2011 Tom
11726 1/19/2011 Tom
90999 1/19/2011 Ray
81119 1/18/2011 Ray
12226 1/18/2011 Steve
23334 1/18/2011 Bob
14448 1/18/2011 Ray
65550 1/18/2011 Bob
96663 1/18/2011 Ray
77771 1/18/2011 Tom
58882 1/18/2011 Tom
39994 1/18/2011 Tom
80996 1/18/2011 Ray

i want it to add the new field (No) to the existing table assigning numbers incrementing from 1 to x where x is the count of records for each varying date, like such

No number date person
1 45001 1/19/2011 Ray
2 21020 1/19/2011 Steve
3 49009 1/19/2011 Bob
4 72005 1/19/2011 Ray
5 62203 1/19/2011 Bob
6 83406 1/19/2011 Ray
7 88700 1/19/2011 Tom
8 99174 1/19/2011 Tom
9 11726 1/19/2011 Tom
10 90999 1/19/2011 Ray
1 81119 1/18/2011 Ray
2 12226 1/18/2011 Steve
3 23334 1/18/2011 Bob
4 14448 1/18/2011 Ray
5 65550 1/18/2011 Bob
6 96663 1/18/2011 Ray
7 77771 1/18/2011 Tom
8 58882 1/18/2011 Tom
9 39994 1/18/2011 Tom
10 80996 1/18/2011 Ray

then i want it to create/return in a new table the random sample (10%) for each date change - example output would be a new table like such:

No number date person
5 62203 1/19/2011 Bob
2 12226 1/18/2011 Steve


if i changed the % to 20% random sample- I want a table like such:

No number date person
9 11726 1/19/2011 Tom
7 88700 1/19/2011 Tom
10 80996 1/18/2011 Ray
2 12226 1/18/2011 Steve

and so on - thanks for helping

[Rabbit]

You could use ranking subquery to get the 1-10, if it's necessary. If the ranking is only for the purpose of selecting a random sample, then you don't need it. If it's needed though for a different purpose, then there's plenty of threads on these forums about ranking records.

As for the random sample, you could use an update query that sets a field using Rnd() seeded with a unique ID and time. Then you could use a query that will select from those records using a subquery. The subquery would return the top # of IDs sorted by the randomized column.

[ADezii]

You can easily create a Query that will generate a Random Number of Records, as a Percentage of Total Records, for a given Date.

1. Create a Calculated Field called [Random]. This Field will call a Public Function, and pass to it the [Number] Field. You will also sort on this Calculated Field (Ascending). The [Number] Field will not be used within the Function, but is required.
2. Function Definition:
   

   Public Function fGenerateRndNum(sngDummy As Long) As Single
   'The Parameter sngDummy is required but not used
   Randomize
   
   fGenerateRndNum = Rnd()
   End Function

3. Create a Query consisting of the [Number], [Date], [Person], and [Random] Fields. Hide the Random Field since it has already done its job, and is no longer needed.
4. Set the Top Values Property of the Query to 20%.
5. Set the Date Criteria to #1/19/2011#.
6. Query Definition:
   

   SELECT TOP 20 PERCENT tblTest.Number, tblTest.Date, tblTest.Person
   FROM tblTest
   WHERE tblTest.Date=#1/19/2011#
   ORDER BY fGenerateRndNum([Number]);

7. Execute the Query.
8. Sample results from three Trial Runs.
   

   Number   Date	      Person
   99174	1 /19/2011	Tom
   62003	1 /19/2011	Bob

   Number   Date	      Person
   72005	1 /19/2011	Ray
   99174	1 /19/2011	Tom

   Number   Date	      Person
   49009	1 /19/2011	Bob
   72005	1 /19/2011	Ray

9. Youo can now convert the Query to a Make Table Query, and execute it once for each Unique Date.