| | |
|
|
|
Choose random records from table |
|---|
| message from hcj on 11 May 2004 |
1. Build a query to pull records. Add a field to the query
like Randstring: =mid([textfield],M,N), where [textfield]
is one of the fields you're pulling, M=some starting
character position, and N= some ending character position.
M and N should be about 4-5 digits apart (more is better),
but need to fit within the shortest length of any expected
value of [textfield]. In this way you'll have some random
string of text extracted from each record. Sort the query
output on the Randstring field.
2. Next, build a report using the query as a recordsource.
Use the Randstring field you generate in the query as the
sort control for the report. Add an accumulating counter,
initialized at 1, in the report. Test the value of the
counter as you loop through the records when you run the
report. When it reaches 5000, stop the report. Or...
2. If you want to keep the recordset from the query, make
the query a Make Table query, still sorting on Randstring.
Make your table and delete all but the first 5000 records.
This should work fairly well if the [textfield] you choose
has fairly heterogenous text in M,N character range.
ALTERNATE CONCEPT
If you need to do this just once, or infrequently, you can
export the records to Excel, add a column containing the
RAND() function for each row, and sort the records on that
column. Then delete all but the first 5000 records. Each
time you sort, the RAND() function will recompute, so you
could sort (="shuffle") a few times before selecting the
first 5000 records. Those could be imported back into
Access if you want to keep them in the data base.
Just some ideas. Hope they help. Post back if this is
confusing and needs more discussion.
and Date types and no primary keys... But I need 5000
random records from that table. Can anyone give me any idea
from the start about how to pull out 5000 random records
from the table? Thanks very much for any suggestions and
your time! Waiting....
|
| =?Utf-8?B?QWxseSBDaGFuZw==?= replied to hcj on 12 May 2004 |
Hi, HCJ,
Thanks for your response. I was not able to see it from my computer until a moment ago.
I did your first step: create a query based on the table by adding a field like Randstring: = Mid(ID, 4, 9) to the query. There are 13,000 records with part of ID coming back when running the query.... Then I was trying to create a report based on the query by using Wizard, and choose Sort Ascending the Randstring... I am pretty new with Access. As you can see, I don't know how to add an accumulating counter in the report and where do I initializ it at 1? Then what should I do? By the way I not only need pull 5000 records from this table but also keep all the information (Not just one field) in the recordset.
Thanks!
|
|
Archived message: Choose random records from table (Microsoft Access Database)
