Archiving Data by Quarter - Best Solution?

[warren0127]

Hi All,

I have an access DB with different forms, queries, reports, etc. Each quarter, we are expected to update all of the data in the database, and then "backup" the data before we make the new changes. I use primary and foreign keys that are connected throughout most tables forming relationships with each. My main requirement is to be able to archive data/backup data from the tables so we can go back in time and see what was provided in Q1, Q2, Q3, and Q4.

So, I need to be able to backup data by quarter, and be able to, for example, be able to pull up the data from "Last Quarter" and compare it against the current data to see what has changed.

I tried to use an append query for to archive the tables to a set of "archived tables" copying structure only, but I dont think it is the right approach. Because when I try to append and run the query again, it gives me all kinds of errors. I also am not sure if an update query is the right solution, because I do not want to "change" any of the data or fields.. I simply want to "see a snapshot" of the old data to see the changes in between quarters.



What do you think I should do, and what is the best approach to this? All I want to do is to simply compare data by the previous quarter (How do I capture data quarter by quarter and go back to it and compare it against the current data to see what has changed?)

PS: I am using linked tables that are integrated into SharePoint lists (linking the access DB to SP). So, I am not using stand alone tables that a normal DB uses. Not sure if it matters, but thought I'd mention that!

Thanks!!
- Warren

[June7]

Ideally, this 'archive' would not be necessary. Saving calculated data, especially aggregate data, is usually a bad idea. Should be able to regenerate the summary data for any given period no matter how old. Archiving should only be considered if the Access file would exceed 2GB size limit.

Can construct a field in query that calculates the quarter identifier. How do you designate quarters - Jan thru Mar is Qtr1, Apr thru Jun is Qtr2, Jul thru Sep is Qtr3, Oct thru Dec is Qtr4 - or do you use a different fiscal year (Jul thru Jun)?

No experience with SharePoint, sorry.

[warren0127]

Thanks June,

I guess I used the wrong word - maybe 'archiving' is not what I want to really achieve here. I would just like to go back in time, 3 months back, to see if that "project name" changed, or their "Start/end dates" changed, etc, by comparing the current set of data that exists today - kind of like going back and looking at a snapshot of what the data looked like 3 months ago. I am not sure which approach to go and what the easiest option is for this. I would assume just baseline the entire DB, saving versions by quarter perhaps, but would be nice if I could pull everything up within the database itself, rather than having to open multiple instances of snapshots.

We designate quarters by the following: 1st quarter: 1 October 2016 – 31 December 2016. 2nd quarter: 1 January 2017 – 31 March 2017. 3rd quarter: 1 April 2017 – 30 June 2017. 4th quarter: 1 July 2017 – 30 September 2017.

Appreciate your help.

[Micron]

I would just like to go back in time, 3 months back, to see if that "project name" changed, or their "Start/end dates" changed, etc,

This could mean you simply only want to know if there was a change, in which case all you need is an UpdateDate. Those that are Null were never changed. Based on the rest of your post, I'd say you want to know what the changes were as well. My suggestion would be something like tblHistory. These records would only contain the fields that you are concerned about, which is probably not all of them. You'd write any record edits to this table. Say you only wanted to know if ProjectName or StartDate changed. Write the parent table record ID to this table along with the new values. You'd then be able to get the rest of the fields from the parent table (such as who created the initial record) plus a record from the history table for each change, including who made the edit. Someone may very well have a better idea, but that's mine, for what it's worth.

[June7]

If you want to track what was changed and when, review http://allenbrowne.com/AppAudit.html

A project name and dates can change and it's still the same project? So you have unique project identifier aside from an autonumber record ID?

[warren0127]

This could mean you simply only want to know if there was a change, in which case all you need is an UpdateDate. Those that are Null were never changed. Based on the rest of your post, I'd say you want to know what the changes were as well. My suggestion would be something like tblHistory. These records would only contain the fields that you are concerned about, which is probably not all of them. You'd write any record edits to this table. Say you only wanted to know if ProjectName or StartDate changed. Write the parent table record ID to this table along with the new values. You'd then be able to get the rest of the fields from the parent table (such as who created the initial record) plus a record from the history table for each change, including who made the edit. Someone may very well have a better idea, but that's mine, for what it's worth.

Yep - would def be more of what the changes were. I like your idea, but am not sure how I could do it. How could I capture the historical change between the quarter into the history table, or writing the record edits?

If you want to track what was changed and when, review http://allenbrowne.com/AppAudit.html

