Takes results from union query and append to table

reference q #
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27320023.html

I have a third table that needs the data from the query below inserted into it.
Table3

3 fields same field names
fld1 PrimaryKey Dups-ok
fld2 PrimaryKey Dups-ok
fld3 PrimaryKey Dups-ok

What I need:
I need to take the results from this query below and append the data to a new table...("Table3")
same structure as the other 2 tables  "Table1" and "Table2"
Table1
Table2

3 fields in each table  same field names
fld1 PrimaryKey Dups-ok
fld2 PrimaryKey Dups-ok
fld3 PrimaryKey Dups-ok

Thanks
fordraiders


Select A.* From
(
SELECT Table1.fld1, Table1.fld2, Table1.fld3,"Table1" as SourceTable
FROM Table1 LEFT JOIN Table2 ON (Table1.fld3 = Table2.fld3) AND (Table1.fld2 = Table2.fld2) AND (Table1.fld1 = Table2.fld1)
WHERE (((Table2.fld1) Is Null) AND ((Table2.fld2) Is Null) AND ((Table2.fld3) Is Null))

Union All
SELECT Table2.fld1, Table2.fld2, Table2.fld3, "table2"
FROM Table2 LEFT JOIN Table1 ON (Table2.fld3 = Table1.fld3) AND (Table2.fld2 = Table1.fld2) AND (Table2.fld1 = Table1.fld1)
WHERE (((Table1.fld1) Is Null) AND ((Table1.fld2) Is Null) AND ((Table1.fld3) Is Null))
) As A

Open in new window

LVL 3
FordraidersAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
fordraiders,

the sql in the text file you uploaded has this line broken


WHERE (((CRS_DescmatchGisXrefExt.item) Is Null) AND ((CRS_Descmatch



GisXrefExt.vendor_name) Is Null) AND ((CRS_DescmatchGisXrefExt.mfrnum) Is Null))
Union All


try correcting it first, like this



WHERE (((CRS_DescmatchGisXrefExt.item) Is Null) AND ((CRS_DescmatchGisXrefExt.vendor_name) Is Null) AND ((CRS_DescmatchGisXrefExt.mfrnum) Is Null))
Union All
0
 
Rey Obrero (Capricorn1)Commented:
insert into table3 (fld1,fld2,fld3)
select A.fld1,A.fld2,A.fld3
from
(
SELECT Table1.fld1, Table1.fld2, Table1.fld3,"Table1" as SourceTable
FROM Table1 LEFT JOIN Table2 ON (Table1.fld3 = Table2.fld3) AND (Table1.fld2 = Table2.fld2) AND (Table1.fld1 = Table2.fld1)
WHERE (((Table2.fld1) Is Null) AND ((Table2.fld2) Is Null) AND ((Table2.fld3) Is Null))

Union All
SELECT Table2.fld1, Table2.fld2, Table2.fld3, "table2"
FROM Table2 LEFT JOIN Table1 ON (Table2.fld3 = Table1.fld3) AND (Table2.fld2 = Table1.fld2) AND (Table2.fld1 = Table1.fld1)
WHERE (((Table1.fld1) Is Null) AND ((Table1.fld2) Is Null) AND ((Table1.fld3) Is Null))
) As A
0
 
FordraidersAuthor Commented:
cap, taking your example and inserting my real field names

see attached text file of append with real fields.

error:
Msg 207, Level 16, State 1, Line 6
Invalid column name 'CRS_DescmatchGisXrefExt_staging'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'CRS_DescmatchGisXrefExt'.

using format below as line reference:
1. INSERT INTO
2. SELECT A.item
3.
4. FROM
5.  (
6. SELECT
7. FROM
8. WHERE
9. UNION ALL
10 .SELECT
11. FROM
12. WHERE
13. ) A



I think its a quotes issue but please take a look ?
 

unionquery-append-capexample.txt
0
 
FordraidersAuthor Commented:
Thanks...I also had to change the " marks  because I was putting the sql in sql server 2008 r2...
Thanks
for the help
0
 
Rey Obrero (Capricorn1)Commented:
actually, since you are not using the "sourceTable" in your append query,
you can remove this parts

   ,"CRS_DescmatchGisXrefExt_staging" as SourceTable


   , "table2"


from the sql statment
0
All Courses

From novice to tech pro — start learning today.