Results 1 to 5 of 5
  1. #1
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44

    Using my DB to "stack" similar sets of data over time and analyze how it changes

    Greetings,



    I'm exploring the possibility of creating an Access DB that would serve as a place to upload and analyze monthly reports from various sources. Currently, I am using an Excel Spreadsheet that does what I want, but there is a lot of legwork involved when the once-a-month reporting period comes around.

    In Excel, most of the time I delete the old data from last month and paste brand new data from the current month and analyze only that.

    My question is, can I create an Access DB where I upload the reports that I run every month, but instead of trashing the old data, I stack it month after month after month and analyze how it changes over time?

    With regards to the data I'm pulling, it's mostly human resources type stuff, so lots of names and dates that don't really change month to month. So, with that, my assumption is I would have to have some mechanism in my database to recognize each record's place in time (or when each record was uploaded) so that (1) the most current data is displayed on my reports and (2) I have a benchmark to analyze trends.

    Apologies in advance if this is not enough to work with, but I appreciate any advice regarding this.

  2. #2
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Of course it can be done but it would be advisable to have two Excel files available, of two consecutive months, to be able to see directly how the data are managed and therefore how to behave in processing when imported into Access.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    it can be done, but databases do not work like excel so if you are going to use it for your analysis you will need to normalise the data.

    I would have to have some mechanism in my database to recognize each record's place in time (or when each record was uploaded)
    easily achieved, just include a timestamp field ( a date field with a default value of now() if you need a time element or date() if you don't. You may also need an 'effective date' field - If you load data for January in the middle of February the effective date would be January, the timestamp would be February. You will also need a PK field for each table

    You also need to consider if data doesn't change much whether as part of the import you compare this months data with last month - if it hasn't changed, do you need to import it again?

  4. #4
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44
    Thank you for the reply. There are slight changes in the data that, if they were accounted for, it could offer a great benefit. The majority of the time, the entire row will not change, though. The name, job title, and certain dates will remain constant over time. However, they someone may have received a promotion recently, or maybe they have a new code associated with their record indicating an issue.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you need to compare each field to see if it has changed. This can make for a long query with lots of OR criteria. If this is the case consider using a hash function to hash the data and store that as well.

    Not sure what you are expecting as an answer, you've asked a general question about 'can it be done' to which the answer is yes. Each application is different so you use the appropriate tools as required. If you require specific help, provide some example data and what you want to do with it

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

Similar Threads

  1. Replies: 4
    Last Post: 07-12-2018, 05:13 PM
  2. Replies: 1
    Last Post: 05-22-2018, 01:54 PM
  3. Replies: 4
    Last Post: 01-17-2018, 03:29 PM
  4. Replies: 7
    Last Post: 12-21-2014, 08:21 PM
  5. Replies: 2
    Last Post: 05-25-2012, 11:31 AM

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