Automatic Historical Archive of Data for Comparison

[deltauser2006]

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!!

 

[Joseph Meehan]

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!!

Rather than new tables, why not just identify what quarter it is in the
same table? That is if it has a date already then you can filter by date to
find any quarter you have. At worse you would need to add a "quarter" field
to identify the quarter.

No need for new tables and complexity.

Current versions of access have no specific limit to the size of a
table, but they do have a two gig limit to the size of the entire database.

 

[deltauser2006]

Here's the thing though, the data is constantly changing so basically
I'm looking for snapshots through time of the database. I'll explain
further. The data is for apartment rents and other information. So we
are constantly surveying and getting new rents (we do that every
quarter). Every quarter all the data has changed slightly. When we do
analysis we want to be able to look back at all the data from, lets say
a year ago. I would need to make a printout that had, for example:
Current Average Rent vs. Average Rent During This Quarter Last Year.
What I am trying to do is automate this process. I want the report to
recognize that it is currently the 1st quarter, that it should use the
data currently in the database for that number and that it should pull
last years comparison from the 1st quarter '05 data (Which obviously
would take 1 year of data being stored before it would function
seamlessly). Any tips on attacking that? I really appreciate any help
I can get. Thanks

 

[Joseph Meehan]

Here's the thing though, the data is constantly changing so basically
I'm looking for snapshots through time of the database. I'll explain
further. The data is for apartment rents and other information. So
we are constantly surveying and getting new rents (we do that every
quarter). Every quarter all the data has changed slightly. When we
do analysis we want to be able to look back at all the data from,
lets say a year ago. I would need to make a printout that had, for
example: Current Average Rent vs. Average Rent During This Quarter
Last Year. What I am trying to do is automate this process. I want
the report to recognize that it is currently the 1st quarter, that it
should use the data currently in the database for that number and
that it should pull last years comparison from the 1st quarter '05
data (Which obviously would take 1 year of data being stored before
it would function seamlessly). Any tips on attacking that? I really
appreciate any help I can get. Thanks

Fine, but you don't need new tables to do that. You just make sure the
date is there and you just look for all the records on that date. If you
don't record a date or if the quarter your want to associate the information
to may not be the same as the date recorded then you would need a quarter
field as well.

No reason to make a bunch of independent tables. In fact having it all
in one table will make life a lot easer if you want to query the information
by year or by month.

I don't see where you would gain anything by splitting your data up into
discrete tables.