Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Querying every nth record (Access 2000)

    I have a table of about 500 records, and I want to query every 20th record for a study. Then after it runs the query, which should result in 25 records, I want the query to be placed as a table in a new database in Access. Any suggestions will be greatly appreciated.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Querying every nth record (Access 2000)

    Hi Stephen,
    Access tables don't really have a record order as such - how would you classify every 20th record? Do you just want a random sampling effectively or do you want every 20th based on a particular order? If so, does your table have an autonumber field you could use?
    You might want to do a search in this forum - I'm sure I have seen posts on this topic before.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying every nth record (Access 2000)

    <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=85558&page=& view=&sb=&o=&vc=1#Post85558>This post refers to the nth record from the point of view of finding a random record. You may or may not find it applicable.
    </A>

    As for using an auto-number field, you could run in to problems using an auto-number field if the 20th record was deleted for some reason. You could get around this problem by generating a new auto number field before running your query.

  4. #4
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying every nth record (Access 2000)

    Thanks, Rupert. I'm having to take the weekend to work this out, but your suggestions will make sure that I'll be done by noon Saturday--instead of 12:00 AM Sunday.

    Again, thanks!

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying every nth record (Access 2000)

    I

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying every nth record (Access 2000)

    I realize that Ruper's suggestion may be a winner for you, but I have another idea (just in case):
    Temporarily add a Boolean (Yes/No) field to the table. Using a module, create a loop that marks every 20th record True. Then just use a Make-Table query to catch every checked record. The code to mark the records would be something like:<pre>Sub MarkRecords()
    Dim db as DAO.Database
    Dim rst as DAO.Recordset
    Dim fld as DAO.Field

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tbl_YourTable", dbOpenDynaset)
    Set fld = rst.Fields("blCheckField")

    Do Until rst.EOF
    fld.Value = True
    rst.Move rst.AbsolutePosition + 20
    Loop

    rst.Close
    Set rst = Nothing
    Set db = Nothing
    End Sub</pre>

    This assumes the records are in the desired order (back to Rupert's suggestion about the AutoNumber field)...Good luck!
    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •