Combine fields from multiple tables in Access query

Question

As an example, I have two queries in Access. The first outputs two columns:

| Cust ID | Revenue 1 |
|    A    |     5     |
|    B    |    10     |

The second query is similar:

| Cust ID | Revenue 2 |
|    B    |     8     |
|    C    |     9     |

I want to create a query that will combine these first two queries:

| Cust ID | Revenue 1 | Revenue 2 |
|    A    |     5     |      0    |
|    B    |     10    |      8    |
|    C    |     0     |      9    |

But because A is not in the second query, and C is not in the first, this is the result I'm getting:

| Cust ID | Revenue 1 | Revenue 2 |
|    B    |     10    |      8    |

I have a master table of all Cust IDs that exist. How can I write a query that will enter 0 for any value not found (in this case, 0 for A,Rev 2 and C, Rev 1)?

Answer 1

What you are looking for is an Outer Join. The default is to do an Inner Join. This little question describes the difference very nicely:

https://stackoverflow.com/questions/38549/sql-difference-between-inner-and-outer-join/38578#38578

Of course, that depicts it with generic SQL - not sure how that relates to Access at all.

The Microsoft page for Outer Joins in Access is here: http://office.microsoft.com/en-us/access-help/creating-an-outer-join-query-in-access-HA001034555.aspx

It should tell you all you need to know.