Normalize Linked Table With Queries

[chummy via AccessMonster.com]

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

 

[Rod Plastow]

Hi chummy,

I don't know of any way to achieve what you want at the time of creating the
tables but you can do this 're-engineering' afterwards.

Go ahead and use make table, etc. to design and populate your tables. Now
use one or more Update queries to propagate the foreign keys. Using your
first two tables as an example. Relate/link WI_TOC with AREA on the field
AREA linking the tables only where the two fields are equal. Now use the
Update query to set WI_TOC!AREA = AREA!AREA_ID. Hey presto, AREA is
overwritten with AREA_ID in table WI_TOC.

Having done all that you may want to fiddle with unmatched entries (if any)
and set the foreign key format to number (long) and the default value to null.

Regards,

Rod