Results 1 to 2 of 2

Thread: Using Top Values to export a range of records
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2011
    Location
    Raleigh, NC
    Posts
    682
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Using Top Values to export a range of records

    I have a very large table of 3 million records that I need to export out of ACCDB in chunks of 500k records at a time.

    They are sorted in a particular order so their Primary IDs are not sequential.

    I can easily export out the first 500k by setting the Top Values property to 500,000. Is there a way to then set the range to 500,001 to 1,000,000?

    THanks,

    Justin
    60% of the time it works every time.

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,337
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Using Top Values to export a range of records

    Not tested (i rarely use top in Access) but should work like this

    select top (500000) * from Table where SomeID < 500000 order by SomeID
    select top (500000) * from Table where SomeID >= 500000 and SomeID < 1000000 order by SomeID
    select top (500000) * from Table where SomeID >= 1000000 and SomeID < 15000000 order by SomeID

    If the IDs are not really sequential then you can't be sure of getting exactly 500,000 per chunk.
    If you need to get exactly 500,000 per chunk then you have to store the ID's of what is exported so you can start the next batch from the same place.

    Probably not ideal. Best to test your algorithm on a small table with say 10 chunks of 10, with only 100 records (more or less).

    Another possibility - export the relevant IDs into a temporary-ish table, partition them into the right number of groups, and use that table to control the exports. This would provide a better audit and probably a better way of ensuring reliability (which can also be helped by recording what is exporting and validating the counts).

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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