Solved

Insert into field values from 2 tables into a new table

Posted on 2011-02-26
5
308 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
Comment Utility
Can you post the SQL that's failing?
0
 

Author Comment

by:comres
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Testing is completed - this resolved the problem.  Thank you for your assistance.  Solution above accepted.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

763 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

14 Experts available now in Live!

Get 1:1 Help Now