Solved

Add 2nd column to union query

Posted on 2014-02-07
3
408 Views
Last Modified: 2014-02-10
This union query takes email addresses from various fields and sources and presents them as a single column of data (no duplicates). I would like a second column to come up, containing the CIFM_NBR field which is present in the 4_QRY_Final query. How do I modify to get to that?

SELECT CIFM_EMLHP_ADDR_502 FROM 4_QRY_Final
UNION SELECT CIFM_EMLHP_ADDR_505 FROM 4_QRY_Final
WHERE CIFM_EMLHP_ADDR_505 IS NOT NULL
UNION SELECT CIFM_EMLHP_ADDR_508 FROM 4_QRY_Final
WHERE CIFM_EMLHP_ADDR_508 IS NOT NULL
UNION SELECT CIFM_EMLHP_ADDR_511 FROM 4_QRY_Final
WHERE CIFM_EMLHP_ADDR_511 IS NOT NULL
UNION SELECT CIFM_EMLHP_ADDR_514 FROM 4_QRY_Final
WHERE CIFM_EMLHP_ADDR_514 IS NOT NULL
UNION SELECT CIFM_EMLHP_ADDR_517 FROM 4_QRY_Final
WHERE CIFM_EMLHP_ADDR_517 IS NOT NULL
UNION SELECT EmailAddress FROM 4_QRY_Final
WHERE EmailAddress IS NOT NULL
UNION SELECT EmailAddress FROM _20140128_ABCStaff
UNION SELECT EmailAddress FROM _20140128_DEFStaff;
0
Comment
Question by:K_Deutsch
3 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 375 total points
ID: 39843004
just add the column to the select statement

SELECT CIFM_EMLHP_ADDR_502, CIFM_NBR FROM 4_QRY_Final
UNION
SELECT CIFM_EMLHP_ADDR_505, CIFM_NBR FROM 4_QRY_Final
WHERE CIFM_EMLHP_ADDR_505 IS NOT NULL

... add the other lines here for 4_QRY_Final

UNION
SELECT EmailAddress, Null as CIFM_NBR FROM _20140128_ABCStaff
UNION
SELECT EmailAddress, Null as CIFM_NBR  FROM _20140128_DEFStaff;
0
 

Author Comment

by:K_Deutsch
ID: 39843057
almost there...the same email address can be present on multiple CIFM_NBR, so the query now produces duplicate email addresses that I want to eliminate
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 total points
ID: 39843205
Then you need to determine which CIFM_NBR (first, last, most frequent) you want from each email.

You could wrap the whole thing in a Subquery, something like:

SELECT Temp.EmailAddress, MIN(temp.CIFM_NBR) as CIFM_NBR
FROM (
SELECT ..
UNION
SELECT ..
...
) as Temp
GROUP BY temp.EmailAddress
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now