Retrive nth record using access query

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

auditguy

Thread Starter
Joined
Nov 4, 2011
Messages
5
I have a database table that has approx 425,000 - 1 million records. I have a field that I am using for my primary [Number ID]. Even though there is a number in here the data type is set to text. I cannot change the data type nor can I add another column and put that data type as auto number or number.
What I want to do is this:

I need 30 samples. I have 425,000 records so 425,000/30 = 14166 . (Not rounding up just number before decimal) So I need a query that will pull every 14,166th record from the 425,000 records.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Access Queries have a Property for choosing the top "n" records called "To Values".
 

auditguy

Thread Starter
Joined
Nov 4, 2011
Messages
5
I know how to get the top value but that it not what I need. I need to be able to retrieve the specific records based on my sample size so using the top function would not work. I dont know where the records fall at in the dataset or what the records say beyond the fact that sample 1 would be record 14166 ( sample size 30 database size 425000 records, 425000/30 means that I need every 14166 record retrieved) based on being the 14166 record and sample 2 would be record 28332 based on being the 28846 record in the dataset and sample 3 would be record 42498, etc.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
I don't think a query can do that, Jimmy the hand may correct me as he is brilliant with SQL.
I think what you need is VBA code to place the record's numbers in a Temporary table that can be used by the query.
 

auditguy

Thread Starter
Joined
Nov 4, 2011
Messages
5
If VBA can do it, I have no problem trying that. I've tried a couple and nothing has worked. I think the main problem is that the data type is text instead of number.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
I don't think that is the problem, the problem is not having an Autonumber, however now that I think about you can create a row number in a query by using the count function.
How do you decide the how many values you need, on a Form?
 

auditguy

Thread Starter
Joined
Nov 4, 2011
Messages
5
No we just receive it as data. There is no form. The value depends on how many records we get in.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Well I woudl suggest that you create a form with some simple VBA code on it to calculate the positions of the records in the whole recordset to establish which ones you need.
Can you provide any dummy data to work with?
 

auditguy

Thread Starter
Joined
Nov 4, 2011
Messages
5
Data Code – text
Start of Service – date/time
End of Service – date/time

DataCode
StartofService
EndofService

9830291874
4/01/2010
4/05/2010
7847562014
2/19/2009
2/25/2009
6309184756
8/18/2008
8/28/20008
3876520198
1/01/2011
1/5/111










I am attaching as well as putting a sample table in here.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Here is a first attempt, it uses a form where you enter the number of Records that you want from the total.
The VBA code then deletes the current records in the ReportTable.
It opens the query of the table to be searched (table1 in this case) and provides the count of records.
It then steps through the records identifying the DataCode number that represents the nth record and appends it to the ReportTable.
It then opens the query showing you the data for those records.
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top