In other words, I would expect this:
| Payer | ClientName | Client_ID | Therapy | SumOfNumber_UnitsBilled | Billed_By | Res_Out | Date_Of_Service | Original_UnitsBilled |
| MLTSS | Conn, Roy | 600145 | Structured Day | -6 | Yashica Walker | Residential | 01-Dec-23 | 8 |
| MLTSS | Conn, Roy | 600145 | Structured Day | -6 | Yashica Walker | Residential | 01-Dec-23 | 10 |
| MLTSS | Ruez, Carl | 600136 | Structured Day | 35 | Jesusa Organista | Outpatient | 01-Dec-23 | 17 |
| MLTSS | Ruez, Carl | 600136 | Structured Day | 35 | Qwana Williams | Outpatient | 01-Dec-23 | 18 |
As I said earlier, the fly in the ointment is that you didn't use Original_UnitsBilled as a way to further correlate the query. Based on the theory that if it appears in a JOIN ... ON sequence it must be a key field in that table, you have TWO records in one table
and TWO records in the other table that match all three of your named (implied) keys: Payer, Client_ID, and Therapy. Yet they have two different values for Original_UnitsBilled, so to Access they are distinct records. If you had included a single-field key such as an autonumber, then included each record's autonumber key, you would have seen pairings.
Let me state it another way. What Access does is it looks at the first record in table 2 to match the "ON" fields of table 1. It returns a result. Now it checks for another record in table 2 that matches the "ON" fields of table 1. AND IT FINDS ONE! But then it runs out of matches. SO... it looks at the next record in table 1 and finds table 2's matching fields... AND IT FINDS TWO MORE to be returned. Then it checks for the NEXT client ID that matches the PAYER and Therapy and it finds a match in table 1. So... like the above, it finds a total of FOUR COMBINATIONS that match the second pair of records. That is how you get eight records.
So the next question is this: WHY DID YOU BOTHER to include table 2? You specified Payer and Therapy. If you had simply selected from table 1 by itself and used Payer and Therapy in a WHERE clause, there would have been no duplication.
Think of it THIS way: If you have a one-to-many relationship, you write a query to return the fields from table 1 and EVERY MATCHING RECORD from table 2. Well, this is no different except that in the one-many case, you have only one record on the table 1 side of the query. Here, you had TWO records on the table 1 side. Does this help?
