Solved

Join properties in query

Posted on 2014-01-17
5
333 Views
Last Modified: 2014-01-18
I have a query that joints two other queries by a date field in each. I want the query to include ALL data from both queries.

When open the "Join" properties I have option 1, 2 and 3. None of these options say what I want... which is include all from both.

Is it possible to even do what I am saying or am I a complete idiot? I am figuring the later.
0
Comment
Question by:cansevin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 84
ID: 39789812
An Inner Join (option 1) will show all records where the linked fields are identical in each table.

A Left Join (option 2) shows all Records in the "left" table. If any records in the "right" table match on the linked fields, the query will show the values from the right table. If there is no match in the right table, the query will show null or blank values.

A Right Join (option 3) is the same as a Left join, except all records in the "right" table are shown, and records from the left table are shown if there is a match on the linked field.

"Left" and "Right" are somewhat vague, but in general they refer to the order in which the tables are listed in the SELECT statement. For example:

SELECT PD.*, PH.PONumber FROM PO_Detail PD INNER JOIN PO_Header PH ON PD.PO=PH.PONumber

In that case, the PO_Detail table would be the "left" table, and the PO_Header table would be the "right" table.

If you had multiple Joins:

SELECT PD.*, PH.PONumber FROM PO_Detail PD INNER JOIN PO_Header PH ON PD.PO=PH.PONumber INNER JOIN Delivery D ON POD.Delivery=D.Delivery

Then both PO_Header and Delivery would be the "right" tables, and PO_Detail would be the sole "left" table. If Delivery were joined to PO_Header:

SELECT PD.*, PH.PONumber FROM PO_Detail PD INNER JOIN PO_Header PH ON PD.PO=PH.PONumber INNER JOIN Delivery D ON POH.Delivery=D.Delivery

Then PO_Detail would be the "left" table and PO_Header the "right" table in the first join, and PO_Header would be the "left" table and Delivery would the "right" table in the second join.

To get to your question (finally):

If you want to show all records in both tables, use a UNION query:

SELECT Col1, Col2, Col3 FROM TableA
UNION
SELECT Col1, Col23, Col129 FROM TableB

You don't "join" those tables, so it would show all items in TableA and all in TableB. The caveat is that each SELECT statement must return the same number of fields.

You can also include WHERE criteria for each SELECT statement:

SELECT Col1, Col2, Col3 FROM TableA WHERE Col6='A'
UNION
SELECT Col1, Col23, Col129 FROM TableB WHERE Col19='B'
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 39789844
What you are looking for is a Full Outer Join, which is not a JOIN option in Access.  However, you can create one yourself, with syntax like:

SELECT A.field1, A.Field2, A.Field3, A.DateField, B.Field1 as BField1, B.Field2 as BField2, B.Field3 as BField3
FROM tableA A
LEFT JOIN tableB B
ON A.DateField = B.DateField
UNION ALL
SELECT Null as Field1, NULL as Field2, NUll as Field3, B.DateField, B.Field1, B.Field2, B.Field3
FROM tableB B
LEFT JOIN tableA A
ON B.DateField = A.DateField
WHERE A.DateField IS NULL

Open in new window

This basically says, give me everything from A, and those record from B that match, and combine that with everything from B that doesn't have a match in A.
0
 
LVL 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39789855
My solution will look something like:

Field1    Field2     Field3     DateField      BField1       BField2    BField3
abc        def          ghi           1/18/14
jkl          mno        pqr          1/19/14        stu                 vw           xyz
                                              1/20/14         123               456         789

Where you have some records where only the A fields have data, some where A and B match on date, and some where only B has data.
0
 
LVL 84
ID: 39789893
Note the difference between UNION and UNION ALL:

UNION will show all records from the two tables. It will eliminate duplicates, if they exist.

UNION ALL will show all records, regardless of whether they're duplicated or not.

If you know that all records returned by the queries will be distinct, then UNION ALL is generally faster.
0
 

Author Closing Comment

by:cansevin
ID: 39790654
Thanks Fyed... I am going to go with this option. I am having problems with the query, I will open another question, and past what I have.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
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…

751 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