Normalize 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!

[Larry Linson]

You'll need to write the key of the main table as a foreign key in your
make-table queries, not try to find the relationships afterward.

If you don't have to be periodically re-creating your data tables from a new
or updated spreadsheet, and it is difficult to determine a natural key, you
may want to import the whole spreadsheet into a table, add an AutoNumber
field as a surrogate key, and make sure you copy that in your make-table
queries.

Larry Linson
Microsoft Access MVP

<Ja************ @ge.com> wrote in message
news:11******** **************@ i40g2000cwc.goo glegroups.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!

[pietlinden]

tangential reply, but just wondering why you need to break up a fairly
simple table:

CREATE TABLE wi_toc (
RecordID Autonumber PRIMARY KEY,
Area Text(20) NOT NULL,
ModelNo Text(30),
Operation Text(50),
FileName Text(100));

All the other tables you suggest seem to be just lookup tables. Or is
there a one-to-many relationship you need to break up? (Like each
wi_toc record can have more than one {Area, ModelNo, Operation, or
FileName} associated with it?)

Otherwise, there's really no reason to break up the table at all.
Unless it's seriously huge (over 2 GB).

Just wondering, that's all.

[Janelle.Dunlap]

You are right...no need to even split up the table! I tend to make
things way more complicated then they need to be. Thanks for pointing
that out.