Search code examples
sqlt-sqlms-accessms-access-2016

Access SQL SUM with subtraction

I am writing what I thought would be a relatively simple Access DB for a doctor client of mine who has a very small practice. I am having trouble with reporting a patient's balance.

I have 3 tables:

Patients (id, [First Name], [Last Name], {other cols})
Treatments (id, Fee, Patient_id, {other cols})
Payments (id, Amount, Patient_id, {other cols})

I want a query to simply show me the total fees for treatments, the total amount paid, and the current balance. I wrote the following...

Select 
 Patients.[Last Name],
 Patients.[First Name], 
 SUM(select SUM(Treatments.Fee) from Treatments Where Treatments.Patient=@PatientID)
AS CHARGES, 
 SUM(select SUM(PAYMENTS.AMOUNT) from PAYMENTS Where PAYMENTS.Patient=@PatientID)
AS PAYMENTS, 
SUM(
 (select SUM(Treatments.Fee) from Treatments Where Treatments.Patient=@PatientID)
- (select SUM(PAYMENTS.AMOUNT) from PAYMENTS Where PAYMENTS.Patient=@PatientID)
)
as Balance
FROM Patients,Treatments,PAYMENTS
WHERE Patients.ID = @PatientID
GROUP BY Patients.[Last Name],Patients.[First Name]

The Charges and Payments columns work fine, but the Balance I'm given is weird. The below is based on:

  1. 5 treatment entries for this patient in the amounts, 50,25,35,45,125 (280).
  2. 3 payment entries in the amounts, 15,60,25 (100).

I expect: Charges = $280, Payments = $100, Balance = $180

But I get: Charges = $280, Payments = $100, Balance = $2,700

Solution

  • By using this FROM clause:

    FROM Patients,Treatments,PAYMENTS

    you're creating a Cartesian product. Meaning your sum calculation for Balance will be multiplied by the number of records in each respective table (1 in Patients, 5 in Treatments, 3 in PAYMENTS):

    • 15*total of fees- 15*total of payments = 15*280-15*100 = 4200-1500 =2700

    You don't need Treatments and PAYMENTS in your FROM clause as you're doing your calculations in your sub query. Use this statement instead:

    Select 
 Patients.[Last Name],
 Patients.[First Name], 
 SUM(select SUM(Treatments.Fee) from Treatments Where Treatments.Patient=@PatientID)
AS CHARGES, 
 SUM(select SUM(PAYMENTS.AMOUNT) from PAYMENTS Where PAYMENTS.Patient=@PatientID)
AS PAYMENTS, 
SUM(
 (select SUM(Treatments.Fee) from Treatments Where Treatments.Patient=@PatientID)
- (select SUM(PAYMENTS.AMOUNT) from PAYMENTS Where PAYMENTS.Patient=@PatientID)
)
as Balance
FROM Patients
WHERE Patients.ID = @PatientID
GROUP BY Patients.[Last Name],Patients.[First Name]