Solved

Takes results from union query and append to table

Posted on 2011-09-21
5
266 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:fordraiders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36577316
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
 
LVL 3

Author Comment

by:fordraiders
ID: 36578585
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 36580244
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
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 36580314
Thanks...I also had to change the " marks  because I was putting the sql in sql server 2008 r2...
Thanks
for the help
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36580361
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 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