Automatic Archiving for historical comparisons w/ data

[sdr4]

My database consists of information which is updated every quarter.
Forms will compare data from the present quarter to quarters past. I
need a way to make the database save a copy of itself every quarter and
then be able to have a form refer to that data through a comparison.
The historical data set would be created at the end of each quarter and
lock in all the values from that quarter. For example this quarter
(1st quarter 2006) I would need to refer to 1st quarter 2005 for a
comparison. Obviously a set of data for 1st quarter 2005 hasn't been
created yet so I would have to put data in manually but eventually once
the data was populated I would want it to do the comparison
automatically.

I realize this is an involved problem but if anyone could just give me
a general suggestion to get me moving in the right direction I would
really be grateful!!

 

[Guest]

Rather than an historical copy of your database, you could do it more easily
with an additional table. In that table, you would want the structure to be
exactly like the table for your current data plus a field that will identify
the year and quarter to which it belongs.

Then when you are ready to close out a quarter, run and append query that
will copy all the records in the current data table to the history data table
and populate the year/quarter field(s). Then, run a delete query to remove
the data from the current table. You will need to to this for each table
that has to be cleared at the end of the quarter.

To do comparisons between current and historical, your queries would have to
filter the data in the historical tables on the year/period you want to look
at.

 

[deltauser2006]

Wow...thanks, that's really helpful. In the append query, how do you
set up the population of data in the year and quarter fields? Thanks
so much for your help....sorry about the crossposting I was just hoping
to get as much help as I could. Thanks again.

 

[Guest]

Include the Year and Quarter fields in your query. Then in the Update To
row, put the year and quarter. Of course, you don't want to have to change
the query each quarter, so in a case like this, I would create a small form
that would ask the user for the year and quarter and use the controls on the
form as inputs for the query parameters. So in the Update to you would do
Like:

Forms!frmQuaterUpdate!txtYear

Have a command button the runs the query from the form, then the query can
pick up the values from the controls on the form.