Solved

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

Posted on 2014-09-16
1
309 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dsum Function for List Box Data 7 48
Setting Macro in Access to Automate Running an Append at a Certain Time 2 24
Filter a form 8 15
Dcount help 2 17
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

809 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