# Thread: combining queries

1. Registered User
Join Date
Apr 2004
Posts
12

## Unanswered: combining queries

How to combine two queries so the combined query shows all data of both queries...
Here an exemple of what I want

query 1 gave as a result the following

ID Year Quality
1 2002 Good
1 2003 Bad
2 2002 Good
3 2003 Regular

query 2
ID Year Quantity
1 2002 100
2 2002 200
3 2002 300
4 2002 400

The combined query should give

ID Year Quality Quantity
1 2002 Good 100
1 2003 Bad
2 2002 Good 300
3 2003 Regular
4 2002 400

I tried different kind of joints...but until now, no succes.
Tnx for helping me out!
Dave

2. Registered User
Join Date
Sep 2003
Location
NJ
Posts
5

## Question.

In the final result, how does the 300 relate to "good"? It seems that it should be 200.

Except for that,

SELECT Table2.id, IIf([table1].[year],[table1].[year],[table2].[year]) AS yr, IIf([quality],[quality],[quantity]) AS qual, IIf([table1].[year]=[table2].[year],IIf([quality],[quantity])) AS Quantity
FROM Table1 RIGHT JOIN Table2 ON Table1.id = Table2.id
WITH OWNERACCESS OPTION;

should work.

3. Registered User
Join Date
Apr 2004
Posts
12
TNX for your reply.
you were right about the mix up of the 200 and 300.
However, your solution only worked partly.
If a combination of and ID and YEAR is in table1 and not in table2, it will show in the result. On the other hand, if there is a combination of ID and YEAR in table2, not existing in table1, than this will not be shown in the result.

Since I believe this is kind of an action which is performed quiet a lot, doesnt Access has an easier solution??

Tnx a lot,
Dave

4. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
Originally Posted by dave_nys
I tried different kind of joints...
heh, so did i, back in the sixties... and i inhaled, too

what you are looking for is a FULL OUTER JOIN

not sure which versions of access might support it, i know access 97 doesn't

but you can achieve the same results as follows:
Code:
```select q1.ID
, q1.Year
, q1.Quality
, q2.Quantity
from query1 q1
left outer
join query2 q2
on q1.ID   = q2.ID
and q1.Year = q2.Year
union all
select q2.ID
, q2.Year
, null
, q2.Quantity
from query1 q1
right outer
join query2 q2
on q1.ID   = q2.ID
and q1.Year = q2.Year
where q1.ID is null```

5. Registered User
Join Date
Apr 2004
Posts
12

## great...it works!

Tnx...that was it...worked perfectly!!

6. Registered User
Join Date
Sep 2003
Location
NJ
Posts
5

## a-HA!

So that's how you do a full outer join.

7. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
in databases that support LEFT OUTER and RIGHT OUTER but not FULL OUTER, yup, that's how you do it

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•