Solved

Join properties in query

Posted on 2014-01-17
5
327 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
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 47

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 47

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

911 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

23 Experts available now in Live!

Get 1:1 Help Now