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

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.
dgheckAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
<< 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
 
mbizupCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.