Full Version: Better Than A Complex Switch Function
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
redphoneconsulting
Jan 27 2011, 12:37 PM
Howdy.

I'm trying to assign list records a segment ID that is based on some math. I'm getting an error that the switch function is too complex. Is there a better way to do this?

This should work in theory of receiving a mail code from the user, then attaching a segment field to each record based on their donation performance.

ie.

ContactKey Segment
23423 NGH1101A
32432 NGH1101E
etc... etc...



CODE
SELECT Donation_Extremes.ContactKey, [MailingCode] & Switch(

[HPC]>99 And DateDiff('m',[MRC],Date())<13 And [Contacts_OfflineOnlyDonors].[ContactKey]>0,"A",
[HPC]>99 And DateDiff('m',[MRC],Date())>12 and Contacts_OfflineOnlyDonors.ContactKey>0,"B",
DateDiff('m',[MRC],Date())<19 And [HPC]>49 And [HPC]<100 and Contacts_OfflineOnlyDonors.ContactKey>0,"C",
DateDiff('m',[MRC],Date())<13 And [HPC]>24 And [HPC]<50 and Contacts_OfflineOnlyDonors.ContactKey>0,"D",
DateDiff('m',[MRC],Date())<10 And [HPC]>9 And [HPC]<25 and Contacts_OfflineOnlyDonors.ContactKey>0,"E",

[HPC]>99 And DateDiff('m',[MRC],Date())<13 And [Contacts_MixedChannelDonors].[ContactKey]>0,"H",
[HPC]>99 And DateDiff('m',[MRC],Date())>12 and Contacts_MixedChannelDonors.ContactKey>0,"J",
DateDiff('m',[MRC],Date())<19 And [HPC]>49 And [HPC]<100 and Contacts_MixedChannelDonors.ContactKey>0,"K",
DateDiff('m',[MRC],Date())<13 And [HPC]>24 And [HPC]<50 and Contacts_MixedChannelDonors.ContactKey>0,"L",
DateDiff('m',[MRC],Date())<10 And [HPC]>9 And [HPC]<25 and Contacts_MixedChannelDonors.ContactKey>0,"M",

[HPC]>99 And DateDiff('m',[MRC],Date())<13 And [Contacts_OnlineOnlyDonors].[ContactKey]>0,"V",
[HPC]>99 And DateDiff('m',[MRC],Date())>12 and Contacts_OnlineOnlyDonors.ContactKey>0,"W",
DateDiff('m',[MRC],Date())<19 And [HPC]>49 And [HPC]<100 and Contacts_OnlineOnlyDonors.ContactKey>0,"X",
DateDiff('m',[MRC],Date())<13 And [HPC]>24 And [HPC]<50 and Contacts_OnlineOnlyDonors.ContactKey>0,"Y",
DateDiff('m',[MRC],Date())<10 And [HPC]>9 And [HPC]<25 and Contacts_OnlineOnlyDonors.ContactKey>0,"Z"

) AS Segment, Donation_Extremes.HPC, Donation_Extremes.MRC, Contacts_OnlineOnlyDonors.ContactKey, Contacts_MixedChannelDonors.ContactKey, Contacts_OfflineOnlyDonors.ContactKey
FROM (((Donation_Inception INNER JOIN Donation_Extremes ON Donation_Inception.ContactKey = Donation_Extremes.ContactKey) LEFT JOIN Contacts_OfflineOnlyDonors ON Donation_Extremes.ContactKey = Contacts_OfflineOnlyDonors.ContactKey) LEFT JOIN Contacts_MixedChannelDonors ON Donation_Extremes.ContactKey = Contacts_MixedChannelDonors.ContactKey) LEFT JOIN Contacts_OnlineOnlyDonors ON Donation_Extremes.ContactKey = Contacts_OnlineOnlyDonors.ContactKey;
BananaRepublic
Jan 27 2011, 12:45 PM
1) Sometimes it's the case that "Too Complex" error is actually a consequence of bad syntax - have you tried stripping away one term by one until it works and re-add it back?

2) I have to admit the criteria is not that entirely clear to me and I really think we need to see the source data (at least a bogus representation) and the intended output) - we may be able to suggest a SQL statement in place of Switch().
John Spencer
Jan 27 2011, 01:09 PM
I would use a public function to do something this complex.

CODE
Public Function fSegment(iHPC, dteMRC, iContactKey)
Dim iMonths as Long; iMonths = -99

   IF isDate(dteMRC) then
      iMonths = DateDiff("m",dteMRC,Date())
   End if

   IF iContactKey <= 0  or Imonths = -99 Then
      'Return null or empty string
      fSegment = Null
   else
      If iHPC > 99 and imonth < 13 Then
         fSegment = "A"
      ElseIf iHPC >99 and iMonth > 12
         fSegment = "B"
      ElseIf iHPC >49 and iHPC <100 and iMonth < 13
         fSegment = "C"
      ElseIf iHPC >24 and iHPC < 50 and iMonth > 12
         fSegment = "D"
      ElseIf iHPC >9 and iHPC < 25 and iMonth < 10
         fSegment = "E"
     ...

      End IF
   End IF

