Need help with query

[Loony22]

In my current DB there are the following Tables and Queries

TblPatient :

PatientId
LastName
FirstName
HostelId
DateOfEnterance (the date the patient entered)

TblEconomyPayment :

EconomyPaymentId
PatientId
ReferenceId
PamentDate
PaymentMethod
SumPayed
PaymentForMonth (for what month within the year did the patient payed)
PaymentForYear (for what year did the patient payed)


TblHostelInfo :

EconomyPayment ( the price needed to pay each month)

QryEconomyPaymentDebt1 :
SELECT TblEconomyPayment.PaymentForYear, TblPatient.FirstName, TblEconomyPayment.PaymentForMonth, TblPatient.LastName, TblPatient.PatientId, TblPatient.CurrentStatus, Sum(TblEconomyPayment.SumPayed) AS TotalSumPayed
FROM TblPatient INNER JOIN TblEconomyPayment ON TblPatient.PatientId = TblEconomyPayment.PatientId
GROUP BY TblEconomyPayment.PaymentForYear, TblPatient.FirstName, TblEconomyPayment.PaymentForMonth, TblPatient.LastName, TblPatient.PatientId, TblPatient.CurrentStatus;

QryEconomyPaymentDebt2:
SELECT [TblHostelInfo].EconomyPayment-QryEconomyPaymentDebt.TotalSumPayed AS Debt, QryEconomyPaymentDebt.PaymentForYear, QryEconomyPaymentDebt.FirstName, QryEconomyPaymentDebt.PaymentForMonth, QryEconomyPaymentDebt.LastName, QryEconomyPaymentDebt.CurrentStatus, QryEconomyPaymentDebt.PatientId, QryEconomyPaymentDebt.TotalSumPayed
FROM QryEconomyPaymentDebt, TblHostelInfo ;


Now, as u see, when running the 2nd query the result will be: PatientId, LastName, FirstName, TotalSumPayed and
Debt: (TblHostelInfo].EconomyPayment-QryEconomyPaymentDebt.TotalSumPayed).

I would like to "upgrade" the query. If the patient didn't pay any kind of fee to EconomyPayment, the query will show that he has 0 TotalSumPayed in the specific month. However for example, if the patient's "DateOfEnterance" is 28/06/2002, he won't have debts for the 5/2002 and less.Of course, the patient won't have debts for the 09/2002 (its in the future ).
Just for the record, Payments Takes place each month.
Moreover, the current query shows only the one's who paid and not the one's who didn't.

 

[Jon K]

It seems you want a query that can display the whole ledger of patient payment accounts. It is too much for a query.

If you know how to write VBA, you can consider calculating the amounts and displaying the account for a patient on a form when the patient is selected or entered on the form.

Besides, it seems you need to have a field in your table to record the date on which a patient is discharged.

And judging from the way table TblHostelInfo is used in the query, I believe it is a single-cell place holder for the current monthly payment rate. If the rate changes in the future, you have no way of applying the old rate to the old records.

 

[Loony22]

Thank you very much. Well, i don't mind doing that on Vb script, its just that i don't know how .
Moreover, you are really right about the payment fee for each month. Do you have a good solution to the problem?

Thank you in advance.

 

[Jon K]

If you enter the monthly fee in each record in the table TblEconomyPayment, you can get rid of the single-cell table TblHostelInfo and the second query.

Just add a MonthlyFee field in the table TblEconomyPayment. (You can set the field's Default Value to the amount of the current monthly fee so that the fee will be added automatically for any new records.)

Manually (or with an update query if there are many records in the table) add the monthly fee in the MonthlyFee field for the existing records.

And edit your first query. Add the following at the end of the Select clause:-
, Last(TblEconomyPayment.MonthlyFee) AS MonthlyFee, MonthlyFee - TotalSumPayed AS Debt

Run this edited query.