Solved

Insert into field values from 2 tables into a new table

Posted on 2011-02-26
5
326 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

785 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