MS Access: SQL to transfer data from one table to another table

I need to transfer the data from one set of tables to another set of table in this SQL. I have tried to get the SQL to work but I am not that good at it though I think you can see what I want to happen.

Can someone take a shot at this SQL?


SELECT Tool_List.[Instrument ID], Tool_List.[Description of Instrument]

FROM Tool_List

INSERT INTO

tblToolNames

 tblToolNames.[ToolNameID], tblToolNames.[ToolName]






SELECT

DimensionVerification.[Autonumber], DimensionVerification.[ID], DimensionVerification.[0625], DimensionVerification.[125], DimensionVerification.[250], DimensionVerification.[500]


FROM DimensionVerification

INSERT INTO

tblDimensionVerification

tblDimensionVerification.[DimensionID], tblDimensionVerification.[Inspection_FID], tblDimensionVerification.[0625], tblDimensionVerification.[125], tblDimensionVerification.[250], tblDimensionVerification.[500], tblDimensionVerification.[1],




SELECT

Inspection_Log.[InspectionNumber], Inspection_Log.[Instrument ID], Inspection_Log.[Date Checked], Inspection_Log.[Checked By], Inspection_Log.[General Condition], Inspection_Log.[Calibration, Inspection]_Log.[Calibration Results];

FROM

Inspection_Log

INSERT INTO

tblInspections

 tblInspections.[InspectionID],  tblInspections.[ToolName_FID],  tblInspections.[DateChecked],  tblInspections.[CheckedBy],  tblInspections.[GeneralCondition],  tblInspections.[Calibration],  tblInspections.[CalibrationResults];


I have included the db with the tables in question


Measuring-Instruments-3-4-20-11.mdb
James CoatsComputer Info. Sys. StudentAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
SamuelClemmons,

looking at your db..

* you cannot  insert a value to an AutoNumber fields

example
* if you want to retain the Instrument ID from table Tool_List as ToolNameID in table tblToolNames,  you have to make the ToolNameID field as Number NOT AutoNumber

* or let it be an Autonumber (will create new sequential numbers as ID), but change the query to remove the AutoNumber fields from the Append queries


* the same goes for the other tables, so at present table designs, the query i posted (even if the syntax is correct, will not append anything to the destination tables)





0
 
Rey Obrero (Capricorn1)Commented:
use this format

INSERT INTO  tblToolNames ([ToolNameID], [ToolName])
SELECT Tool_List.[Instrument ID], Tool_List.[Description of Instrument]
FROM Tool_List

0
 
Rey Obrero (Capricorn1)Commented:
here is the second query

INSERT INTO tblDimensionVerification ( DimensionID, Inspection_FID, [0625], [125], [250], [500] )
SELECT DimensionVerification.Autonumber, DimensionVerification.ID, DimensionVerification.[0625], DimensionVerification.[125], DimensionVerification.[250], DimensionVerification.[500]
FROM DimensionVerification;
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
borkiCommented:
How did you get this SQL code? The way to do it is to use the query design tools and use a make table query. Try and test it until you got the satisfactory outcome.
Then look at the SQL view of the query you tried and tested and lift the SQL from there to use for further automation.
0
 
Rey Obrero (Capricorn1)Commented:
here is the third query

INSERT INTO tblInspections ( InspectionID, ToolName_FID, CheckedBy, CheckedBy, GeneralCondition, Calibration, CalibrationResults )
SELECT Inspection_Log.InspectionNumber, Inspection_Log.[Instrument ID], Inspection_Log.[Date Checked], Inspection_Log.[Checked By], Inspection_Log.[General Condition], Inspection_Log.Calibration, Inspection_Log.[Calibration Results]
FROM Inspection_Log;
0
 
Rey Obrero (Capricorn1)Commented:
see query1,query2 and query3 from the db
Measuring-Instruments-3-4-20-11.mdb
0
 
SheilsCommented:
Safest bet is to use the append query wizard.

Firstly the field that you want to append must exist in the table that you are going to append it to. That is DimensionID, Inspection_FID, [0625], [125], [250], [500] must exist in tblDimensionVerification.

Then create a query in design view from DimensionVerification. Right click on the query in design view and click query type>Append Query. You will then be asked for the table that you want to append the data to and select the fields to append. And voila
0
 
James CoatsComputer Info. Sys. StudentAuthor Commented:
I was just looking at that. Query 1 only moved over 6 records, Query 2 worked and Query 3 had an error that said "Duplicate output destination 'CheckedBy' but that was because in the Design mode that field had been selected twice once I changed one of them to "DateChecked" the second query worked.

All I really need now is to get the field "Description of Instrument" from table "Tool_List" into the field "ToolName" of "tblToolNames" which are referrenced to unique ID's (Instrument ID)from table "Tool_List"

Could I just copy and past the other over minus the six that came over in the query??



Two of your queries worked. The only thing is that there are multiple "ToolName" (s) due to more than one inspections which would associated with "tblInspections" Or will that matter if I copy all the records down to just before the last six that came over in the query??
0
 
James CoatsComputer Info. Sys. StudentAuthor Commented:
Thanks again I can see this will work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.