H
hcj
Here's a concept:
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.
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....
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....