Insert into field values from 2 tables into a new table

Posted on 2011-02-26
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.
Question by:comres
  • 3
  • 2
LVL 10

Expert Comment

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

Author Comment

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


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

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
LVL 10

Accepted Solution

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.


Author Comment

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.

Author Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

896 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now