Query for Late Payments - Partial payment quandry

[boblarson]

Hi all:

Well, as part of my new job I get to revise a current database for a client and I am hitting a brick wall in my mind at the moment and could use some ideas thrown my way on how to deal with it.

First of all I have two tables -

Table AR_Due
AR_Due_ID - Autonumber (PK)
Entity_ID - Long Integer (FK)
AR_Due_Amt - Currency
AR_Due_Date - Date/Time
AR_Due_Waive (boolean and if checked the amt due is forgiven and not due)

Table AR_Pay
AR_Pay_ID - Autonumber (PK)
AR_Due_ID - Long Integer (FK)
AR_Pay_Date - Date/Time
AR_Pay_Amt - Currency


Now, I have to pull all AR_Due where the payments are late (past the AR_Due_Date) and have no in the Waive column and, if partial payments were made those payments can't be past the due date or else they need to be included as late (that's my main issue - trying to figure out the logic on that one).

So, any ideas on how that might be best accomplished? I need a report that shows all late payments (not counting lates more than two years ago) including any where there were partial payments made but the total partial payments were not made before the due date.

I'll take any ideas, although I might not be able to use some of them due to having to follow certain standards here (don't know all of them yet but I've kind of figured a few out based on the existing db). And, I didn't put this all together so there are some things I can't change (at least at this point), but I'm happy to get any ideas that might spark the "aha" moment .

 

[Uncle Gizmo]

Well I'm not too sure if I can be much help, but at least I can ask some questions which may trigger that gem of an idea you are looking for.

If I follow you correctly, table "Table AR_Due" refers to a single amount "AR_Due_Amt" due on a particular day "AR_Due_Date" any partial and indeed whole amounts paid against this amount due are stored in the associate table " Table AR_Pay" where the amount paid, and the date paid are stored.

If this is correct then I think I would be inclined to create a set of queries, the first one would be to match the sum of the paid amounts against the due amount, and eliminate them from the query results. So you would be left with unpaid, partially paid, and also overpaid amounts. You could also use this query to exclude older than two years and those that have no in the Waive column.

 

[Uncle Gizmo]

I would expect to see invoice numbers?

So I am wondering is this bit:

Table AR_Due
AR_Due_ID - Autonumber (PK)

Is it an invoice number? Or does it relate to multiple invoices batched into a statement or some other entity?

 

[boblarson]

The AR Due ID is what is being used as an "invoice number" so-to-speak. They really aren't producing official Invoices (this is a small non-profit) and so I think that's why the person who designed this did it this way.

 

[ajetrumpet]

Now, I have to pull all AR_Due where the payments are late (past the AR_Due_Date) and have no in the Waive column and, if partial payments were made those payments can't be past the due date or else they need to be included as late (that's my main issue - trying to figure out the logic on that one).

As for this portion Bob, I assume you have payment data somewhere? If you do, it should be pretty easy shouldn't it? Especially if any of the payments have dates and invoice ID numbers attached to them. I would think if you had this info, you're problem would be solved. Do you not have this?

 

[pbaldy]

I didn't think it sounded that tough either, which makes me assume I'm misunderstanding something. What are the chances for a sample db and the expected results of the query?

 

[ajetrumpet]

What are the chances for a sample db and the expected results of the query?

Probably slim to none.

 

[Rich1968]

Ar

Hey Bob,

Im trying to figure this out also. Any suggestions?

Rich De Gray