morpheusdreams
MIS
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:
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
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:
Code:
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