• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

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
0
James Coats
Asked:
James Coats
1 Solution
 
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
 
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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
 
Rey Obrero (Capricorn1)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
 
James CoatsAuthor 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 CoatsAuthor Commented:
Thanks again I can see this will work.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now