Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Join properties in query

Posted on 2014-01-17
5
Medium Priority
?
342 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 85
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 49

Expert Comment

by:Dale Fye
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 49

Accepted Solution

by:
Dale Fye earned 2000 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 85
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

876 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