Full Outer Join (access) substitute

[morpheusdreams]

Am new to SQL and Queries in general

I have two tables, i need to display the number of times each name appears in each table. If the name doesnt appear in one table but does in the other it should display a null value for the first table. And vice versa.

I came up with the below:

SELECT
sql_NoSale_AgentTotal.AgentName,
sql_Sale_AgentTotal.total_sales, sql_NoSale_AgentTotal.total_non_sales

FROM sql_NoSale_AgentTotal
LEFT OUTER JOIN
sql_Sale_AgentTotal
ON sql_NoSale_AgentTotal.AgentName = sql_Sale_AgentTotal.Username;

However this will only work if the name is in the left table and not the right. If there is a name in the right table but not in the left, i get no values.

I have seen the solution as Full Outer Join but this is not apparently supported in Access.

Any solutions?


If this is not possible I can have all of my data in one table. In which case i would need a query which would do something like this:

Table:
NAME | FIELD1 | FIELD 2
A | B |
B | A |
A | C |
C | | S

Query Results:
NAME | Calc_Field1 | Calc_Field2
A | 2 |
B | 1 |
C | | 1

 

[Golom]

SELECT
sql_NoSale_AgentTotal.AgentName,
sql_Sale_AgentTotal.total_sales, sql_NoSale_AgentTotal.total_non_sales

FROM sql_NoSale_AgentTotal
[red]LEFT[/red] JOIN
sql_Sale_AgentTotal
ON sql_NoSale_AgentTotal.AgentName = sql_Sale_AgentTotal.Username

UNION

SELECT
sql_NoSale_AgentTotal.AgentName,
sql_Sale_AgentTotal.total_sales, sql_NoSale_AgentTotal.total_non_sales

FROM sql_NoSale_AgentTotal
[red]RIGHT[/red] JOIN
sql_Sale_AgentTotal
ON sql_NoSale_AgentTotal.AgentName = sql_Sale_AgentTotal.Username

 

[morpheusdreams]

Would that not duplicate results?

 

[Golom]

No. UNION eliminates duplicates. The results would be duplicated if you used UNION ALL.

 

[morpheusdreams]

Ahh fantastic,

All working fine now.

Much love and many thanks