SQL Code to append multiple queries in an update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I get an error when I try to run the following

INSERT INTO tbl_Round2
SELECT *
FROM tbl_Changes_PSG_Round2, tbl_Changes_IPG_Round2;

The tables have the same fields. The Update query works with one tbl. The
error says 'Duplicate output destination "Qtr"'. Qtr is the first field in
the tables.
 
That is not going to work at all. You will need to either break it into one
query per table for the two tables you are trying to append from or create a
UNION query between tbl_Changes_PSG_Round2 and tbl_Changes_IPG_Round2 and use
that in your SELECT statement.
 
Thanks, I did not know there was such a thing as a UNION query. I was able
to join 5 tables together. Do I have to open this query to get it to update
if I ever change the sources?
 
Sorry, but I don't really understand the question. You can't create a Uniion
query in design mode. You have to write the SQL by hand. If any of your
tables changes or you remove or add tables, you will have to modify the SQL.

Now, in case I did not really make it clear (I probably did not), you would
use the union query in the append query

INSERT INTO tbl_Round2 (Field1, Field2, Field3, Field4)
SELECT FirstFld, SecondFid, ThirdFld, LastFld
FROM MyUnionQuery;
 
If you mean you are going to change the data in the sources, then you don't need
to open the union query and edit it. It will return the data from the tables.
If you modify one or more tables then you may need to modify the query.

A query is a set of instructions to display data from tables.

(Well, some queries add, delete, and change records and some queries can create
and delete tables and fields - more on that in your 5-day course of writing queries).
 
Hi, my union query is updating when my source changes if it is a unique
record. I added a duplicate row and see it in my links source ....ESS.....
But it does not show up in the union qry after I open it. Does the below
code I wrote only add unique records? It is a great feature if it does this.

SELECT *
FROM [1_tbl_Changes_ESS_Round2]
UNION SELECT *
FROM [1_tbl_Changes_IPG_Round2]
UNION SELECT *
FROM [1_tbl_Changes_PSG_Round2]
UNION SELECT *
FROM [1_tbl_Changes_SW_Round2]
UNION SELECT *
FROM [1_tbl_Changes_Services_Round2]
ORDER BY [Qtr];

any advice?
 
By default, no duplicate records are returned when you use a UNION
operation; however, you can include the ALL predicate to ensure that all
records are returned. This also makes the query run faster.

UNION returns unique record values

UNION ALL returns all the records

So depending on what result you need, use UNION or UNION ALL

Browns77449 said:
Hi, my union query is updating when my source changes if it is a unique
record. I added a duplicate row and see it in my links source ....ESS.....
But it does not show up in the union qry after I open it. Does the below
code I wrote only add unique records? It is a great feature if it does
this.

SELECT *
FROM [1_tbl_Changes_ESS_Round2]
UNION SELECT *
FROM [1_tbl_Changes_IPG_Round2]
UNION SELECT *
FROM [1_tbl_Changes_PSG_Round2]
UNION SELECT *
FROM [1_tbl_Changes_SW_Round2]
UNION SELECT *
FROM [1_tbl_Changes_Services_Round2]
ORDER BY [Qtr];

any advice?

John Spencer said:
If you mean you are going to change the data in the sources, then you
don't need
to open the union query and edit it. It will return the data from the
tables.
If you modify one or more tables then you may need to modify the query.

A query is a set of instructions to display data from tables.

(Well, some queries add, delete, and change records and some queries can
create
and delete tables and fields - more on that in your 5-day course of
writing queries).
Click to expand...
Click to expand...
 
Back
Top