Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PROBLEM: INSERT INTO & SELECT ................ WITH A TWIST

Posted on 2014-03-26
2
Medium Priority
?
218 Views
Last Modified: 2014-03-26
Here is my SQL Statement:

str_SQL1 = "INSERT INTO tbl_2 (Field_1, Field_2) SELECT Field_1, Field_2 FROM tbl_1 WHERE tbl_1.Field_2 = " & Supplied_Variable_1 & ";"

It works fine copying one or more records from tbl_1 over to tbl_2 based on the filter Supplied_Variable_1.

But here is the twist:  I want to supply another variable (Supplied_Variable_2) into Field_2 of the target table (tbl_2)  rather than what is found in tbl_1.Field_2. How do I do this?

EXAMPLE:
Supplied_Variable_1 = 1200 (original query filter)
Supplied_Variable_2 = 2222

tbl_1
Field_1, Field_2
Abernathy, 1200
Baker, 1200
Coker, 1200
Dodds, 1200

tbl_2 (after INSERT INTO is Run)
Field_1, Field_2
Abernathy, 2222
Baker, 2222
Coker, 2222
Dodds, 2222

Last Question:  Assuming proper syntax, can you have a table run this "Append" on itself?

i.e.

str_SQL1 = "INSERT INTO tbl_1 (Field_1, Field_2) SELECT Field_1, Field_2 FROM tbl_1 WHERE tbl_1.Field_2 = " & Supplied_Variable_1 & ";"

Thanks for any help.
0
Comment
Question by:dgheck
  • 2
2 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39956077
Try this if field 2 is NUMERIC.

str_SQL1 = "INSERT INTO tbl_2 (Field_1, Field_2) SELECT Field_1, " & Supplied_Variable_2 & " FROM tbl_1 WHERE tbl_1.Field_2 = " & Supplied_Variable_1 & ";"

Open in new window


If field 2 is TEXT:

str_SQL1 = "INSERT INTO tbl_2 (Field_1, Field_2) SELECT Field_1, '" & Supplied_Variable_2 & " ' FROM tbl_1 WHERE tbl_1.Field_2 = " & Supplied_Variable_1 & ";"

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 1000 total points
ID: 39956080
<< Last Question:  Assuming proper syntax, can you have a table run this "Append" on itself? >>

Yes.  The syntax would be:

str_SQL1 = "INSERT INTO tbl_1 (Field_1, Field_2) SELECT Field_1, '" & Supplied_Variable_2 & " ' FROM tbl_1 WHERE tbl_1.Field_2 = " & Supplied_Variable_1 & ";"

Open in new window


(Just change the target table name)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

916 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