00-30/31-60/61-90/Over-90 Aging

[Gary B]

How can i do this:

tbl Patients: PatKey(pk); PatientNo; PatientName

tblCharges: ChrgKey(pk); PatKey(fk); ChrgDate

tblServices: ServsKey(pk); ChrgKey(fk), PatientFee; InsuranceFee

tblPayments: PmtKey(pk); PmtType(1=PatPmt; 2=InsPmt); PmtDate; PmtAmount


I want to display PatientNo, PatientName, [00-30](Chrgs-Pmts),
[31-60](Chrgs-Pmts), [61-90](Chrgs-Pmts), [Over-90](Chrgs-Pmts)

Thank you very much!

 

[Steve Schapel]

Gary,

As far as I can see, you don't have any way to relate the payments to
the patients.

Can you give an example of what [00-30](Chrgs-Pmts),
[31-60](Chrgs-Pmts), [61-90](Chrgs-Pmts), [Over-90](Chrgs-Pmts) is
supposed to designate? Might make your meaning clearer. Thanks.

 

[Gary B]

Updated Table Structure... sorry:

tbl Patients: PatKey(pk); PatientNo; PatientName

tblCharges: ChrgKey(pk); PatKey(fk); ChrgDate

tblServices: ServsKey(pk); ChrgKey(fk), PatKey(fk); PatientFee; InsuranceFee

tblPayments: PmtKey(pk); PatKey(fk); PmtType(1=PatPmt; 2=InsPmt); PmtDate;
PmtAmount

I want to display PatientNo, PatientName, [00-30](Chrgs-Pmts),
[31-60](Chrgs-Pmts), [61-90](Chrgs-Pmts), [Over-90](Chrgs-Pmts)

[00-30](Chrgs-Pmts) = I want this column to be the monitary value of all
(Chrgs - Pmts) that occurred between 0 and 30 days ago;

[31-60](Chrgs-Pmts) = I want this column to be the monitary value of all
(Chrgs - Pmts) that occurred between 31 and 60 days ago;

I am lousy at expressing myself!

Thanks again!

 

[John Spencer (MVP)]

Take a look at the Partition function. It should do what you want.

 

[Dale Fye]

Gary,

From my perspective, you may be missing the relationship between a payment
and a specific charge. Without this, your (Chrgs-Pmts) figure for a
particular time period could be negative (no charges, but payments
received). You also don't indicate whether you want to only display the
PatientFee and Patient PmtType in your computations. Furthuremore, I think
your structure overlooks the potential (frequent in my case) instance where
the insurance company denies part or all of the claim; what do you do then?

Let me think on this one for a while and I'll get back to you. In the
meantime, maybe someone else has already dealt with this situation and can
respond.

Dale

 

[Steve Schapel]

I agree with Dale. I don't think you will be able to get the result you
want with the table structure you have presented.

 

[Ken Snell [MVP]]

I do this in one of my applications to generate an "aged receivables"
report, which sums according to how old a charge is and shows the balances
for each customer for each time window. (I admit that I didn't know about
the Partition function when I wrote this part of the application - I think
it would simplify things a lot if I had used it).

Assuming that you have data in the tables that relates each payment and each
charge back to a specific invoice, and that each invoice relates back to a
specific customer, my setup uses three cascading queries.

The first provides a sum of the payments made for each invoice, providing
these outputs:
InvoiceNumber
TotalPaymentsMadeToDate

The second query uses the first query and the table with "total amount due"
for each invoice to get a list of all invoices, their amounts that were due
on the invoice, and the total amount of payments made for that invoice.
Outputs are
InvoiceNumber
InvoiceDate
OriginalAmountDue
TotalPaymentsMadeToDate

The third query then uses the second query to build the data for the final
report for each invoice - namely, the invoice number, customer's
information, the total amount of money that was billed on the invoice, the
total amount that has been paid on the invoice, the unpaid amount remaining
for the invoice, and a series of calculated fields that look at the invoice
date and compare to the date of the report to calculate how many days have
passed -- and if the number of days fits within that window (e.g., 0 - 30
days; 31 - 60 days), displays the "unpaid" amount in that field, else it
displays a Null value.

The report that is run from the third query then sums the data as desired
for the outputted report.

Not overly complex, but it did take me a while to work it all out. Partition
function would be used in place of my calculated fields in the third query.
--

Ken Snell
<MS ACCESS MVP>

Gary,

From my perspective, you may be missing the relationship between a payment
and a specific charge. Without this, your (Chrgs-Pmts) figure for a
particular time period could be negative (no charges, but payments
received). You also don't indicate whether you want to only display the
PatientFee and Patient PmtType in your computations. Furthuremore, I
think your structure overlooks the potential (frequent in my case)
instance where the insurance company denies part or all of the claim; what
do you do then?

Let me think on this one for a while and I'll get back to you. In the
meantime, maybe someone else has already dealt with this situation and can
respond.

Dale

Updated Table Structure... sorry:

tbl Patients: PatKey(pk); PatientNo; PatientName

tblCharges: ChrgKey(pk); PatKey(fk); ChrgDate

tblServices: ServsKey(pk); ChrgKey(fk), PatKey(fk); PatientFee;
InsuranceFee

tblPayments: PmtKey(pk); PatKey(fk); PmtType(1=PatPmt; 2=InsPmt);
PmtDate; PmtAmount

I want to display PatientNo, PatientName, [00-30](Chrgs-Pmts),
[31-60](Chrgs-Pmts), [61-90](Chrgs-Pmts), [Over-90](Chrgs-Pmts)

[00-30](Chrgs-Pmts) = I want this column to be the monitary value of all
(Chrgs - Pmts) that occurred between 0 and 30 days ago;

