Filter customer records who have exceeded their loan period together with amount owed

[Kapay]

I’m in real trouble here. I need your help. I have two tables: Issues and Payments with the following fields.

ISSUES
ProductID
TransactionDate
Quantity
UnitCost
CreditExpiryDate (14 days after transaction date)
CustomerID

PAYMENTS
CustomerID
TransactionDate
Amount

Goods are issued to customers on credit basis. Also, a customer is allowed to take additional goods even when the current credit is not fully settled. In that case, the customer would have two or more expiry dates.

What I want to do using a query or otherwise is to come up with all customers (defaulters) whose credit expiry dates have expired and their respective amounts due. If the customer had two or more credit expiry dates, I’m only interested in the most recent date but should come up with the total outstanding credit.

 

[KKilfoil]

First of all, you shouldn't store [CreditExpiryDate] if it is ALWAYS = [TransactionDate]+14. Calculate it in a query instead. You're going to need at least one query to get what you want anyways.

Now to your problem....

If a customer bought an item for $1000 on the 1st of the month, paid $900 on the 8th, and then bought $500 more stuff on the 10th, what value of total outstanding credit should there be after the 15th? $100? $600?

After the 25th, I think you are always going to want to show credit as $600, but what about before that time?

 

[Kapay]

The Total Outstanading credit refers to Total Expired Credit.That is we're only interested in the $100 that's expired on the 15th.It will only come up with $600 if the customer fails to honour the two loans after the 25th.

 

[KKilfoil]

First, create a query, lets call it qryPastExpiry to return only those Issues that are expired. Put '<Date()-14' (without the quotes!) in the design grid under [TransactionDate]. You should also add a calculated field:

IssueAmount: UnitCost * Quantity


Next, create a summation query (call it qrySumPastExpiry) using qryPastExpiry as source. Select 'Group By' for [CustomerId] and 'Sum' for [IssueAmount].


Then, create a summation query (call it qrySumPayment) using Payments as source. Select 'Group By' for [CustomerId] and 'Sum' for [Amount].


Finally, create a select query that includes both qrySumPayment and qrySumPastExpiry. Join the two in a relationship by CustomerID. Use a left join (Include all records in SumPastExpiry and only those in SumPayments that match). Add the following fields: CustomerID, SumPayments, and SumPastExpiry, and add '>SumPayments' (without quotes!) as a criterion under SumPastExpiry. Finally, add a calculated field as follows:

PastDueBalance: SumPastExpiry - SumPayments


HTH

 

[KKilfoil]

Oops, forgot your need for the most recent date...

In qrySumPastExpiry, add TransactionDate as a field, and select 'Max' in the Total row. This will find the most recent 'expired' date.

In the last query I previously posted, add a calculated field like:

ExpiryDate: MaxTransactionDate + 14

 

[Kapay]

Thanks! It works perfectly well.