calculate value from two tables

[victor]

Attached is a database with two tables. i would like to calculate the difference between field amount(table invoice) and amount paid(payments). the problem is when i linked the two tables using patient ID it replicates the amount paid more then the number. say receipt 24 will appear for each column of patient id in the invoice table

[NoellaG]

Hi,

you need to include Invoice.[issue ID] to the table Payments to be able to see which payment is for which invoice. In fact you probably need at minimum the following tables:
*tblPatients
*tblInvoices
*eventually tblInvoiceLines if one invoice can have more then one productline
*eventually tblProducts(drug, consultation, ...)
*tblPayments: this should include which patient pays for which invoice

For the moment all you can do is make two totalsqueries: one for the amount due per patient and one per amount payed per patient. Then you link those two queries by the patient ID. Then you'll be able to see if the totals match.

succes
NG

PS: it's bad practice to use spaces in object names, this only leads to trouble later on.

[NTC]

Sorry I don't look at attachments; in general - if one wants to solve this at the query level; when one table has multiple records - make a query that results in one record...then make a second query that links the first query with the other table. That first query may be an aggregate query or distinct query depending on the correct solution.

One can also approach this from at the form/report level using main/sub forms/reports and do comparative math between appropriate fields. This approach may or may not be right depending on the situation.

Hope this helps a little.

[ajetrumpet]

Attached is a database with two tables. i would like to calculate the difference between field amount(table invoice) and amount paid(payments). the problem is when i linked the two tables using patient ID it replicates the amount paid more then the number. say receipt 24 will appear for each column of patient id in the invoice table

VICTOR,

your data is very much disoriented. it needs to be normalized. try this to fix your current problem though:

SELECT DISTINCT 

Invoice.[Patient ID], 

NZ(DSUM("[QUANTITY] * [PRICE]", "INVOICE", 

"[PATIENT ID] = " & [PATIENT ID]), 0) AS TotalPurchased, 

NZ(DSUM("[AMOUNT PAID]", "PAYMENTS", 

"[PATIENT ID] = " & [PATIENT ID]), 0) AS TotalPaid, 

[TotalPurchased] - [TotalPaid] AS CurrentBalance

FROM Invoice;