1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Retrive nth record using access query

Discussion in 'Business Applications' started by auditguy, Nov 4, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. auditguy

    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.
     
  2. OBP

    OBP

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

    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.
     
  4. OBP

    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.
     
  5. auditguy

    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.
     
  6. OBP

    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?
     
  7. auditguy

    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.
     
  8. OBP

    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?
     
  9. auditguy

    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.
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Is the DataCode Unique or are there duplicates?
     
  11. OBP

    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.
     

    Attached Files:

  12. Sponsor

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 733,556 other people just like you!

Loading...
Similar Threads - Retrive record using
  1. jamesb2
    Replies:
    1
    Views:
    343
  2. jamesb2
    Replies:
    6
    Views:
    455
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1025416

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice