Solved

Add 2nd column to union query

Posted on 2014-02-07
3
415 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
[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 Comments
 
LVL 120

Accepted Solution

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

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

726 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