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