Solved

Add 2nd column to union query

Posted on 2014-02-07
3
406 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

746 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

12 Experts available now in Live!

Get 1:1 Help Now