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