Automatic historical archiving 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!!

[Bob Quintal]

"deltauser2006" <sd**@georgetown.edu> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

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

Add two fields to the table one for year, one for quarter. No
need to archive anything, just filter using a pair of queries.

You can even just store a date and calculate the year and
quarter from those if necessary. Access can handle several
million records.
--
Bob Quintal

PA is y I've altered my email address.

[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

[Bob Quintal]

"deltauser2006" <sd**@georgetown.edu> wrote in
news:11**********************@v46g2000cwv.googlegr oups.com:

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

your data from '05 has to be in the same table as for '06 for
this to work effectively. If you have a date of effectivity for
the data, it becomes easy to do this sort of thing.

The way I would build it is that you have a parent record for an
apartment, and a related set of child records that contains the
stuff that changes for that apartment, the date, the amount of
rent, whether it is rented or vacant, that sort of thing. Every
time (quarter) you get new information, a new record is added to
the child table.

When the time comes for statistical analysis, you use the date
of the child record to filter into quarters.

QuarterOfYear: (Month(Effectivitydate)\3)+1

to compare this year's data to last, create a query that
groups on apartmentType, quarterofyear (as defined above) and
year of effectivitydate

Compare this year to 3 years ago? the query doesn't change, only
the filter parameters, (year = 2006 or year = 2003) instead of
2006 or 2005. The filter for QuarterOfYear would be constant at
1
Want to compare last quarter's data instead, reorder year and
quarter. then filter on (Year =2005 and QuarterofYear = 4) or
(Year = 2006 and QuarterofYear = 1)

If you insist on seperate tables for every year, you'll curse
yourself sooner than later.

--
Bob Quintal

PA is y I've altered my email address.