Microsoft 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,

 

[Zathras]

Assume three tables:

PATIENTS
--------
PATIENT (number)
NAME    (text)

BILLINGS
--------
BILLINGNUM   (number)
PATIENT      (number)
CHARGEAMOUNT (currency)

RECEIPTS
--------
RECEIPTNUM     (number)
PATIENT        (number)
AMOUNTRECEIVED (curency)

Assume the following data:

PATIENTS
--------
 101, LUIGI
 102, SMITH

BILLINGS
--------
 1, 101, $40.00
 2, 101, $50.00
 3, 102, $60.00
 4, 102, $70.00

RECEIPTS
--------
 1, 101, $30.00
 2, 101, $45.00
 3, 102, $65.00
 4, 102, $64.00

Then, create this query as BILLINGQUERY:

SELECT PATIENTS.PATIENT, PATIENTS.NAME, Sum(BILLINGS.CHARGEAMOUNT) AS SumOfCHARGEAMOUNT
FROM PATIENTS INNER JOIN BILLINGS ON PATIENTS.PATIENT = BILLINGS.PATIENT
GROUP BY PATIENTS.PATIENT, PATIENTS.NAME;

And finally, this query:

SELECT BILLINGQUERY.PATIENT, BILLINGQUERY.NAME, BILLINGQUERY.SumOfCHARGEAMOUNT, Sum(RECEIPTS.AMOUNTRECEIVED) AS SumOfAMOUNTRECEIVED
FROM BILLINGQUERY INNER JOIN RECEIPTS ON BILLINGQUERY.PATIENT = RECEIPTS.PATIENT
GROUP BY BILLINGQUERY.PATIENT, BILLINGQUERY.NAME, BILLINGQUERY.SumOfCHARGEAMOUNT;

Produces this result:

PATIENT   NAME     SumOfCHARGAMOUNT  SumOfAMOUNTRECEIVED
-------   ------   ----------------  -------------------
  101     LUIGI         $90.00            $75.00
  102     SMITH        $130.00           $129.00

It seems like there ought to be a way to create a 3-table join with one query, but I couldn't quite get it to work that way.

 

[lespaul]

Couldn't you name the secondquery, PAYMENTINQUIRY and then subtract the total due from the amount paid and end up with the balance due?

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Zathras]

Leslie, of course you could. But that is just a variation on the theme. The essential problem was avoiding the Cartisian product in the result set.

One must be able to walk before starting to run.

An advantage of keeping charges and payments separate in the query is that it allows the user to do additional arithmetic if desired. For example, one patient owing $40 on a $400 billing may not need special attention, while another owing $40 on a $40 billing might need a dunning notice.

Also, in production the actual queries would include additional data such as last billing date, last payment date, total number of billings, total number of payments, etc.