How do I normalize this?

Hello,
I guess the question is, what is the best way to organize this data. I
inherited this database years ago and have been afraid to change how things
have always been done:

1. Last years table is renamed table Previous Year (the table named
table Previous Year is renamed table year)
2. A copy of last years data is then gone over with a fine tooth comb in the
current year table and unecessary records are deleted

This database is used (very effectively but not very effeciently ) as a
vessel for mail merge. Previous years need to be kept for legal purposes.
Any ideas?

Thanks,
nick

Nick,

This has to start with you making a detailed decision of what information
you want to store. "Information" isn't information unless it answers a
question / serves a purpose.

The "information" contained in those old tables includes:

1. (existence of a record indicates) whether or not they were "on the list"
that year.

2. The person's data as of (the end of) that year. For example, the 2006
list might have Joe's address where he lived in 2006 before he moved.

If the "#2" "Information" really is information that is useful or required,
then your current plan is a pretty good one and is probably normalized. I
say "probably" because if you have data on individuals which which either
spans years or NEVER changes, full normalization would dictate separating
that.

Even if #2 needs to be retained, here's another idea. Just add a field for
each year, and put a "Y" in that field if th record is on the list that year.
(this does not meet, but in my opinion is better than 100% normalization).
And just update the information on an ongoing basis. Then just save copies
copies of the entire database. Might "waste" 2 cents worth of hard drive
space and save you 1000 times that in time. This also works if #2 is not
required, with less need for the copies.

When you need to add a table just due to the passing of time (i.e. a
table for each year), then your database isn't normalized. It makes
forms and reports more complex, and also makes querying multiple-year
data more difficult (requiring Union queries and design changes each
year).

As Fred suggests, adding a Year column to the table is a reasonable
first step.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com