Would be helpful to know what your table structure looks like.
Do you have separte Invoice and Receipts tables? If so, you might start
with a query that looks something like:
Query1:
SELECT I.ClientID, I.InvoiceNo, First(I.DueDate) as DueDate,
First(I.AmountDue) as AmountDue,
Max(R.DatePaid) as LastPayment, Sum(R.Received) as
TotalReceipts
FROM tbl_Invoices as I
LEFT JOIN tbl_Receipts as R
ON I.InvoiceNo = R.InvoiceNo
GROUP BY I.ClientID, I.InvoiceNo
HAVING First(I.AmountDue) - Sum(R.Received) > 0
Once you have this query working, you can create a second query to actually
Group the amounts by 30, 60, 90 days. To do this, I would probably create
another table (tbl_Timeperiods) that contains fields for:
StartDiff, EndDiff, and RangeDesc
0 30 On Time
31 60 >30
61 90 >60
90 999 >90
Then you your next query might look something like:
Select Q.ClientID, T.RangeDesc, Sum(Q.AmountDue - Q.TotalReceipts) as Due
FROM Query1 as Q, tbl_TimePeriods as T
WHERE DateDiff("d", Q.DueDate, NZ(Q.LastPayment, Date())
BETWEEN T.StartDiff AND T.EndDiff
Group By Q.ClientID, T.RangeDesc
This method would give results like:
ClientID RangeDesc Due
1 >30 $25
1 >60 $75
1 >90 $100
2 >30 $25
2 >90 $75
This query uses the computed date difference between the due date and either
the last payment date (or the current date if no payments have been made)
and then compares this to the StartDiff and EndDiff values in the table
above to determine which range group it falls in. I prefer this over the
method below because this gives me flexability if I want to change the range
values (all I have to do is change a couple of values in a table as opposed
to having to hard code the ranges in the query below).
You could then take this and create a crosstab query out if it if you need
all the values for a particular client in a single record.
Another way to approach the second query would be:
SELECT Q.ClientID,
Sum(IIf(DateDiff("d",[DueDate],NZ([LastPayment],Date()))<=30,[AmountDue]-[TotalReceipts],Null))
AS [30 or Less],
Sum(IIf(DateDiff("d",[DueDate],NZ([LastPayment],Date())) Between 31 And
60,[AmountDue]-[TotalReceipts],Null)) AS [>30],
Sum(IIf(DateDiff("d",[DueDate],NZ([LastPayment],Date())) Between 61 And
90,[AmountDue]-[TotalReceipts],Null)) AS [>60],
Sum(IIf(DateDiff("d",[DueDate],NZ([LastPayment],Date()))>90,[AmountDue]-[TotalReceipts],Null))
AS [>90]
FROM qry_Invoice_Receipts AS Q
GROUP BY Q.ClientID;
HTH
Dale
