The problem being, I have a paydate of 11/25 @ 786$, and i have 3 bills that are due on 11/25, so when I link the queries, it is adding 786$ for all three 11/25 dates that appear due to expenses.
Depending on the rest of the data being returned, UNIQUE VALUES may be quite correct, but if not, also check out UNIQUE ROWS.
Just to clarify, if you are saying you get multiple expense records (different ones) this is how it's supposed to work. You have joined the one side of a relationship (the pay period) to the many side of another (multiple expenses in that period) hence you get the pay period data repeated for each expense. Getting the data is step one; step two would be to split the data in a form/subform with the pay period data in the main form and the detail records (expenses) in the sub form. I suppose you could use a split form, but I never cared for them much.
Not sure I see the value in having a month-year table as opposed to getting the DatePart of a pay period. One thing that may cause an issue later on is when you run into pays that start near the end of one month and end in the beginning of the next. When linking these dates to a month-year table, I'd expect problems.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.