Solved

Access table union - merge data even even non-matches other criteria

Posted on 2014-09-16
1
302 Views
Last Modified: 2014-09-17
I have two tables, "GDW" and "JJHCC" that have the same fields -- User ID and Job Level

I want to create Union by User ID of both tables but I want to use the JJHCC values for Job Level if it exists and only use the GDW information if JJHCC is blank or if that User ID doesn't exist in JJHCC.

GDW has a lot more records than JJHCC so there will be a many that get brought over in the union.

What would the SQL look like for this?

Thanks for your help.

Adam
0
Comment
Question by:aehrenwo
1 Comment
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40325100
SELECT GDW.[User ID], Nz([JJHCC].[Job Level],[GDW].[Job Level]) AS [Job Level]
FROM GDW LEFT JOIN JJHCC ON GDW.[User ID] = JJHCC.[User ID]

UNION ALL

SELECT JJHCC.[User ID], JJHCC.[Job Level]
FROM GDW RIGHT JOIN JJHCC ON GDW.[User ID] = JJHCC.[User ID]
WHERE (((GDW.[User ID]) Is Null));
0

Featured Post

Highfive Gives IT Their Time Back

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

707 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

15 Experts available now in Live!

Get 1:1 Help Now