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

"deltauser2006" <sdr4@georgetown.edu> wrote in
news:1140550995.783300.324570@o13g2000cwo.googlegr oups.com:
[color=blue]
> 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!!
>[/color]
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.

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

"deltauser2006" <sdr4@georgetown.edu> wrote in
news:1140805595.342497.321740@v46g2000cwv.googlegr oups.com:
[color=blue]
> 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.[/color]

The data is for apartment[color=blue]
> rents and other information. So we are constantly surveying
> and getting new rents (we do that every quarter).[/color]


Every[color=blue]
> 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
>[/color]
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.