Results 1 to 10 of 10
  1. #1
    tkbeard29 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    17

    Customer Service Survey db questions

    Hello All,



    I've been lurking around the last few days trying to get a couple questions answered and it seems like survey db's are the most difficult and challenging to get right. I believe I have a good layout and have been able to hammer down most of my db, but I'm missing the last couple pieces to finish this thing up and get it into production. A quick rundown:
    I have the following tables:
    tbl_Customer_Service_Survey
    tbl_Employees
    tbl_Managers
    tbl_Grade_Values
    tbl_Property_List
    tbl_Response_Values
    tbl_Survey_Questions_CSR
    tbl_Survey_Questions_Tech

    I've created two forms, one for CSR and one for Tech and have them in a tabbed form for the manager giving the out-bound survey calls to use. All the information collected from the survey form is placed into tbl_Customer_Service_Survey for both CSR and Tech surveys. My questions are these:

    What is the best way to have the Questions from the respective table be pre-populated into the form?

    I've had a hard time getting the final grade assigned to the survey score. tbl_Grade_Values has the following:
    Grade MaxValue MinValue
    F- 50 50
    F 49.99 42
    F+ 41.99 41
    D- 40.99 40
    D 39.99 34
    D+ 33.99 33
    C- 32.99 32
    C 31.99 27
    C+ 26.99 26
    B- 25.99 25
    B 24.99 19
    B+ 18.99 18
    A- 17.99 17
    A 16.99 11
    A+ 10.99 10
    I gave min and max range for averaging scores from the survey's per employee and regions.

    If any other information is needed I'll supply as quickly as possible. Thanks in advance for the assistance!

    TK

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,616
    What is the best way to have the Questions from the respective table be pre-populated into the form?

    Options:
    1. run an INSERT sql action to create records and open form to this set of blank records
    2. an unbound form with labels and textboxes as 'rows' of questions, code saves a record for each 'row', again an INSERT sql action

    As for the grading issue - you need a process that will return the letter equivalent of a calculated score depending on which range the score falls between? A Dlookup might accomplish that.
    DLookup("Grade", "tbl_Grade_Values", [Score] & " BETWEEN MaxValue AND MinValue")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    tkbeard29 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    17
    First off, thanks for the response and assistance.

    I added an unbound text box named "Grade" and wrote a Dlookup to update on click on the box after the Score for the survey is calculated (also unbound), and this is the error I get in return:

    Run-time error '3075':
    Syntax error (missing operator) in query expression 'BETWEEN MaxValue AND MinValue'.

    Here is my code:
    Private Sub Text62_Click()
    Grade = DLookup("Grade", "tbl_Grade_Values", [Score] & " BETWEEN MaxValue AND MinValue")
    End Sub

    Now the INSERT INTO statement.

    Getting Run-time error '3134':
    Syntax error in INSERT INTO statement.

    Here is my code:
    Private Sub Form_Current()
    DoCmd.RunSQL ("INSERT INTO [tbl_Customer_Service_Survey] ([Question1]) " & _
    "SELECT [tbl_Survey_Questions_Tech].[Question], " & _
    "FROM [tbl_Survey_Questions_Tech] " & _
    "WHERE ([tbl_Survey_Questions_Tech].[QuestionNumber])= " & (Question1) & "; ")
    End Sub


    Here is my tbl_Survey_Questions_Tech
    QuestionID QuestionNumber Question
    1 Q1 Question one
    2 Q2 Question two
    3 Q3 Question three
    4 Q4 Question four
    5 Q5 Question five
    6 Q6 Question six
    7 Q7 Question seven
    8 Q8 Question eight
    9 Q9 Question nine
    10 Q10 Question ten


    Not expecting a response tonight, and I'm heading out for the day soon. Again, your help is greatly appreciated to this slightly above novice Access/VBA/SQL guy.

    Attached is the db.
    Attached Files Attached Files
    Last edited by tkbeard29; 11-14-2012 at 05:04 PM. Reason: Attached db

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,616
    The DLookup works for me.

    Syntax for the SQL has unnecesary comma and don't enclose the statement in parens. To avoid warning messages or having to use SetWarnings code, instead of DoCmd.RunSQL, use: CurrentDb.Execute.

    What is structure of tbl_Customer_Service_Survey? Is each question in a column instead of individual records?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    tkbeard29 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    17
    Quote Originally Posted by June7 View Post
    The DLookup works for me.
    That's odd, I've tried it several times this morning and it won't work for me. The score calculation works, but the Dlookup to assign a letter grade doesn't. I have several other dlookups written so I understand the basics of dlookup, but this one is throwing me for a loop. Still getting "Syntax error (missing operator) in query expression 'BETWEEN Max AND Min.

    Grade = DLookup("Grade", "tbl_Grades", [Score] & "BETWEEN Max AND Min")

    I removed the word "values" from the table name and field names in case there was something with that.

    Quote Originally Posted by June7 View Post
    Syntax for the SQL has unnecesary comma and don't enclose the statement in parens. To avoid warning messages or having to use SetWarnings code, instead of DoCmd.RunSQL, use: CurrentDb.Execute.

    What is structure of tbl_Customer_Service_Survey? Is each question in a column instead of individual records?
    Changed this up to:

    Private Sub Form_Current()
    CurrentDb.Execute "INSERT INTO [tbl_Customer_Service_Survey] ([Question1]) " & _
    "SELECT [tbl_Survey_Questions_Tech].[Question] " & _
    "FROM [tbl_Survey_Questions_Tech] " & _
    "WHERE ([tbl_Survey_Questions_Tech].[QuestionNumber])= " & (Question1) & "; "
    End Sub


    Getting error code 3075, "Syntax error (missing operator) in query expression
    ([tbl_Survey_Questions_Tech].[QuestionNumber])='.

    tbl_Survey_Questions_Tech
    pkQuestionID (autonumber)
    fkQUestionNumber (text)
    fkQuestion (test) (this is where the actual question will be).

  6. #6
    tkbeard29 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    17
    I just noticed that my "QuestionNumber" column was not the same as the field in the form I was trying to have it relate to, changed the table to this:
    QuestionID QuestionNumber Question
    1 Question1 Question one
    2 Question2 Question two
    3 Question3 Question three
    4 Question4 Question four
    5 Question5 Question five
    6 Question6 Question six
    7 Question7 Question seven
    8 Question8 Question eight
    9 Question9 Question nine
    10 Question10 Question ten

    I've attached the db to show where I'm at right now with a couple changes today.

    I figure once I get this down for the form "Customer Service Survey - Tech" I can duplicate to the other form.
    Attached Files Attached Files

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,616
    Need a space before BETWEEN so the Score doesn't run into the word:
    Grade = DLookup("Grade", "tbl_Grades", [Score] & " BETWEEN Max AND Min")


    Multiple similar columns (Question1, Q1Score, Question2, Q2Score, etc) indicates data is not normalized. The means you are duplicating saving the question in two tables. Will every survey have the same 10 questions and always have 10 questions?

    Using form Current event to INSERT a record won't work. The form opens with DataEntry property set to Yes. This means the form opens only to NewRecord and the code in the Load event to move to new record is unnecessary. The record just created by sql INSERT will not show anyway. With this setup, need code to fill in the question textboxes on form, not create record in table. As soon as value entered in any field on form, a record is initiated. Record will be committed when form closes, move to another record, or run code.

    Private Sub Form_Current()
    For i = 1 To 10
    Me.Controls("Question" & i) = dlookup("Question", "tbl_Survey_Questions_Tech", "QuestionID=" & i)
    Next
    End Sub

    If you change the DataEntry property to No then the Load event code might work (or maybe should be in Open event) and modify the above code to something like:
    If IsNull(Me.Question1) Then
    For each...
    End If

    If you want to create record first then open the survey form to edit that record, that is very different code.


    When you want to refer to a field or control on form or report, best to qualify the reference with a prefix of the form/report name. Example:

    [Customer Service Survey - CSR]!ManagerName = dlookup("ManagerName", "tbl_Managers", "ManagerID=" & [Customer Service Survey - CSR].[ManagerID])

    If the code is behind the form referenced then can use the Me alias:

    Me.ManagerName = dlookup("ManagerName", "tbl_Managers", "ManagerID=" & Me.ManagerID)


    I find it odd that VBA is not correcting the capitalization of DLookup.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    tkbeard29 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    17
    Your code for the questions to populate in the form worked great! Thank you!!!

    I have tbl_Survey_Questions_Tech set up that way because I don't know how many or what the questions will be, is there a better way so the data is normalized? I don't foresee any future queries or use for that table beyond the current function of populating them into the form.

    I still can't get the grading code to work, I originally had that space in there... but in my attempts to try everything deleted it. I'm still getting the same error code.
    What is the best procedure to activity that code? I've tried by click (on the text box) and a couple other ways.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,616
    Score is a field of the form recordset but it is Null. Refer to the textbox that has the total score.

    Grade is a label, not a textbox. The textbox is named Text62, so:

    Me.Text62 = DLookup("Grade", "tbl_Grades", Me.Text60 & " BETWEEN [Min] AND [Max]")

    Max and Min are reserved words, really should use different names for fields.

    The total Score will not calculate until every question has a score and exit the last question score textbox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    tkbeard29 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    17
    Wow, that was a complete oversight on my part. Thank you so much for your help!!!! I truly appreciate it.
    Have a great day and a great Thanksgiving!!

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

Similar Threads

  1. A survey issue
    By JefMar in forum Reports
    Replies: 1
    Last Post: 07-11-2012, 11:22 AM
  2. Replies: 1
    Last Post: 12-24-2011, 08:48 AM
  3. Report from survey responses
    By Saramj in forum Reports
    Replies: 0
    Last Post: 02-20-2011, 04:59 PM
  4. Newbie needs help with survey design
    By Buakaw in forum Database Design
    Replies: 5
    Last Post: 01-20-2011, 10:20 PM
  5. Replies: 2
    Last Post: 12-02-2010, 02:35 AM

Tags for this Thread

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