Solved

Join properties in query

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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…
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 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