thardyjackson
Registered User.
- Local time
- Yesterday, 23:42
- Joined
- May 27, 2013
- Messages
- 45
Things were going fine :banghead: ....
I'm building a query that creates customized SMS messages based on customer payment statistics.... "Your balance is $100 and please make a payment by Dec 1, 2013."
I have a field called [smsIdentifierToUse] that determines which SMS "template" to use for each customer payment. Values can be smsPmtComplete, smsPmtRcvdDefault, smsPmtRcvdPastDueDate, etc.. I couldn't find a way to have a true template for each [smsIdentifierToUse] value so I've done concatenation in a weird way... Every SMS may or may not have certain phrases like "You're past due" or "Thanks for your payment of .." I build each message by concatenating (or not) each phrase relevant to the particular [smsIdentifierToUse] value. Because the expression was so long I had to break up the expression into 4 parts [msgPart1], [msgPart2], etc that also get concatenated together.
Anyway ... the overall query:
* works quickly before any concatenation
* becomes extremely sluggish to view the results after I add the 4 [msgPart#] expressions to the query
* generates "query too complex" if I add 1 final concatenation expression [msgPart1] & [msgPart2] & [msgPart3] & [msgPart4] AS message,
I know Excel fairly well but am new to Access. Using Access 2007 and Windows 7.
Advice?
Here's the full SQL (red section is the crazy concatenation) I added line breaks for easier viewing:
I'm building a query that creates customized SMS messages based on customer payment statistics.... "Your balance is $100 and please make a payment by Dec 1, 2013."
I have a field called [smsIdentifierToUse] that determines which SMS "template" to use for each customer payment. Values can be smsPmtComplete, smsPmtRcvdDefault, smsPmtRcvdPastDueDate, etc.. I couldn't find a way to have a true template for each [smsIdentifierToUse] value so I've done concatenation in a weird way... Every SMS may or may not have certain phrases like "You're past due" or "Thanks for your payment of .." I build each message by concatenating (or not) each phrase relevant to the particular [smsIdentifierToUse] value. Because the expression was so long I had to break up the expression into 4 parts [msgPart1], [msgPart2], etc that also get concatenated together.
Anyway ... the overall query:
* works quickly before any concatenation
* becomes extremely sluggish to view the results after I add the 4 [msgPart#] expressions to the query
* generates "query too complex" if I add 1 final concatenation expression [msgPart1] & [msgPart2] & [msgPart3] & [msgPart4] AS message,
I know Excel fairly well but am new to Access. Using Access 2007 and Windows 7.
Advice?
Here's the full SQL (red section is the crazy concatenation) I added line breaks for easier viewing:
Code:
SELECT DISTINCTROW tbl_customers.nbrCustAcct, tbl_payments.ID, tbl_payments.pmtCompletedInOut, tbl_payments.pmtDate, tbl_payments.nbrCustAcctUsed,
DLookUp("[smsIdentifier]","tbl_lookupSms","[smsCategory] ='pmtReceipt'" & "And [mpesaAcct] ='" & [Expr1] & "'" & "And [nbrCustAcctValidity]='" & [nbrCustAcctValidity] & "'" & "And [customerStatus]='" & [statusCustomerAfterPmt] & "'") AS smsIdentifierToUse,
[COLOR="Red"][msgPart1] & [msgPart2] & [msgPart3] & [msgPart4] AS message,
IIf([smsIdentifierToUse]="smsSuppress",Null,Null) & IIf([smsIdentifierToUse]="smsPmtComplete","Congratulations! You have made your final Bandika na MoneyMaker payment. Your " & [productChosen] & " should be available within 1 week. KickStart MoneyMaker will send you additional instructions soon. Please call 0756 081911 if you have questions.",Null) & IIf([smsIdentifierToUse]="smsPmtNoAcctNbr","KickStart MoneyMaker received your M-PESA payment of Tsh " & [pmtCompletedInOut] & ". Please reply to this SMS with your name and the name of your KickStart MoneyMaker sales representative.",Null) AS msgPart1,
IIf([smsIdentifierToUse]="smsPmtBelowDeposit" Or [smsIdentifierToUse]="smsPmtRcvdDefault" Or [smsIdentifierToUse]="smsPmtRcvdDetailed" Or [smsIdentifierToUse]="smsPmtRcvdPastDueDate" Or [smsIdentifierToUse]="smsPmtRcvdNotOnTrack" Or [smsIdentifierToUse]="smsPmtWrongBusNbr" Or [smsIdentifierToUse]="smsPmtWrongRef" Or [smsIdentifierToUse]="smsPmtWrongRegBusNbr","Thank you for your payment of Tsh " & Format([pmtCompletedInOut],"#,##0;(#,##0);0") & " for your " & [productChosen] & ". You have paid Tsh " & [PmtSumAfterPmt] & " in total. ",Null) & IIf([smsIdentifierToUse]="smsPmtBelowDeposit","The minimum initial deposit is Tsh " & Format([depositMin],"#,##0;(#,##0);0") & ". Please send an additional Tsh " & (1*[depositMin]-1*[PmtSumAfterPmt]) & ". ",Null) & IIf([smsIdentifierToUse]="smsPmtRcvdNotOnTrack","Please continue to make periodic payments to reduce your balance. ",Null) AS msgPart2,
IIf([smsIdentifierToUse]="smsPmtRcvdDetailed" Or [smsIdentifierToUse]="smsPmtWrongBusNbr" Or [smsIdentifierToUse]="smsPmtWrongRef" Or [smsIdentifierToUse]="smsPmtWrongRegBusNbr","Your remaining balance of Tsh " & [balanceDueAfterPmt] & " is due " & [dateFinalPmtDue] & ". ",Null) & IIf([smsIdentifierToUse]="smsPmtRcvdPastDueDate","Your remaining balance of Tsh " & [balanceDueAfterPmt] & " is now overdue. Please send a payment of any amount to MoneyMaker-KS (business # 778778) using M-PESA Malipo and reference # " & [nbrCustAcct] & ".",Null) & IIf([smsIdentifierToUse]="smsPmtWrongBusNbr" Or [smsIdentifierToUse]="smsPmtWrongRef" Or [smsIdentifierToUse]="smsPmtWrongRegBusNbr","The business name and/or reference # was incorrect but we have applied the payment to your account. ",Null) AS msgPart3,
IIf([smsIdentifierToUse]=[smsIdentifierToUse]="smsPmtBelowDeposit" Or [smsIdentifierToUse]="smsPmtRcvdDefault" Or [smsIdentifierToUse]="smsPmtRcvdDetailed" Or [smsIdentifierToUse]="smsPmtRcvdPastDueDate" Or [smsIdentifierToUse]="smsPmtRcvdNotOnTrack" Or [smsIdentifierToUse]="smsPmtWrongBusNbr" Or [smsIdentifierToUse]="smsPmtWrongRef" Or [smsIdentifierToUse]="smsPmtWrongRegBusNbr","Please send all payments to MoneyMaker-KS (business # 778778) using M-PESA Malipo and reference # " & [nbrCustAcct] & ". ",Null) & IIf(IsNumeric([discountAvailable])," If you pay in full by " & [dateDiscEligibility] & " you will receive a Tsh " & [discountAvailable] & " discount.",Null) AS msgPart4, [/COLOR]
DLookUp("[dateDiscEligibility]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcctUsed] & "'") AS dateDiscEligibility, Format(DLookUp("[discountAvailable]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcctUsed] & "'"),"#,###") AS discountAvailable,
"Correct" AS Expr1,
Switch(IsNull([dateRegistration]),"Unknown account number",[statusRefund] Is Not Null,"Refund process",1*[PmtSumAfterPmt]>=1*[costTotalPreDiscount],"Payment complete",(([dateGracePeriodEnds] Is Not Null) And ([pmtDate]>[dateGracePeriodEnds])),"Past grace period",(([dateGracePeriodEnds] Is Not Null) And ([pmtDate]<=[dateGracePeriodEnds])),"In grace period",[pmtDate]>[dateFinalPmtDue_Revised],"Past due date",[PmtSumAfterPmt]<[depositMin],"Minimum not met",[balanceExpectedAfterPayment]>=[balanceDueAfterPmt],"On track",[balanceExpectedAfterPayment]<[balanceDueAfterPmt],"Not on track") AS statusCustomerAfterPmt,
IIf([dateGracePeriodEnds] Is Not Null,[dateGracePeriodEnds],[dateFinalPmtDue]) AS dateFinalPmtDue_Revised,
Format([costTotalPreDiscount]-[pmtSumAfterPmt],"#,##0;(#,##0);0") AS balanceDueAfterPmt,
IIf((IsNumeric([price]) And IsNumeric([fee])),([price]*1+[fee]*1)) AS costTotalPreDiscount,
Format(DSum("[pmtCompletedInOut]","tbl_payments","[pmtDate] <=" & Format([pmtDate],"\#yyyy-mm-dd hh:nn:ss\#") & "And [nbrCustAcctUsed]='" & [nbrCustAcctUsed] & "'"),"#,##0;(#,##0);0") AS pmtSumAfterPmt,
DLookUp("[statusRefund]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcctUsed] & "'") AS statusRefund,
IIf(IsNull(DLookUp("[ID]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcctUsed] & "'")),"Orphan payment",IIf(IsNull(DLookUp("[nbrCustAcctOverride]","tbl_payments","[nbrCustAcctUsed]= '" & [nbrCustAcctUsed] & "'")),"Valid account number used","Account number overriden")) AS nbrCustAcctValidity,
DLookUp("[dateGracePeriodEnds]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcct] & "'") AS dateGracePeriodEnds,
DLookUp("[custOverallStatusNow]","qry_customerStatus","[nbrCustAcct]= '" & [nbrCustAcct] & "'") AS custOverallStatusNow,
Format(DLookUp("[depositMin]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcct] & "'"),"#,##0;(#,##0);0") AS depositMin,
Format(DLookUp("[price]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcct] & "'"),"#,##0;(#,##0);0") AS price,
Format(DLookUp("[fee]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcct] & "'"),"#,##0;(#,##0);0") AS fee,
DLookUp("[productChosen]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcct] & "'") AS productChosen,
DLookUp("[dateFinalPmtDue]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcct] & "'") AS dateFinalPmtDue,
DLookUp("[dateRegistration]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcct] & "'") AS dateRegistration,
IIf(IsNumeric([monthsElapsedTilPmt]),Format(DLookUp(Switch([monthsElapsedTilPmt]>6,0,[monthsElapsedTilPmt]=6,"[balExpectedAfterMo6]",[monthsElapsedTilPmt]=5,"[balExpectedAfterMo5]",[monthsElapsedTilPmt]=4,"[balExpectedAfterMo4]",[monthsElapsedTilPmt]=3,"[balExpectedAfterMo3]",[monthsElapsedTilPmt]=2,"[balExpectedAfterMo2]",[monthsElapsedTilPmt]<2,"[balExpectedAfterMo2]"),"tbl_customers","[nbrCustAcct]= '" & [nbrCustAcct] & "'"),"#,##0;(#,##0)")) AS balanceExpectedAfterPayment,
IIf(IsNumeric([dateRegistration]),DateSerial(Year([dateRegistration]),Month([dateRegistration])+2,0)) AS tbd,
IsNull([monthsElapsedTilPmt]) AS dddd,
DateDiff("m",[dateRegistration],[pmtDate]) AS monthsElapsedTilPmt
FROM tbl_payments LEFT JOIN tbl_customers ON tbl_payments.nbrCustAcctUsed = tbl_customers.nbrCustAcct
WHERE (((tbl_payments.financialCategory)="PaymentOrRefund") AND ((tbl_payments.msgStatusPmt)<>"Sent" Or (tbl_payments.msgStatusPmt) Is Null))
GROUP BY tbl_customers.nbrCustAcct, tbl_payments.ID, tbl_payments.pmtCompletedInOut, tbl_payments.pmtDate, tbl_payments.nbrCustAcctUsed
HAVING (((tbl_payments.ID)>0))
ORDER BY tbl_customers.nbrCustAcct, tbl_payments.pmtDate, tbl_payments.ID;