Solved

Join properties in query

Posted on 2014-01-17
5
326 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
  • 2
  • 2
5 Comments
 
LVL 84
Comment Utility
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 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

772 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

11 Experts available now in Live!

Get 1:1 Help Now