How do I NTH select in MSAccess 2003?

[ja]

Hello all,

How can I ramdomly select a list of records from an existing table?

For example: 500,000 names divided by 20,000 samples = 25. The N is
25, meaning samples would be sent to every 25th name on the list.

Any help will be appreciated?

TIA

ja

[Bob Quintal]

ja <jo******@sbcglobal.net> wrote in
news:2v********************************@4ax.com:

Hello all,

How can I ramdomly select a list of records from an existing
table?

For example: 500,000 names divided by 20,000 samples = 25. The
N is 25, meaning samples would be sent to every 25th name on
the list.

Any help will be appreciated?

TIA

ja

If you have a numeric key in the table, use the Modulo operator

SELECT * from mytable where ([NumField] mod 25) = 25;

You'll get every 25th record if NumField contains consecutive
numbers.
--
Bob Quintal

PA is y I've altered my email address.

[Br]

Bob Quintal wrote:

ja <jo******@sbcglobal.net> wrote in
news:2v********************************@4ax.com:

Hello all,

How can I ramdomly select a list of records from an existing
table?

For example: 500,000 names divided by 20,000 samples = 25. The
N is 25, meaning samples would be sent to every 25th name on
the list.

Any help will be appreciated?

TIA

ja

If you have a numeric key in the table, use the Modulo operator

SELECT * from mytable where ([NumField] mod 25) = 25;

Shouldn't that be [field] mod 25 = 0 ?
You'll get every 25th record if NumField contains consecutive
numbers.

Or create a row counter in a query so it is guaranteed to be sequential....

SELECT A1.Personnel_no, A1.Name, (SELECT COUNT(*) FROM tblEmployee A2 WHERE
A2.NAME <= A1.NAME) AS RowNumber
FROM tblEmployee AS A1
WHERE ((((SELECT COUNT(*) FROM tblEmployee A2 WHERE A2.NAME <= A1.NAME) Mod
25)=0))
ORDER BY A1.Name;
--
regards,

Br@dley

[Anthony England]

"Br@dley" <[e-mail]> wrote in message
news:dt**********@news-02.connect.com.au...

Bob Quintal wrote:

ja <jo******@sbcglobal.net> wrote in
news:2v********************************@4ax.com:

Hello all,

How can I ramdomly select a list of records from an existing
table?

For example: 500,000 names divided by 20,000 samples = 25. The
N is 25, meaning samples would be sent to every 25th name on
the list.

Any help will be appreciated?

TIA

ja

If you have a numeric key in the table, use the Modulo operator

SELECT * from mytable where ([NumField] mod 25) = 25;

Shouldn't that be [field] mod 25 = 0 ?

You'll get every 25th record if NumField contains consecutive
numbers.

Or create a row counter in a query so it is guaranteed to be
sequential....

SELECT A1.Personnel_no, A1.Name, (SELECT COUNT(*) FROM tblEmployee A2
WHERE A2.NAME <= A1.NAME) AS RowNumber
FROM tblEmployee AS A1
WHERE ((((SELECT COUNT(*) FROM tblEmployee A2 WHERE A2.NAME <= A1.NAME)
Mod 25)=0))
ORDER BY A1.Name;
--
regards,

Br@dley

Two potential problems here are that with 500,000 names (as the OP says)
this is not going to be fast - I could imagine it would pretty much grind to
a halt. Secondly, if you are ordering the people alphabetically and
selecting in any kind of structured way, then the results cannot be said to
be random.
Now I'm not a mathematician, and don't want to enter a huge debate on what
is random, but wouldn't this be better?
SELECT TOP 20000 * FROM People ORDER BY Rnd([ID])

[Br]

Anthony England wrote:

"Br@dley" <[e-mail]> wrote in message
news:dt**********@news-02.connect.com.au...

Bob Quintal wrote:

ja <jo******@sbcglobal.net> wrote in
news:2v********************************@4ax.com:

Hello all,

How can I ramdomly select a list of records from an existing
table?

For example: 500,000 names divided by 20,000 samples = 25. The
N is 25, meaning samples would be sent to every 25th name on
the list.

Any help will be appreciated?

TIA

ja
If you have a numeric key in the table, use the Modulo operator

SELECT * from mytable where ([NumField] mod 25) = 25;
Shouldn't that be [field] mod 25 = 0 ?

You'll get every 25th record if NumField contains consecutive
numbers.

Or create a row counter in a query so it is guaranteed to be
sequential....

SELECT A1.Personnel_no, A1.Name, (SELECT COUNT(*) FROM tblEmployee A2
WHERE A2.NAME <= A1.NAME) AS RowNumber
FROM tblEmployee AS A1
WHERE ((((SELECT COUNT(*) FROM tblEmployee A2 WHERE A2.NAME <=
A1.NAME) Mod 25)=0))
ORDER BY A1.Name;
--
regards,

Br@dley

Two potential problems here are that with 500,000 names (as the OP
says) this is not going to be fast - I could imagine it would pretty
much grind to a halt.
Sure. There are lots of ways to skin a cat. The most elegant/complex of SQL
statements may just be too slow realistically.
Secondly, if you are ordering the people
alphabetically and selecting in any kind of structured way, then the
results cannot be said to be random.
Ah, I missed the random part and only saw the second part (and the subject)
where he stated he wanted every 25th record...
Now I'm not a mathematician, and don't want to enter a huge debate on
what is random, but wouldn't this be better?
SELECT TOP 20000 * FROM People ORDER BY Rnd([ID])

Yep, that'd be the easiest way to get a random sample.

(I like doing things dynamically so I'd probably pass a parameter such as
the percentage or number of records I wanted returned).

Thanks for pointing that out.
--
regards,

Br@dley