Hi there,
I was wondering if anyone had a better solution for dealing with duplicates.
I would like to Match off One duplicate and show the other in my results.
Table 1:
Table 2:
Desired Result:
Is the only way is to group and left join on trace, account, amount and count or is there an optimal way?
Result:
Thank you for any help on this, I am kind of a newbie with Access so any suggestions would be great!
I was wondering if anyone had a better solution for dealing with duplicates.
I would like to Match off One duplicate and show the other in my results.
Table 1:
Book1 | |||||
---|---|---|---|---|---|
E | F | G | |||
1 | TRACE | ACCOUNT | AMOUNT | ||
2 | 1 | 111 | 100 | ||
3 | 2 | 222 | 200 | ||
4 | 3 | 333 | 300 | ||
5 | 4 | 444 | 400 | ||
6 | 5 | 555 | 500 | ||
Sheet2 |
Table 2:
Book1 | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | TRACE | ACCOUNT | AMOUNT | ||
2 | 1 | 111 | 100 | ||
3 | 2 | 222 | 200 | ||
4 | 3 | 333 | 300 | ||
5 | 4 | 444 | 400 | ||
6 | 5 | 555 | 500 | ||
7 | 5 | 555 | 500 | ||
Sheet2 |
Desired Result:
Book1 | |||||
---|---|---|---|---|---|
I | J | K | |||
1 | TRACE | ACCOUNT | AMOUNT | ||
2 | 5 | 555 | 500 | ||
Sheet2 |
Is the only way is to group and left join on trace, account, amount and count or is there an optimal way?
Code:
[/COLOR]SELECT Table1.TRACE, Table1.ACCOUNT, Table1.AMOUNT, Count(Table1.AMOUNT) AS CountOfAMOUNT
FROM Table1
GROUP BY Table1.TRACE, Table1.ACCOUNT, Table1.AMOUNT;
SELECT Table2.TRACE, Table2.ACCOUNT, Table2.AMOUNT, Count(Table2.AMOUNT) AS CountOfAMOUNT
FROM Table2
GROUP BY Table2.TRACE, Table2.ACCOUNT, Table2.AMOUNT;
SELECT Table2_Count.*
FROM Table2_Count LEFT JOIN Table1_Count ON (Table2_Count.TRACE = Table1_Count.TRACE) AND (Table2_Count.ACCOUNT = Table1_Count.ACCOUNT) AND (Table2_Count.AMOUNT = Table1_Count.AMOUNT) AND (Table2_Count.CountOfAMOUNT = Table1_Count.CountOfAMOUNT)
WHERE (((Table1_Count.CountOfAMOUNT) Is Null));
Result:
Book1 | ||||||
---|---|---|---|---|---|---|
M | N | O | P | |||
2 | TRACE | ACCOUNT | AMOUNT | CountOfAMOUNT | ||
3 | 5 | 555 | 500 | 2 | ||
Sheet2 |
Thank you for any help on this, I am kind of a newbie with Access so any suggestions would be great!