We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

James Coats
James Coats asked
on
Medium Priority
322 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
use this format

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

CERTIFIED EXPERT
Top Expert 2016

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;

Commented:
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.
CERTIFIED EXPERT
Top Expert 2016

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;
CERTIFIED EXPERT
Top Expert 2016

Commented:
see query1,query2 and query3 from the db
Measuring-Instruments-3-4-20-11.mdb

Commented:
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
CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
James CoatsComputer Info. Sys. Student

Author

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??
James CoatsComputer Info. Sys. Student

Author

Commented:
Thanks again I can see this will work.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.