Access-queries

[ilanaz]

Hi,

I have a primary table including names of people. Related to it is a second table saying how much they have to pay for a treatment ("treatment_price&quotand a third table saying how much they paid ("amount_paid&quot(they still owe some money).

I want to make a query to know the balance of each person, i.e. how much they have to pay minus how much they did pay.

Say, for example, Mr. Luigi had 1 treatment of 50 $ and he paid in 2 times, once 40$ and once 5$ (He owes 5$). But when I do the query including those 3 tables, for Mr. Luigi, Access would make 2 rows, one saying he did a treatment of 50 $ and paid 40$ and ANOTHER ROW saying he did a treatment of 50 $ and paid 5$. In other words, Access writes all the possibilities between those tables.

So if, as a formula, I do the sum of the treatment prices minus the sum of the amounts paid, it will make (50+50)-(40+5), making the balance due of 55$ instead of 5$.

Is there a way to correct this??

Thank you,

 

[koresnordic]

Do they only have one treatment each? If so then use a query to sum the amount paid, and use the result of that in place of of your amountpaid table. If not then you will need to have another form of identifier to say which treatment they are paying for.



Graham