Left & Right Outer Join Union, Query Setup Help

[walpy]

I am trying to Union two tables based on each employee's EIN.



Following these steps here: http://office.microsoft.com/en-us/ac...96320.aspx#BM4

Other than naming my 2 queries Left Join and Right Join, I am not sure how to create a proper "Left Join" query to use in the SQL part of the instructions. What I came up with looks like this so far:

SELECT [HR Data].[Last Name, First Name], [HR Data].EIN, [HR Data].[Date Hired], [HR Data].[Job Title (Employee)], [HR Data].Terminated, [HR Data].[Location (Employee)], [HR Data].[Supervisor Name (Last, First Name)], [HR Data].[Address (Employee)], [HR Data].[City (Employee)], [HR Data].[State (Employee)], [HR Data].[Zip Code], [HR Data].Phone
FROM [HR Data]
UNION
SELECT [Non ICF Trainings].[Name], [Non ICF Trainings].[EIN], [Non ICF Trainings].[Term], [Non ICF Trainings].[Reports To:], [Non ICF Trainings].[Devision], [Non ICF Trainings].[DOH], [Non ICF Trainings].[CPR], [Non ICF Trainings].[First Aid], [Non ICF Trainings].[Fire Ex], [Non ICF Trainings].[BB], [Non ICF Trainings].[D& I], [Non ICF Trainings].[DSP 2], [Non ICF Trainings].[PP/SH/HS], [Non ICF Trainings].[Mandt/TCI/CPI], [Non ICF Trainings].[CRMA], [Non ICF Trainings].[BHP Prov/Mod 1], [Non ICF Trainings].[BHP], [Non ICF Trainings].[Core Day 2/DSP Day 1], [Non ICF Trainings].[Driving], [Non ICF Trainings].[Swim], [Non ICF Trainings].[CS Intro]
FROM [Non ICF Trainings]
WHERE

I have a feeling the queries I started with aren't correct?

[orange]

A couple of comments:

Don't name queries Left Join and/or Right Join. These are specific types of JOINS in SQL.

The best source of info on SQL and JOINs is w3schools

Do a little research with the link info.

And tell us WHAT you are trying to achieve in plain English. Readers will help with the detailed response once they understand your intentions.

[walpy]

Ok, easiest way I can put it is that I have 2 tables, the first (HR Data) has a row for every employee. The second has training data (Non ICF Training) which not every employee is listed in.

Each table has a column "Last Name First Name" and "EIN".

What I am trying to do is add all the training data into the HR Data table, by matching the EIN fields, if no match is found, leave just the HR Data.

Does that make sense?

[JoeM]

Sounds like all you need to do is simply do a Left Join from your "HR Data" table to your "Non ICF Training" table on your EIN field.
That will return ALL the records from your "HR Data", along with any matching data from your "Non ICF Training" table.

No Union Query should be necessary.

[orange]

I don't think you want your TRaining data in the HR table. What happens if an employee takes 5 training courses --will you repeat the HR Data 5 times??

You really have to work through this tutorial before you get too deep. The key to successful data base is to get your tables and relations designed and tested to ensure they support your business.
Too many people have acquired a DBMS software package and think they are now "database designers". It doesn't work like that.

There is a good ebook at http://www.accessmvp.com/strive4peace/ for Access and using it.