Hello,
I am new to Access and I am struggling with a probably simple problem. I want to compare invoicing from different years for each of my clients. Let me elaborate.
I have a separate excel table for each year (lets say, 2017, 2018 ). In each of those tables, I have a column with client name and a column with total invoiced amount.
I would like to have a final table where I have "client name", "2017 invoiced" and "2018 invoiced" as columns.
My main problem is that some client may appear in 2017 but NOT in other years. I might have:
CLIENT_NAME |
INVOICED17 |
clientA |
1000€ |
clientB |
500€ |
clientC |
100€ |
CLIENT_NAME |
INVOICED18 |
clientA |
800€ |
clientC |
200€ |
clientD |
50€ |
I want my final table to look like this:
CLIENT_NAME |
INVOICED17 |
INVOICED18 |
clientA |
1000€ |
800€ |
clientB |
800€ |
|
clientC |
500€ |
200€ |
clientD |
|
50€ |
What is the easiest way to do this? The best result so far I have is importing each table, and then using a UNION query, but I have a not perfect result that looks like this:
CLIENT_NAME17 |
CLIENT NAME18 |
INVOICED17 |
INVOICED18 |
clientA |
clientA |
1000€ |
800€ |
clientB |
|
800€ |
|
clientC |
clientC |
500€ |
200€ |
|
clientD |
|
50€ |
Here is my UNION query SQL code:
Code:
SELECT inv17.[client_name], inv18.[client_name], inv17.inv17,inv18.inv18
FROM inv17 LEFT JOIN inv18 ON inv17.[client_name]=inv18.[client_name]
UNION ALL
SELECT inv17.[client_name], inv18.[client_name], inv17.inv17,inv18.inv18
FROM inv17 RIGHT JOIN inv18 ON inv17.[client_name]=inv18.[client_name]
WHERE inv17.[client_name] IS NULL
Thanks a lot!