Solved

Insert into field values from 2 tables into a new table

Posted on 2011-02-26
5
340 Views
Last Modified: 2012-05-11
I am looking for the most effective way to combine 4 fields from tblImport and 1 field from dbo_item into a new table tblSelected.  Fields from tblImport are seqField, CustItemNum, mfgName and Qty1.  The 5th field has been saved in "Dim holdItem As String" earlier in the code.  The common field that was used to get the holdItem and is needed to get the data from tblImport is holdSeq which is defined "Dim holdSeq As Long".  I attempted to use an "Insert into .. Select...From...Where" SQL statement but I can seem to get the syntax correct.  Any suggestions would be appreciated.
0
Comment
Question by:comres
  • 3
  • 2
5 Comments
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 34989349
Can you post the SQL that's failing?
0
 

Author Comment

by:comres
ID: 34989402
I have tried two different approaches - neither work

#1

If hold1 > 0 Then
    SQL = "INSERT INTO tblSelected SELECT selSeq FROM tblImportData.seqField WHERE seqField=" & [holdSeq] & " SELECT selitem_no FROM holdItem SELECT selCustNum FROM tblImportData.CustItemNum WHERE seqField=" & [holdSeq] & " SELECT selMfgName FROM tblImportData.mfgName WHERE seqField=" & [holdSeq] & " SELECT selQty FROM tblImportData.Qty1 WHERE seqField = " & [holdSeq] & ";"
    DoCmd.RunSQL SQL
  End If

#2
If hold1 > 0 Then
    SQL = "INSERT INTO tblSelected ( selSeq, selitem_no, selCustNum, selMfgName, selQty)SELECT tblImportData.seqField, me.holdItem, tblImportData.CustItemNum, tblImportData.mfgName, tblImportData.Qty1 FROM tblImportData WHERE seqField = " & [holdSeq] & ";"
    DoCmd.RunSQL SQL
  End If
0
 
LVL 10

Accepted Solution

by:
Michael Vasilevsky earned 500 total points
ID: 34989775
I think you're close. You're running this in VBA on your form right? Don't use "SQL" because that's a reserved keyword. I use "strSQL"

If hold1 > 0 Then
     strSQL = "INSERT INTO tblSelected (selSeq, selitem_no, selCustNum, selMfgName, selQty) SELECT tblImportData.seqField, '" & Me!holdItem & "', tblImportData.CustItemNum, tblImportData.mfgName, tblImportData.Qty1 FROM tblImportData WHERE seqField = " & [holdSeq]
    DoCmd.RunSQL strSQL
End If

You can always use a debug.print strSQL right before your DoCmd statement to see what the resulting SQL looks like.
HTH,

MV
0
 

Author Comment

by:comres
ID: 34989929
I am not getting any error messages when I run this code.  I need to run some more tests tomorrow on my data to see if it is inserting the correct data.

I will resume testing in the morning - thanks for your help to this point.
0
 

Author Comment

by:comres
ID: 34992282
Testing is completed - this resolved the problem.  Thank you for your assistance.  Solution above accepted.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question