If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
select every 100th records
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. |
#2
|
|||
|
|||
select every 100th records
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. -- Duane Hookom Microsoft Access MVP "pdehner" wrote: 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. |
#3
|
|||
|
|||
select every 100th records
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. "Duane Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "pdehner" wrote: 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. |
#4
|
|||
|
|||
select every 100th records
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. Must I create a table to pull the 10% for? Use a union query to pull the 5 table together. -- Build a little, test a little. "pdehner" wrote: 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. "Duane Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "pdehner" wrote: 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. |
#5
|
|||
|
|||
select every 100th records
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]); -- Duane Hookom Microsoft Access MVP "KARL DEWEY" wrote: 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. Must I create a table to pull the 10% for? Use a union query to pull the 5 table together. -- Build a little, test a little. "pdehner" wrote: 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. "Duane Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "pdehner" wrote: 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. |
#6
|
|||
|
|||
select every 100th records
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 pdehner wrote: 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] excel spreadsheet. I am drawing a blank on how to accomplish this. Any help is greatly appreciated. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200912/1 |
Thread Tools | |
Display Modes | |
|
|