B
Bob Vance
I never envisaged this problem and I suppose its my fault for not having a
close off period after each month, but you learn as you go
Basically my statements are calculated by Invoice Totals and Payments made
against them the query is "qPayableTotalForPayment".
My monthly Overdue starts with "qOwnerPercentAmountInPaymentMethodAll"
SELECT tblOwnerInfwnerID,
IIf((Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0))>1,0,1)
AS SortVal,
IIf(IsNull(tblOwnerInfwnerLastName),'',tblOwnerInfwnerLastName & ', ')
& " " &
IIf(IsNull(tblOwnerInfwnerFirstName),'',tblOwnerInfwnerFirstName) AS
Name, qTotalDueForPayment.TotalDue AS DueAmount,
qPaidAmountForPayment.PaidTotal AS Paid,
Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0) AS
Payable, IIf(Not IsNull(),"e") AS EmailAlert1,
Format([MaxOfBillDate],"d-mmm-yy") AS MyDate,
qPaidAmountForPayment.MaxOfBillDate,
Switch(DateDiff("d",[MaxOfBillDate],Date())<30 And
[Payable]>1,Null,DateDiff("d",[MaxOfBillDate],Date())<60 And
[Payable]>1,"1",DateDiff("d",[MaxOfBillDate],Date())<90 And
[Payable]>1,"2",DateDiff("d",[MaxOfBillDate],Date())<120 And
[Payable]>1,"3",DateDiff("d",[MaxOfBillDate],Date())>121 And
[Payable]>1,"4+",[MaxOfBillDate] Is Null And [Payable]>1,"N.P.") AS
LastPayment,
IIf(IsNull(tblOwnerInfwnerLastName),'',tblOwnerInfwnerLastName & ', ')
& " " &
IIf(IsNull(tblOwnerInfwnerFirstName),'',tblOwnerInfwnerFirstName) AS
NameTest
FROM (tblOwnerInfo LEFT JOIN qPaidAmountForPayment ON tblOwnerInfwnerID =
qPaidAmountForPayment.OwnerID) LEFT JOIN qTotalDueForPayment ON
tblOwnerInfwnerID = qTotalDueForPayment.OwnerID
WHERE (((tblOwnerInfo.Status) Like 'Active*'))
ORDER BY
IIf((Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0))>1,0,1),
IIf(IsNull(tblOwnerInfwnerLastName),'',tblOwnerInfwnerLastName & ', ')
& " " &
IIf(IsNull(tblOwnerInfwnerFirstName),'',tblOwnerInfwnerFirstName);
Then My Overdue 1 month, 2 Month, 3+Months
SELECT qOwnerPercentAmountInPaymentMethodAll.OwnerID,
Sum(qOwnerPercentAmountInPaymentMethodAll.AmountSummary) AS Dues,
iif(qOwnerPercentAmountInPaymentMethodAll.AmountSummary<0,3,IIf(MonthsDue([OnDate])>=3,3,IIf(MonthsDue([OnDate])>=2,2,IIf(MonthsDue([OnDate])>=1,1,0))))
AS MD
FROM qOwnerPercentAmountInPaymentMethodAll
GROUP BY qOwnerPercentAmountInPaymentMethodAll.OwnerID,
IIf(qOwnerPercentAmountInPaymentMethodAll.AmountSummary<0,3,IIf(MonthsDue([OnDate])>=3,3,IIf(MonthsDue([OnDate])>=2,2,IIf(MonthsDue([OnDate])>=1,1,0))));
What I would like to do is to get a Sum() for 3+Months going into
"qOwnerPercentAmountInPaymentMethodAll"
As you can see if a client has been with me for 10 years to work his overdue
out the query has to go back that far but if I had a another query that had
the total from 3 months back and the above would only need to calculate the
last 2 Months. when creating a Report for a client that has been with me for
5 years can take 30sec to create but a client that has been with me for 4
months 5sec, If some only could point me in the right direction
Please........Bob
close off period after each month, but you learn as you go
Basically my statements are calculated by Invoice Totals and Payments made
against them the query is "qPayableTotalForPayment".
My monthly Overdue starts with "qOwnerPercentAmountInPaymentMethodAll"
SELECT tblOwnerInfwnerID,
IIf((Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0))>1,0,1)
AS SortVal,
IIf(IsNull(tblOwnerInfwnerLastName),'',tblOwnerInfwnerLastName & ', ')
& " " &
IIf(IsNull(tblOwnerInfwnerFirstName),'',tblOwnerInfwnerFirstName) AS
Name, qTotalDueForPayment.TotalDue AS DueAmount,
qPaidAmountForPayment.PaidTotal AS Paid,
Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0) AS
Payable, IIf(Not IsNull(),"e") AS EmailAlert1,
Format([MaxOfBillDate],"d-mmm-yy") AS MyDate,
qPaidAmountForPayment.MaxOfBillDate,
Switch(DateDiff("d",[MaxOfBillDate],Date())<30 And
[Payable]>1,Null,DateDiff("d",[MaxOfBillDate],Date())<60 And
[Payable]>1,"1",DateDiff("d",[MaxOfBillDate],Date())<90 And
[Payable]>1,"2",DateDiff("d",[MaxOfBillDate],Date())<120 And
[Payable]>1,"3",DateDiff("d",[MaxOfBillDate],Date())>121 And
[Payable]>1,"4+",[MaxOfBillDate] Is Null And [Payable]>1,"N.P.") AS
LastPayment,
IIf(IsNull(tblOwnerInfwnerLastName),'',tblOwnerInfwnerLastName & ', ')
& " " &
IIf(IsNull(tblOwnerInfwnerFirstName),'',tblOwnerInfwnerFirstName) AS
NameTest
FROM (tblOwnerInfo LEFT JOIN qPaidAmountForPayment ON tblOwnerInfwnerID =
qPaidAmountForPayment.OwnerID) LEFT JOIN qTotalDueForPayment ON
tblOwnerInfwnerID = qTotalDueForPayment.OwnerID
WHERE (((tblOwnerInfo.Status) Like 'Active*'))
ORDER BY
IIf((Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0))>1,0,1),
IIf(IsNull(tblOwnerInfwnerLastName),'',tblOwnerInfwnerLastName & ', ')
& " " &
IIf(IsNull(tblOwnerInfwnerFirstName),'',tblOwnerInfwnerFirstName);
Then My Overdue 1 month, 2 Month, 3+Months
SELECT qOwnerPercentAmountInPaymentMethodAll.OwnerID,
Sum(qOwnerPercentAmountInPaymentMethodAll.AmountSummary) AS Dues,
iif(qOwnerPercentAmountInPaymentMethodAll.AmountSummary<0,3,IIf(MonthsDue([OnDate])>=3,3,IIf(MonthsDue([OnDate])>=2,2,IIf(MonthsDue([OnDate])>=1,1,0))))
AS MD
FROM qOwnerPercentAmountInPaymentMethodAll
GROUP BY qOwnerPercentAmountInPaymentMethodAll.OwnerID,
IIf(qOwnerPercentAmountInPaymentMethodAll.AmountSummary<0,3,IIf(MonthsDue([OnDate])>=3,3,IIf(MonthsDue([OnDate])>=2,2,IIf(MonthsDue([OnDate])>=1,1,0))));
What I would like to do is to get a Sum() for 3+Months going into
"qOwnerPercentAmountInPaymentMethodAll"
As you can see if a client has been with me for 10 years to work his overdue
out the query has to go back that far but if I had a another query that had
the total from 3 months back and the above would only need to calculate the
last 2 Months. when creating a Report for a client that has been with me for
5 years can take 30sec to create but a client that has been with me for 4
months 5sec, If some only could point me in the right direction
Please........Bob