End function


redphoneconsulting
Jan 27 2011, 01:16 PM
The error is just coming from the length of the switch. If removing segment options, it works again. If I run each set of segments by themselves, it also works fine.

The source data is a queries that gives the donors donation behavior.

HPC = highest contribution
MRC = most recent contribution

and then it also queries their donation activity, such as whether or not they give only online, only through mail, or through both.

This query assigns them all codes based on their giving level and their giving activity, then the solicitation package is changed based on the different giving patterns.

The source data is in four tables:

DonationExtremes
ContactKey HPC MRC

OnlineOnlyDonors
ContactKey

OfflineOnlyDonors
ContactKey

MixedMethodDonors
ContactKey


The sample output is then:

ContactKey Segment
23423 NGH123A
34454 NGH123D
65464 NGH123Z


The segment is made up of the mail code supplied by the user ("NGH123" in our example) and the segment assigned by the query.


redphoneconsulting
Jan 27 2011, 01:21 PM
John,

Thanks for that. I think that is the right path to be on. My VBA usage is pretty weak. How do I call that function in my SQL query?

is it just something like


SELECT Donation_Extremes.ContactKey, [MailingCode] & fSegment(HPC, MRC, ContactKey) as MailingSegment

?



Warm Regards,
redphoneconsulting
Jan 27 2011, 02:43 PM
Hmm...

Well I've hit a snag, and I can't find the error message.

My query looks like this:

CODE
SELECT Donation_Extremes.ContactKey AS ContactKey, [MailingCode] &

fCustomSegment(
Donation_Extremes.[HPC],
Donation_Extremes.[MRC],
Donation_Extremes.[ContactKey],
Contacts_OnlineOnlyDonors.ContactKey,
Contacts_OfflineOnlyDonors.ContactKey,
Contacts_MixedChannelDonors.ContactKey
) AS Segment,

Donation_Extremes.HPC,
Donation_Extremes.MRC,
Contacts_OnlineOnlyDonors.ContactKey As OnlineKey,
Contacts_MixedChannelDonors.ContactKey As MixedKey,
Contacts_OfflineOnlyDonors.ContactKey AS OfflineKey

FROM (((Donation_Inception INNER JOIN Donation_Extremes ON Donation_Inception.ContactKey = Donation_Extremes.ContactKey) LEFT JOIN Contacts_OfflineOnlyDonors ON Donation_Extremes.ContactKey = Contacts_OfflineOnlyDonors.ContactKey) LEFT JOIN Contacts_MixedChannelDonors ON Donation_Extremes.ContactKey = Contacts_MixedChannelDonors.ContactKey) LEFT JOIN Contacts_OnlineOnlyDonors ON Donation_Extremes.ContactKey = Contacts_OnlineOnlyDonors.ContactKey;


And my function looks like this:

CODE
Public Function fCustomSegment(iHPC, dteMRC, iExtremeKey, iOnlineKey, iOfflineKey, iMixedKey)
Dim iMonths As Long
iMonths = -99

   If IsDate(dteMRC) Then
      iMonths = DateDiff("m", dteMRC, Date)
   End If

   If iExtremeKey <= 0 Or iMonths = -99 Then
      'Return null or empty string
      fSegment = "pre-else statement fail"
   Else
      If iHPC > 99 And iMonth < 13 And iOfflineKey > 0 Then
         fSegment = "A"
      ElseIf iHPC > 99 And iMonth > 12 And iOfflineKey > 0 Then
         fSegment = "B"
      ElseIf iHPC > 49 And iHPC < 100 And iMonth < 13 And iOfflineKey > 0 Then
         fSegment = "C"
      ElseIf iHPC > 24 And iHPC < 50 And iMonth > 12 And iOfflineKey > 0 Then
         fSegment = "D"
      ElseIf iHPC > 9 And iHPC < 25 And iMonth < 10 And iOfflineKey > 0 Then
         fSegment = "E"
      Else
         fSegment = "-NoSegment"
    
      End If
   End If

End Function


But my output isn't returning a segment value of any type. It just looks like this:

ContactKey Segment HPC MRC OnlineKey MixedKey OfflineKey
1 MailCode 35 12/4/2010 1
4 MailCode 75 11/8/2010 4
7 MailCode 25 12/1/2010 7


redphoneconsulting
Jan 27 2011, 03:54 PM
When I renamed the function, I forgot to rename the returned values. Dumb...

Thanks for the help, it's now working correctly.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.