Access Query Question

[Nonaluuluu]

I have two tables.

Table 1 Columns:
Name
Address
Height
Color
Money
ID
ID2

Table 2 Columns:
Name
Address
Height
Color
Money
ID
ID2

Table 1 has the records:
Name | Address | Height | Color | Money | ID | ID2
David | 45kkkkk | 100tt | Black | 1000 | 1 | 1
David | 45kkkkk | 100tt | Blue | 2000 | 2 | 1
David | 45kkkkk | 100tt | Red | 3000 | 3 | 1
David | 45kkkkk | 100tt | Orange | -3000 | 4 | 1

Table 2 has the records:
Name | Address | Height | Color | Money | ID | ID2
David | 45kkkkk | 100tt | Black | 1000 | 1 | 1
David | 45kkkkk | 100tt | Blue | 2000 | 2 | 1
David | 45kkkkk | 100tt | Red | 3000 | 3 | 1

When inner joined on column ID, it returns the below three records:
David 45kkkkk 100tt Black 1000 1
David 45kkkkk 100tt Blue 2000 2
David 45kkkkk 100tt Red 3000 3

However, when joined on column ID2 it's returning:
David 45kkkkk 100tt Black 1000 1 1
David 45kkkkk 100tt Blue 2000 2 1
David 45kkkkk 100tt Red 3000 3 1
David 45kkkkk 100tt Orange -3000 4 1
David 45kkkkk 100tt Black 1000 1 1
David 45kkkkk 100tt Blue 2000 2 1
David 45kkkkk 100tt Red 3000 3 1
David 45kkkkk 100tt Orange -3000 4 1
David 45kkkkk 100tt Black 1000 1 1
David 45kkkkk 100tt Blue 2000 2 1
David 45kkkkk 100tt Red 3000 3 1
David 45kkkkk 100tt Orange -3000 4 1

I expect it to return the below instead of the above because the below are all the ID2 (1s)
David 45kkkkk 100tt Black 1000 1 1
David 45kkkkk 100tt Blue 2000 2 1
David 45kkkkk 100tt Red 3000 3 1
David 45kkkkk 100tt Orange -3000 4 1



Why is Access duplicating these records? I know it's because it's matching the first table to the second table by ID2 three times for each 1 record in table 1 (so 3x4) = 12. Is there a way to avoid this?

In the end, what I really want is the three matches below:
David 45kkkkk 100tt Black 1000 1
David 45kkkkk 100tt Blue 2000 2
David 45kkkkk 100tt Red 3000 3
and this below record too (it doesn't match anything on ID1 but matches on ID2...Is there a way to pull in matches for ID2 without duplicating ID1 matches?)
David | 45kkkkk | 100tt | Orange | -3000 | 4 | 1

I did a SELECT DISTINCT SQL query to get the above four results. However, when I group them by the Money column and Sum them, it sums even the non-distinct records too. Is there a way to sum only the distinct records?

[Nonaluuluu]

Picture 1 below has matches based on Column IndexAll (Which is Name, Address, Height, Color, Money) all concatenated as an Index number. These three records are common to both table 1 and table 2.
https://imgur.com/TS1kNbv




Picture 2 below has matches based on Column IndexWO (Which is Name, Address, Height) all concatenated as an Index number. These four records are common to both table 1 and table 2. I used a DISTINCT SELECT to only get unique matches.
https://imgur.com/FfE6Tlp


Picture 3 below has all records that are in picture 2 that aren't in picture 1.
https://imgur.com/t0ImBZP


I want to have the Column for Amount in Picture 3 alongside Picture 1 and can't figure out how to do that.

[Nonaluuluu]

help me dude

[alansidman]

Before addressing your issue, I have a question. Why do you have two tables containing the same information. This is contrary to RDBMS theory and normalization. Suggest you look at this link on design of a data base.

http://r937.com/relational.html