Combining new data with old

[Sally]

I have a database set up with FY07 data. I would like to continue to use
this database, but add in FY08 data without changing the old records. What's
my best approach. BTW, I inherited this Access 2003 project, and am fairly
new to it.

 

[mscertified]

Two options:
(a) Put the new data in the same table and have a column called FY set to 07
or 08 to distinguish them. Everywhere the data is accessed you will have to
check for the FY value.
(b) Put the new data in a separate table.

Which way you do it depends on how you are using the data and what the
volume is. For instance, will you need to combine the FY07 and FY08 data
together or will they always remain separate?

-Dorian

 

[Tony Toews [MVP]]

(a) Put the new data in the same table and have a column called FY set to 07
or 08 to distinguish them. Everywhere the data is accessed you will have to
check for the FY value.

That's definitely what I'd do. Now if you need to do reports based
on current FY vs last year FY then put those values, ie 2007 and 2006
in two fields in a "Global Options" table and in a hidden form opened
by either your autoexec macro and your first form.

Don't use a menu or other form viewable by the user for these "Global
Options" values as if the user accidentally scrolls up with the mouse
you'll get a new Global Options record created which could cause much
confusion in queries and elsewhere.

(I wasted an hour trying to diagnose this problem remotely with a
client before I finally had them zip the Backend MDB file and email it
to me. Once I figured out the problem it took me another three or
four months before I had the aha moment that told me how the user
managed to do this. Listen to the voice of experience here.
<smile>)

Now in your various queries you can filter the records based on these
two values in the hidden form. Or you could throw in the Global
Options table into the queries. I'm not sure I like that option
because it's a Cartesian join and thus may be relatively inefficient.

I also created some very standard base queries called "Teams this
Year" and "Teams Last Year." This was for a sporting league
database. These included the main table with annual data as well as
all records from the foreign keys.

The following only applies if you have some very complex queries
including cross tab, make table or stacked queries. Stacked queries
being where one query calls another and so forth.

Finally, because I chose to use this afore mentioned hidden "Global
Options" form I was having troubles in one particular very ugly cross
tab MakeTable query when I had to execute it in code. So I created
the query using a standard name parameter "Season" in this case. I
also had to explicitly define the "Season" parameter in the query. So
the Make Table code ended up being a few lines longer.

Dim db As Database, qdf As QueryDef, prmSeason As Parameter

DoCmd.DeleteObject acTable, "temp Team Forecast Report"
' CurrentDb.Execute "Team Forecast Report - Make Table",
dbFailOnError

Set db = CurrentDb()
Set qdf = db.QueryDefs("Team Forecast Report - Make Table")
qdf.Parameters("Season") =
Forms!GlobalOptionsHidden!goCurrentSeason
qdf.Execute dbFailOnError

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Sally]

Thank you, Tony and Rupert. I appreciate your very detailed instructions,
although I admit I'm a little overwhelmed. As I mentioned, I'm new to Access
and am trying to learn it from a textbook. Specifically, my situation is
that there is a linked table that changes each fiscal year. I'd like to be
able to point the FY07 records to the FY07 info in the table, while pointing
the FY08 records to the FY08 info in the table. Is there an easy way to
accomplish this?

Again, thanks very much for your help.

 

[Tony Toews [MVP]]

Thank you, Tony and Rupert. I appreciate your very detailed instructions,
although I admit I'm a little overwhelmed. As I mentioned, I'm new to Access
and am trying to learn it from a textbook. Specifically, my situation is
that there is a linked table that changes each fiscal year. I'd like to be
able to point the FY07 records to the FY07 info in the table, while pointing
the FY08 records to the FY08 info in the table. Is there an easy way to
accomplish this?

So you have one table for FY07 and another table for FY08? I'd change
that to put all the data in one table with the year being another
field in the record.

Otherwise every year you are going to have to change all your queries,
forms and reports to point to the new data.

As far as being overwhelmed goes I can appreciate that. I'd suggest
rereading that and asking specific questions. I did clean it up and
posted that page on my website at
http://granite.ab.ca/access/using_data_from_the_previous_year.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/