Trouble importing and normalizing tables

[Jeff]

I need to import files from Excel and MS Project that have
duplicate fields - project name, project id, task name,
task id, etc. The Excel file then has cost, revenue fields
and Project has start date, end date, task complete
(yes/no) type fields. The other piece of the puzzle is
that the files are updated weekly so if I link to Excel,
rather than import, I do not need to update that side
manually.

Ultimate goal is a summary report. Should I try to
normalize these tables and if so, how? If I do, which
directon should the relationships go?

I would really appreciate any advice because this has me
stumped.

Thanks.

 

[Jeff Boyce]

Jeff

What you have to start with (Excel, Project data) and what you end up with
don't need to be the same. You could link to and/or import whichever
pieces, then run as many queries as needed to clean up and append to more
"permanent" tables.

You mention normalizing your tables -- that's a good place to start. But
I'd suggest what you normalize is the "permanent" tables. (By the way, take
a look at Access HELP on the topic of normalization and/or the following
link:
http://support.microsoft.com
for KB# 100139

You and Access may not be using the same definition.)

Good luck

Jeff Boyce
<Access MVP>

 

[Jeff]

I understand what you are saying, in theory, about running
multiple queries to get data into "permanent" tables and
normalizing those. I am a little fuzzy on the actual
technique on how to best query multiple tables with
duplicate data to separate them into tables with unique
data.

This does make me think that was really my question to
begin with. I would appreciate any suggestions or pointers
to any KB articles that would talk about this.

Thanks.

Jeff

 

[Jeff Boyce]

Jeff

Take a look into the topic of normalization, then use paper/pencil to design
the entities (tables), attributes (fields) and relationships that reflect
the data.

Import or link to both Excel and Project data. Now, write a query that
takes data from one of these sources and either appends (if your receiving
table is empty) or updates (if your receiving table has ID on which you can
join your imports and your permanent tables).

One approach might be to first load up (?append) the Excel records to your
final structure, then, using the ID match between your final structure and
your Project records (?on ProjectID), update the final table(s) with data
from Project that you don't already have. This approach eliminates the need
to resolve any duplication.

Or, have you considered loading the Project data into your Excel spreadsheet
directly and building your reports from there?

Or even, using Project reporting to summarize?

Good luck

Jeff Boyce
<Access MVP>