MS Access Need advice if there is a mechanism that records snapshots of the database

I was asked if there was a way to go back in time to produce a status report at the end of the fiscal year.

My Access product tracks projects and instead of creating a report on the day at the end of the fiscal year - is there a way to create this report at a later date - a snap shot of the database data to produce an accurate report for the past?

Problem:
Projects status will change and if I produce a report for the fiscal year a couple of weeks later- I may receive projects with a status of complete instead of working.
DJPr0Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
If you need that functionality, then you need to change your Access project to archive those aspects that you want to be able to query on.

For example, instead of having a [Status] field in your projects table, you might need a [Project_Status] table that contains fields:

ProjectID
Effective_Date
Status

This way, you could create a query that displays the status of each project as of a particular date.

SELECT PS1.ProjectID, PS1.Effective_Date, PS1.Status
FROM tbl_Project_Status
INNER JOIN (SELECT ProjectID, Max(Effective_Date) as MaxDate
                      FROM tbl_Project_Status
                      WHERE Effective_Date <= [Enter some date]
                      GROUP BY ProjectID) as PS2
ON PS1.ProjectID = PS2.ProjectID AND PS1.Effective_Date = PS2.MaxDate

You would need to make these types of changes for all of the fields that you would want to track a value for as of a particular date.  This might include you Project_Employees table, which tracks which employees are assigned to a project on a particular date.  I'm sure there are other fields like this but without more information about your application, these are just hypothetical examples.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<My Access product tracks projects and instead of creating a report on the day at the end of the fiscal year - is there a way to create this report at a later date - a snap shot of the database data to produce an accurate report for the past?>>

  No.    There are a couple of ways to attack this.   One is the suggestion above by Dale.

  A variant of that is an audit log table.   This is an approach that I've used in the past with HR systems.

  What you do is record every change to every field of a record and date/time stamp it.

  When you want to see a record as of a certain date, you query the audit table searching for the last change for each field up to and including your "as of" date.

  You then take a copy of the current record and update it with these changes (in a temp table).  

 You now have the record as it stood as of a specific date.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I should add that the other choice, which is along the lines of what Dale suggested is to copy the entire record everytime it changes and log that to an audit table.

Jim.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DJPr0Author Commented:
Thanks guys!

Fyed - Do I need a function to fill in the dates for the Effective_Date field? I.E. If no status was entered on that day - auto fill today's date.

[Project_Status] table that contains fields:
ProjectID
Effective_Date
Status
0
Dale FyeCommented:
I would generally:

1.  Have some code in the Before Update event of the form that would evaluate whether the value of the [Status] field has changed (Status.Value and Status.OldValue), and if so I would automatically insert a new record in the table with the current date as the Effective Date.

2.  Alternately, you could include both Eff_Date and Status on your form and allow the user to actually enter the effective date.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
In regards to audit logs in general, you should give this a read:

http://allenbrowne.com/AppAudit.html

That would help with #1 in the last comment.

Jim.
0
DJPr0Author Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.