Normalizing Linked Table with Queries

[Janelle.Dunlap]

I have a table in my database that is linked to an excel spreadsheet.
I need to be able to manipulate the data in this linked table so that I
can create smaller normalized tables that work with what I am trying to
do (all data used in my db will come from this one spreadsheet). I am
currently trying to utilize the make-table queries to create the tables
that I need from this data, however I am unsure of how to add foreign
keys to a table so that my data matches up correctly. Does anyone know
of a way to do this? Is there some sort of append query that can be
ran to find entries in one table matching the entries of another table
as the criterion?

Current Tables:

1) Linked Table - WI_TOC with fields AREA, MODEL_NO, OPERATION and
FILE NAME

2) AREA with fields AREA_ID (autonumber primary key) and AREA

3) MODEL_NO with fields MODEL_NO_ID (autonumber primary key) and
MODEL_NO

4) OPERATION with fields OPERATION_ID (autonumber primary key) and
OPERATION

Table 3 needs to have the foreign key AREA_ID added to it and Table 4
needs to have the foreign key MODEL_NO_ID added to it

The query I have to add AREA_ID to Table 3 is as follows:

INSERT INTO Model_No ([Model No], Model_No_ID, Area_ID)
SELECT Model_No.[Model No], Model_No.Model_No_ID (SELECT Area.Area_ID
FROM Area
WHERE Model_No.Model_No = WI_TOC.Model_No AND Area.Area = WI_TOC.Area)
AS Area_ID
FROM Model_No, WI_TOC;

This isn't working though. Any help would be much appreciated!

 

[tina]

if you're migrating the Excel data into Access tables for storage and use,
suggest you start by dumping the Excel data into a single Access table
(using one make-table query would do it). then add a primary key field to
the new table. now you can split the data out into the tables it *should* be
in, with a native primary key value for each record that you can use as a
foreign key where needed.

hth

 

[Janelle.Dunlap]

Tina,

Thanks for the suggestion but I don't think this will work in my case.
I have a lot of duplicate data and assigning a primary key to the
master table doesn't allow me to normalize all my tables. For example
my AREA table needs to look like this:

AREA ID AREA
1 Area 1
2 Area 2
3 Area 3

but in the master table it would look like this with a primary key:

ID AREA
1 Area 1
2 Area 1
3 Area 1
4 Area 2
5 Area 2

To transfer ID over as a foreign key for this and most of the other
fields in my data (MODEL_NO, OPERATION) Access will not know which ID
to use unless I duplicate the data in my smaller tables as well. For
example for Area 1 there is an ID of 1, 2, and 3.
Any other ideas?

 

[tina]

by "a native primary key value for each record that you can use as a
foreign key where needed", i didn't necessarily mean as the "final" foreign
key for any normalized table. what i meant is that by adding a native key to
the Excel data, you can track the individual records in that dataset, and
keep the data "related" as you split it out into normalized tables. data
migration from a flat file into normalized tables is almost always tricky,
and there's no generic answer - other than to say that generally you have to
populate "supporting" tables first, then "parent" data tables, then "child"
data tables, following the relationship hierarchy you've set up in the
Relationships window of the database. remember that this doesn't have to be
"pretty" unless you have to do it over and over - it just has to work right,
once. also keep in mind that you can add additional fields to tables as
necessary to facilitate the migration, and then just delete those extraneous
fields afterward. good luck!