Results 1 to 9 of 9
  1. #1
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23

    Year end - what do you do with the previous year(s) data?

    I'm writing a simplistic accounting and point of sale application, which is going pretty well. I'm having a hard time deciding what to do when rolling over to a new year. There won't be a huge number of records (and MS-Dos doesn't need more than 640k right?).

    I can think of a few options.

    Keep all data in the tables and set the defaults to only look at the current year with options to look at previous years.

    I could detach the data and store previous years in separate tables. In other words, instead of storing customer ID and retrieving the name from a customers table just store the actual name in the record. The idea being that changes to the active database tables can't alter this historical data. That still allows looking at previous data inside the same application though it'll be slower since lookups would be text or value based rather than a simple numerical index. Keeps the active tables small and fast.

    I could also just make a copy of the database as a backup then use an empty database for the new year.



    Any other ideas or preference out of the above?

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Keep all data in the tables and set the defaults to only look at the current year with options to look at previous years.
    this seems to make the most sense. All reports should be based upon queries and you can then specify the year.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Agree with Alan
    Do not archive your data into separate tables or a separate application. Its creating additional maintenance work for little or no benefit.
    I've been down that route in the past and its almost always a mistake.
    Using queries and specifying the year will be fast and just as effective.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Separate files for every year is bad idea. Do you preserve all data or do you archive them depends on amount of data, and your needs for detailed data.

    For accounting data, there usually is some number of years determined legally you must have a detailed info about all transactions, orders, invoices, etc. For this period, it will be best to keep all info in tables as it was entered originally. What you do with older data, mostly depends on what you needs are. Practically you have 3 options:
    1. You keep all data as they are;
    2. You keep some generalized info in archive tables, or in archive app. Usually some specific forms or app is used to get/report data from archived years;
    3. You generalize old data in your database. E.g. You have tables for OrderHeaders and OrderRows. For year to archive, for every month of year, and supplier[, and part] you generate a specific archive row in OrderHeaders table (the table must have fields for all needed totals)[, and in OrderRows table], and then you delete all earlier info from both OrderHeaders and OrderRows tables. You can then get working reports about all your purchases for archived year, but not on order level anymore.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    There won't be a huge number of records (and MS-Dos doesn't need more than 640k right?).
    don't know where you got MSDos/640k from, you can store up to 2Gb in an access database, 10Gb in sql server express, more in sql server, mySql etc

    With few records, per other responses
    Keep all data in the tables and set the defaults to only look at the current year with options to look at previous years.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree --- seems Keep all data in the tables and set the defaults to only look at the current year with options to look at previous years. is unanimous.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I'd still say this even where there are many records...as long as the fields used for searches are indexed as the search time will be almost identical whether there are 10 records or 10 million. Without using indexes, searches will be very slow with many records.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23
    Quote Originally Posted by Ajax View Post
    [COLOR=#333333] don't know where you got MSDos/640k from, you can store up to 2Gb in an access database, 10Gb in sql server express, more in sql server, mySql etc

    With few records, per other responses
    Lol, it's an old reference. MS-Dos couldn't use more than 640kilobytes of memory and Gates said that's more than enough. Then it wasn't. Had to use memory managers and modify the config.sys and autoexec.bat to make them work but it left ram between 640k and 1meg unusable for regular programs so even more startup tweeking had you shoehorning device drivers in there.

    It was a famous last words kind of thing. Just like my saying "There won't be a ton of records".

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Bill Gates also once said
    The Internet is just a passing fad
    and
    The Internet. We're not interested in it
    Times change
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-19-2017, 01:09 PM
  2. Replies: 10
    Last Post: 02-20-2017, 06:00 PM
  3. Replies: 3
    Last Post: 06-13-2016, 03:26 PM
  4. Previous Year Data Query Expression
    By NotReese in forum Queries
    Replies: 4
    Last Post: 11-22-2013, 04:54 PM
  5. Dsum data between to dates for this year and last year
    By sdel_nevo in forum Programming
    Replies: 1
    Last Post: 06-13-2013, 06:48 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums