Results 1 to 2 of 2
  1. #1
    tubefeed is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    1

    Red face 10 questions to get a Lead Score - how to calc the score and then populate a field

    I am using access for a Sales lead management database which I have created using a template.
    I have a Customer Table, employee table, and opportunity table.


    In the Opp table i have a field call Probability. This is a percentage field that is used to guess the likelihood of the job converting.
    At the moment this field is simply a data entry field where you enter the probability.
    I want to put some logic behind this. To do this I have come up with 10 Yes/No questions.
    If the answer is yes, the score for that question is 10, if No, then zero. (I also have a couple of questions with 3 values, but can modify to simply yes no to get this working).
    The total of the 10 questions then gives the probability e.g. six yes answers, and 4 no's means the probability is 60%
    Soooo....I know what I want to do...But I have no idea how to set this up in Access. That is calculate the score using the questions, and have the probability populated into the Opp table.
    I have a programming background, but not in access.
    Any tips would be gratefully accepted...

  2. #2
    euanl is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jun 2013
    Posts
    25
    There are probably other ways to do this and this way may be quite convoluted (but it works). Assuming you are using an input form (do use an input form). Put this wee bit of VBA into the after update function of one of the other fields (most logical one for you, and make sure it is a field that will always be populated):

    ‘declare variables
    Dim q1 as string, q2 as string, q3 as string, q4 as string, q5 as string, q6 as string, q7 as string, q8 as string, q9 as string, q10 as string
    Dim answer as string
    Dim val1 as integer, val2 as integer, val3 as integer, val4 as integer, val5 as integer, val6 as integer, val7 as integer, val8 as integer, val9 as integer, val10 as integer, tot as integer


    q1 = "Type your question in here"
    answer = MsgBox(myquest, vbQuestion + vbYesNo)
    If answer = vbNo Then
    'enter your criteria for clicking no
    Val1 = 0
    Elseif answer = vbYes then
    'enter your criteria for clicking yes
    Val1 = 10
    End if

    q2 = "Type your question in here"
    answer = MsgBox(myquest, vbQuestion + vbYesNo)
    If answer = vbNo Then
    'enter your criteria for clicking no
    Val2 = 0
    Elseif answer = vbYes then
    'enter your criteria for clicking yes
    Val2 = 10
    End if
    ‘You get the gist, repeat this for each question then:

    ‘Add all the values up
    Tot = val1 + val2 + val3….etc

    ‘Take that value and place into the percentage field
    Me.FieldWhatever .value = tot

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-22-2013, 01:56 PM
  2. Replies: 4
    Last Post: 08-26-2012, 10:51 PM
  3. Test Score Results
    By bob.bud in forum Queries
    Replies: 5
    Last Post: 01-18-2012, 04:59 PM
  4. how to get the average of score?
    By beauty in forum Access
    Replies: 1
    Last Post: 10-08-2011, 06:40 AM
  5. score and place query
    By ymds in forum Queries
    Replies: 1
    Last Post: 06-10-2010, 06:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums