Improve company productivity with a Business Account.Sign Up

x
?
Solved

Add 2nd column to union query

Posted on 2014-02-07
3
Medium Priority
?
426 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1500 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 50

Assisted Solution

by:Dale Fye
Dale Fye earned 500 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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

585 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