0

This question is similar to the one here stackoverflow however it does differ.

I am trying to combine two different queries into one query. Each query has 2 columns. Here is some sample data.

Query #1
Country | Quantity
USA     | 312
Canada  | 513
Mexico  | 258

Query #2
Country | Quanity
USA     | 425
UK      | 394
Mexico  | 489

And then the final query needs to look like this...

Country | Query#1 | Query #2
USA     | 312     | 425
Canada  | 513     | 0
UK      | 0       | 394
Mexico  | 258     | 489

The empty ones can be blank, or 0 it does not matter.

It seems like I need to use a FULL OUTER JOIN, or something to that affect, but Access does not have a FULL OUTER JOIN.

I have tried to use a union query, but I do not understand them well enough to accomplish this. Any help is much appreciated!

Community
  • 1
  • 1
sonfollower
  • 1,047
  • 2
  • 10
  • 20
  • He mentions MSAccess in the title, so Query1 and Query2 are probably saved Queries. In MSAccess world, they work like views... – Steven Mastandrea Jul 11 '12 at 15:45
  • Each query is really complex because it is pulling the information from several places. There is another column in each query (#1 ) that is vendor. And by placing a criteria line that is what gets the correct numbers. Not sure if that is clear or not.... – sonfollower Jul 11 '12 at 15:45

1 Answers1

-1

You want to create a query that joins your other two queries. In order to get nulls (or zeros) from both Queries, you'll need to have a table and/or a Query that has all the countries. Then, you can outer join to the other two tables like so (assuming the Country table/query is called Country): 

SELECT Country.Country, Query1.Quantity, Query2.quantity 
FROM Country
LEFT OUTER JOIN Query1 ON Query1.Country = Country.Country
LEFT OUTER JOIN Query2 ON Country.Country = Query2.Country
Steven Mastandrea
  • 2,752
  • 20
  • 26
  • If you want blank values, then you'll need to have a table/Query that just lists the Countries, and then you can outer join both Query1 and Query2 to the Countries table so you can get blank values. – Steven Mastandrea Jul 11 '12 at 15:38
  • That is close, but it looks like I need an OUTER JOIN instead of an INNER JOIN. However if I replace INNER with OUTER or FULL or FULL OUTER Access throws an error? – sonfollower Jul 11 '12 at 15:52
  • I don't believe MS Access does full outer joins. You can do a LEFT OUTER JOIN but that will only get you blank values from 1 of the queries. Otherwise, you'd have the list of countries and then OUTER JOIN both Query1 and Query2 to the Country table/query. – Steven Mastandrea Jul 12 '12 at 02:15