Creating a new database that will be updated annually

J

Jim P

I'm trying to track payroll data that will be update annually.Should this be
done with a data base for each year or should a copy the 14 tables planned
for each year with a name that start with the year. Example 2006earnings,
2005 earnings
 
K

Klatuu

Copying the tables from one database to another will not maintain your
relationships. If you take this approach, it would be better to copy the
entire database with a name for the new year, then delete all the data from
the tables with the exception of any data you want to keep.

A better way to do this would be to add a field to each table (where
appropraite) that would contain the year that the record is for. Then you
would need to filter data for all your forms, reports, etc. on the current
year.
 
J

Jim P

Thanks Klatuu...That was my original thought but what I can not figure out is
when I'm ready to add the data for the new year to the table...can this be
imported and assigned to the primary key record?
 
K

Klatuu

Sorry, Jim, I don't quite follow your question. Is this data imported from
an external source? Is there any way (not programmatically, but procedurly)
what year the data is for? Is there a date field in the data, like pay
period, pay day date, etc?
Describe to me how the data gets in and maybe we can come up with a solution.
 
J

John Vinson

I'm trying to track payroll data that will be update annually.Should this be
done with a data base for each year or should a copy the 14 tables planned
for each year with a name that start with the year. Example 2006earnings,
2005 earnings
Click to expand...

Storing data in a tablename is EXTREMELY BAD DESIGN.

Just store a date or year field in the table. To get 2005 earnings,
use a Query selecting records from that year.

Totals, averages, etc. should (generally) not be stored in ANY table
but should be generated on the fly using a Totals query.

John W. Vinson[MVP]
 
Top