Customer Service Survey db questions

[tkbeard29]

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

[June7]

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")

[tkbeard29]

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.

[June7]

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?

[tkbeard29]

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.

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).

[tkbeard29]

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.

[June7]

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.

[tkbeard29]

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.

[June7]

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.

[tkbeard29]

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!!