Table structure

[wal50]

The current db is updated monthly from a workbook that comes from another
dept. I append the new records monthly. The queries and reports have worked
fine for months.
The wizards who generate the input have changed the spreadsheet structure.
They added about 60 new fields (the "old" fields are retained with the same
names) and have sent new (monthly) workbooks with the history of both the old
and new fields going back 4 months.
I tried importing the new files (to a test copy ot the current table) using
update and append queries. Doesn't work - new fields. Seems like if I add
the new fields to the structure of the existing table that would work. But
then I would have correct all the typos and data type errors I make when I
enter them in. Is there an easy way to do this?
I have also considered importing the new files to a new table and the
pointing all the queries and reports to the new one (Again, the 'old' field
names are the same).
Or can someone suggest a better way?

Thanks in Advance,
wal50

 

[John W. Vinson]

The current db is updated monthly from a workbook that comes from another
dept. I append the new records monthly. The queries and reports have worked
fine for months.
The wizards who generate the input have changed the spreadsheet structure.
They added about 60 new fields (the "old" fields are retained with the same
names) and have sent new (monthly) workbooks with the history of both the old
and new fields going back 4 months.
I tried importing the new files (to a test copy ot the current table) using
update and append queries. Doesn't work - new fields. Seems like if I add
the new fields to the structure of the existing table that would work. But
then I would have correct all the typos and data type errors I make when I
enter them in. Is there an easy way to do this?
I have also considered importing the new files to a new table and the
pointing all the queries and reports to the new one (Again, the 'old' field
names are the same).
Or can someone suggest a better way?

Probably. If you have 60 fields that's about 45 too many for any rationally
designed table! It's reasonable for a spreadsheet but NOT for a relational
database.

I'd strongly urge setting up a set of properly normalized tables (untangling
the many repeating fields which undoubtedly exist in these spreadsheets into
multiple records in a one-to-many relationship); using File... Get External
Data... Link to link to the spreadsheet; and running as many Append queries as
are needed to migrate the data into the normalized structure.

Big job and perhaps not appropriate, so take this advice considering what you
paid for it...!

John W. Vinson [MVP]

 

[wal50]

Thanks for the reply John.
That's the solution. You are correct that there should be multiple tables
as much of the info for a given employee is the same from month to month and
I will take your (valuable) advice on that score. As far as the new stuff, I
can put that in a new table(s) and use it as needed without impact on any of
the existing reports/queries.
Thanks again for your help.
wal50