select every 100th records

[pdehner]

I have numerous tables in one database that I must select records to send a
survey to. I would like to select every 100th record an export results to an
excel spreadsheet. I am drawing a blank on how to accomplish this.
Any help is greatly appreciated.

 

[Duane Hookom]

Why every 100th? This assumes the records are in some basic sort order that
the 100th record can be determined.

Would random work better? Perhaps you could determine the number of records
and grab a random sampling of 1%. There are a number of good examples of
selecting random records if you search this news group or with your favorite
search engine.

 

[pdehner]

I have searched and found the rnd() function you refer to. I am trying
unsuccessfully to run on a query. I have 5 tables that I need to first query
and return only those records that are not null in a specified field. Then I
need to take 10% of those records and export to excel. Must I create a table
to pull the 10% for? I can't seem to get the code to work for a query.

Please advise.

 

[KARL DEWEY]

I have searched and found the rnd() function you refer to. I am trying
unsuccessfully to run on a query.
What is it doing wrong or what is it not doing? Post the query SQL by
opening in design view, click on VIEW - SQL View, highlight all, copy, and
paste in a post.
Use a union query to pull the 5 table together.

 

[Duane Hookom]

A typical query for 10% of the records in the Orders table of Northwinds
would be:

SELECT TOP 10 PERCENT Rnd([OrderID]) AS Expr1, Orders.*
FROM Orders
ORDER BY Rnd([OrderID]);

 

[KenSheridan via AccessMonster.com]

If you want every 100th row in a particular sort order rather than a random
10% returned this should work with a query providing the sort order is on a
column with unique values. The rows returned will start at the 100th row in
the sort order:

SELECT *
FROM YourQuery AS Q1
WHERE EXISTS
(SELECT MAX(YourID)
FROM YourQuery AS Q2
WHERE Q1.YourID >= Q2.YourID
HAVING COUNT(*) MOD 100 = 0)
ORDER BY YourID;

where YourQuery is the query name and YourID is the uniquely valued column.
If you also want the first row in the sort order returned then extend it to:

SELECT *
FROM YourQuery AS Q1
WHERE EXISTS
(SELECT MAX(YourID)
FROM YourQuery AS Q2
WHERE Q1.YourID >= Q2.YourID
HAVING COUNT(*) MOD 100 = 0)
OR YourID =
(SELECT MIN(YourID)
FROM YourQuery)
ORDER BY YourID;

Ken Sheridan
Stafford, England

I have searched and found the rnd() function you refer to. I am trying
unsuccessfully to run on a query. I have 5 tables that I need to first query
and return only those records that are not null in a specified field. Then I
need to take 10% of those records and export to excel. Must I create a table
to pull the 10% for? I can't seem to get the code to work for a query.

Please advise.

Why every 100th? This assumes the records are in some basic sort order that
the 100th record can be determined.

[quoted text clipped - 8 lines]