Selecting records at random from main table

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

Guest

Hi,

I have a mailing list in a table and I want to create a new table of 10,000
records by pulling out every 10th record from the main list. Can someone
tell me how to do this? I've not found the Access Help guide of much
assistance.
 
Dear Moki:

There could be a difference in interpreting "every tenth record" compared to
"10% of all records."

In order to choose "every tenth record" you would need to supply some way of
creating an ordering of the records. Then, you should choose some number
between 1 and 10 designating which of the first 10 records is the point to
start. You might then choose the third, 13th, 23rd, 33rd, etc.

Choosing 10% of all records could interpreted as a random process that might
produce exactly 10% or which might produce a random selection having a 10%
probability of choosing any given row.

All three selections above are possible. Which do you prefer?

Tom Ellison
 
Tom,

I would like "every tenth record".

thnx

Tom Ellison said:
Dear Moki:

There could be a difference in interpreting "every tenth record" compared to
"10% of all records."

In order to choose "every tenth record" you would need to supply some way of
creating an ordering of the records. Then, you should choose some number
between 1 and 10 designating which of the first 10 records is the point to
start. You might then choose the third, 13th, 23rd, 33rd, etc.

Choosing 10% of all records could interpreted as a random process that might
produce exactly 10% or which might produce a random selection having a 10%
probability of choosing any given row.

All three selections above are possible. Which do you prefer?

Tom Ellison
Click to expand...
 
Dear Moki:

To be able to choose "every tenth record" you must supply a unique ordering
of the table. You have not supplied this. You must also choose at which
record to start: the first, the third, the tenth, etc.

These are essentials of the problem, whether you want a computer to do so,
or if you have a person do the job.

I'll gladly supply some code to do this if you'll answer the specifications.

Tom Ellison
 
Tom,

I don't know what you mean by "unique ordering", but since there are approx
100,000 records total and I want every 10th, then I guess it makes sense to
start with the 10th.
 
By definition, saying the "tenth record" implies that the records are in
some order. For this to be unambiguous, the order must be based on some
unique column, or set of columns.

Otherwise, the records in a table are considered to be in a "bag," meaning
they are not in any order at all.

For a query to do what you want, I propose to have it number the records,
divide that number by 10, and choose those whose remainder is some value
0-9. Since the numbering I propose begins with 0, I now understand you want
those with a remainder of 9. But, how to number the rows I do not know.

On what basis do you know which record is the first one? The second one?
Without some definition, they aren't in any order at all. They're just in a
bag with no sequence whatsoever. You must impose a sequence on them with
some ordering. That ordering must be unique, or the query cannot assign a
sequence numbering to them in order to be able to perform the division and
obtain the remainder.

Tom Ellison
 
Tom,

I re-imported the database into Access and added and ID field which is
unique to each record. Does that help?
 
Dear Moki:

Well, it may help.

You say you want every tenth record. If you don't care which records you
get, or if the order in which they were imported determines the sequence
from which to select every tenth record, then this may be just what you
want.

If you make sure the new identity column starts from 1, then you can just
filter:

WHERE ID MOD 10 = 0

Does that do it?

Tom Ellison
 
Back
Top