Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    London, UK
    Posts
    1

    Question Unanswered: Too many records in my table

    Hi,

    Feeling my way round Access and getting sshhttuuuck...
    I have a transaction table which contains details of (suprise) transactions. Currently I have over 1 million records worth of transactions covering the year 2003 and finding queries tough going... what should I do?
    - Should I split the transactions table into smaller tables (eg: monthly)?
    - If I do this, how do I query all tables (eg: query customer A's activities over the last year?)
    - any other ideas?

    Your guidance would be warmly appreciated.

    Grant

  2. #2
    Join Date
    Oct 2003
    Posts
    706
    I do not think that your active tables should be split in this way. All of the records which you routinely need for daily processing should be in one table.

    However, it is reasonable to siphon off older data periodically and to place it in an archive table.

    It is also reasonable to look for opportunities whereby detail data beyond a certain age is replaced by a summary record.

    As "big" as "millions" of records may sound, to a computer database this is actually not a large number. AFAIK, Access has no particular concerns with it, as long as the records you need to select can be efficiently fetched using indexes. Keep the filesize well under 2 gigabytes.

    This archiving process ought to be part of the regular, carefully-choreographed and faithfully-executed backup process that you perform against the data every day without fail to ensure that it is intact and recoverable. Generally, backup tables are placed in separate MDB-files.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85

    Re: Too many records in my table

    Originally posted by glemke
    Hi,

    Feeling my way round Access and getting sshhttuuuck...
    I have a transaction table which contains details of (suprise) transactions. Currently I have over 1 million records worth of transactions covering the year 2003 and finding queries tough going... what should I do?
    - Should I split the transactions table into smaller tables (eg: monthly)?
    - If I do this, how do I query all tables (eg: query customer A's activities over the last year?)
    - any other ideas?

    Your guidance would be warmly appreciated.

    Grant
    There is a tool which can compress your database on a scheduled base.
    look at www.peterssoftware.com

Posting Permissions

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