[31-60](Chrgs-Pmts) = I want this column to be the monitary value of all
(Chrgs - Pmts) that occurred between 31 and 60 days ago;

I am lousy at expressing myself!

Thanks again!

 

[Gary B]

I agree with Dale. I don't think you will be able to get the result you

want with the table structure you have presented.

No, I disagree.

My 30/60/90 Day report will not deal with how payments are applied to
Services, this is a job for another report. After studying Intuit's
QuickBooks, their Aging report also does not deal with how payments are
applied. Having a negative balance for a particular "time-zone" is not a bad
thing (for this report).

If you think hard a bout it, if your aging report tries to deal with applied
payments, your Aging reports potentially will not balance (on a monthly
basis) whenever there are unapplied funds.

So... Does anyone know how to write the query I need?

Thanks again

 

[Gary B]

I agree with Dale. I don't think you will be able to get the result you

want with the table structure you have presented.

No, I disagree.

My 30/60/90 Day report will not deal with how payments are applied to
Services, this is a job for another report. After studying Intuit's
QuickBooks, their Aging report also does not deal with how payments are
applied. Having a negative balance for a particular "time-zone" is not a bad
thing (for this report).

If you think hard a bout it, if your aging report tries to deal with applied
payments, your Aging reports potentially will not balance (on a monthly
basis) whenever there are unapplied funds.

So... Does anyone know how to write the query I need?

Thanks again

 

[Steve Schapel]

Gary,

Ok, now I think I am beginning to get the idea of what you want to do.

You could do it in 3 steps.

Make a query based on the tblCharges and tblServices tables, to get the
charge amounts for each of the aging periods, grouped by PatKey. For
example...
31To60Charge: Sum(IIf([ChrgDate] Between Date()-60 And
Date()-31,Nz([PatientFee],0)+Nz([InsuranceFee],0),0))

Make a query based on the tblPayments table, to get the payment amounts
for each of the aging periods, grouped by PatKey. For example...
31To60Payment: Sum(IIf([PmtDate] Between Date()-60 And
Date()-31,[PmtAmount],0))

Make a query based on the above 2 queries, Left Join to both of them
from the tblPatients table, to give for example...
31To60: Nz([31To60Charge],0)-Nz(31To60Payment],0)

Hope that will help to point you in a useful direction.

 

[Gary B]

Steve,

I love you! This is what I was looking for...

Your colleague (SC), who responded to another post, seems quite upset.

Your reponse came a bit after my re-post.

Regarding your suggestion, do you think the query would run faster if I did
a sub-query for Charges for each 30-day zone (and the payments as well); I
am thinking that the condition "WHERE ChrgDate Between Date()-60 And
Date()-31" will be faster than IIF() functions?

Additionally, do you know if IIF() and Nz() are/will be supported in SQL
Server Express?

Thanks again!

 

[Steve Schapel]

Gary,

Your colleague (SC), who responded to another post, seems quite upset.

I have just seen Steve C's response. Yes, he does seem upset. To be
honest, I also felt "reactive" when I read your post in the new thread.
I don't know whether you intended it or not, but it did seem from your
wording that you had overlooked the fact that some very knowledgeable
and experienced people had volunteered their time to help you, in spite
of the fact that your questions were unclear, your table design is poor,
and the information you have provided is inconsistent and incomplete.
None of this is unusual in these forums, of course, but it is best to
remember that when you are looking for free advice on complex concepts,
and then throw varying communication styles into the mix, it is
sometimes necessary to give the helper a bit of support and assistance
in understanding what you want.

Regarding your suggestion, do you think the query would run faster if I did
a sub-query for Charges for each 30-day zone (and the payments as well); I
am thinking that the condition "WHERE ChrgDate Between Date()-60 And
Date()-31" will be faster than IIF() functions?

Unless you have very large volumes of data, I wouldn't expect the IIf()
functions I suggested to be appreciably slow. I am sorry, I don't know
whether sub-queries would be slower or faster.

Did you look into the Partition() function as suggested by John Spencer
and Ken Snell?

Additionally, do you know if IIF() and Nz() are/will be supported in SQL
Server Express?

No, I don't know. Someone else may be able to answer this.

Thanks again!

You're welcome.

 

[Dale Fye]

I still have problems with just summing charges and payments in those
payment periods. Usually, these types of reports are designed to tell the
client how much they are overdue in payments from each of these periods of
time.

I've gotta tell you, if you sent me a billing statement that had those
summations it would confuse the hell out of me.

Dale

 

[Gary B]

I still have problems with just summing charges and payments in those
payment periods. Usually, these types of reports are designed to tell the
client how much they are overdue in payments from each of these periods of
time.

I've gotta tell you, if you sent me a billing statement that had those
summations it would confuse the hell out of me.

Dale,

I got it... you think this is a Patient Statement or a Patient Bill.

No, it is merely a A/R Report based on Patient Balances Only.

Statements or Bills would definitely incorporate a Pmts2Servs Table!

 

[Gary B]

I concede.. my apologies to all!

 

[Gary B]

Steve,

Rearding your solution...

I create and store the 2 Views (Queries) for Charges and Payments, and when
I run the Core Query that calls them, I need to be able to alter the assumed
date "Date()".

Is there a way that a Query can run and reference another query AND pass a
parameter.

Regarding Partition... I am trying to keep it "pure" SQL and not rely on
Access as we want to have a flexible database solution, be able to upgrade
to SQL Server, and want minimal conversion worries.

Thank you again, and again, and again.

 

[Steve Schapel]

Gary,

I guess it depends on what the date crieria is and where it comes from.
One common approach to this is to use an unbound textbox on a form
where the date required is entered, and then this form control is
referenced in the query, e.g. using syntax such as
[Forms]![NameOfForm]![DateFrom]