By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,226 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Normalize Linked Table with Queries

P: n/a
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!

Jun 13 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.googlegr oups.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!

Jun 13 '06 #2

P: n/a
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.

Jun 14 '06 #3

P: n/a
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.

Jun 14 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.