A project name and dates can change and it's still the same project? So you have unique project identifier aside from an autonumber record ID?

Would this solution allow me to see what exactly changed? So.. as far as project names and dates changing, may not be that likely. It would be more like, last quarter project 1 said it was going to do X Y and Z deliverables. But now, that may have changed to B, C and D deliverables. This is the type of change data I need to be able to look at - going back in time to see what they said they were going to do is still what they said they are going to do "today".

[Micron]

I like your idea, but am not sure how I could do it.

Me neither to be exact. Problem is not being able to see much of your db structure or relationships, so it can only be discussed in general terms. I'm assuming that at first, you open a form based on user's choice to either create or edit a record. This might be a switchboard type of form.

If edit, you need a way to identify which record (which should have a PK field). Is that a listbox on the form showing some key data such as a contract number? Or maybe the form is opened showing an entire recordset using record navigation controls, or perhaps a form-subform relationship. All the data input controls are disabled either way because you want to control the edit process. Maybe then there's a button to start the edit of the chosen record, in which case you append a copy of this record to the temp table. You could start this edit by closing this form and opening another one whose record source is set to the temp (the first form record source being the primary table), but my approach is to minimize duplication of forms and reports. Thus I would alter my first (and only) form's record source to be the temp table by way of code, requery it and enable the controls. Once the edit is done, run an Update query to overwrite all the primary table fields with the temp values.

If the choice is from the switchboard is to create a new record, you open same main form with all the controls enabled (would happen by default since you'd design it that way) but set the DataEntry property to True. Form data/record properties can be mutually exclusive or dependant, so you might want to research those a bit. For example, to do what I just mentioned, the AllowEditions property has to be True. Start with https://msdn.microsoft.com/en-us/vba...roperty-access
Hope that can get you started at least. It can be a bit complicated, depending on your level of Access knowledge.

[June7]

Would this solution allow me to see what exactly changed? So.. as far as project names and dates changing, may not be that likely. It would be more like, last quarter project 1 said it was going to do X Y and Z deliverables. But now, that may have changed to B, C and D deliverables. This is the type of change data I need to be able to look at - going back in time to see what they said they were going to do is still what they said they are going to do "today".

AFAIK, this is what Allen Browne's audit log model would provide.

The alternative is to create a new deliverables record and somehow 'flag' the previous as 'inactive' and in queries pull only the 'active' deliverable records. So you have history and current in one table.

[warren0127]

Me neither to be exact. Problem is not being able to see much of your db structure or relationships, so it can only be discussed in general terms. I'm assuming that at first, you open a form based on user's choice to either create or edit a record. This might be a switchboard type of form.

If edit, you need a way to identify which record (which should have a PK field). Is that a listbox on the form showing some key data such as a contract number? Or maybe the form is opened showing an entire recordset using record navigation controls, or perhaps a form-subform relationship. All the data input controls are disabled either way because you want to control the edit process. Maybe then there's a button to start the edit of the chosen record, in which case you append a copy of this record to the temp table. You could start this edit by closing this form and opening another one whose record source is set to the temp (the first form record source being the primary table), but my approach is to minimize duplication of forms and reports. Thus I would alter my first (and only) form's record source to be the temp table by way of code, requery it and enable the controls. Once the edit is done, run an Update query to overwrite all the primary table fields with the temp values.

If the choice is from the switchboard is to create a new record, you open same main form with all the controls enabled (would happen by default since you'd design it that way) but set the DataEntry property to True. Form data/record properties can be mutually exclusive or dependant, so you might want to research those a bit. For example, to do what I just mentioned, the AllowEditions property has to be True. Start with https://msdn.microsoft.com/en-us/vba...roperty-access
Hope that can get you started at least. It can be a bit complicated, depending on your level of Access knowledge.

Appreciate the info and help on this. I am looking into a few options. There is still a lot for me to learn in access - will be looking into the resource you provided.

AFAIK, this is what Allen Browne's audit log model would provide.

The alternative is to create a new deliverables record and somehow 'flag' the previous as 'inactive' and in queries pull only the 'active' deliverable records. So you have history and current in one table.

This will be a challenge for me, and will be looking into this for the next week or so when I have the time. I found an example someone created of a DB that pretty much has a built in module that tracks changes for any specified field/record - highlighting things in red/blue like you see in MS word. So basically you can see what was inputted originally, and the edits in a separate column side by side in a report. Could be useful to see how it was constructed.