How do I NTH select in MSAccess 2003?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ja - 17 Feb 2006 01:15 GMT

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

Reply to this Message

Bob Quintal - 17 Feb 2006 01:45 GMT

> Hello all,
>
[quoted text clipped - 10 lines]
>
> 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.

Signature

Bob Quintal

PA is y I've altered my email address.

Reply to this Message

Br@dley - 17 Feb 2006 02:24 GMT

>> Hello all,
>>
[quoted text clipped - 14 lines]
>
> 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;

Signature

regards,

Br@dley

Reply to this Message

Anthony England - 17 Feb 2006 09:39 GMT

>>> Hello all,
>>>
[quoted text clipped - 29 lines]
> Mod 25)=0))
> ORDER BY A1.Name;

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])

Reply to this Message

Br@dley - 17 Feb 2006 09:46 GMT

>>>> Hello all,
>>>>
[quoted text clipped - 33 lines]
>>
>> 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.

Signature

regards,

Br@dley

Reply to this Message