Hi all, I've searched the forums for surveys and questionnaires, picked up a few pointers and watched a online tutorial here but found it was based on a non normalised table.
I've created the survey as a non normalised design, works fine, just getting reporting hicups with null fields etc, so want to normalise it.
The survey is basic, 25 questions with just a 1 to 10 score.
I have created three tables with the following fields
The question...The Form input
In the non normilised table its pretty easy to create a form based on a non normalised table with 25+ table fields with each table field relating to a field on form and a combo box with the 1 to 10 Result list.
What I do not understand using the Normalised structure is how to show all 25 questions on one form and then add each question result to the Results table with the questionId and Result on the appropriate row?
If I've missed the answer searching the forum, apologies!
Thanks for the reply However thats a little above my current ability, so a little help would be appreciated.
Using your suggestion, I thought I start small with just getting two fields to append to a table. I'm stuck allready. I've created a query to see the SQL code which I do understand, this has given me this, I've added the do.cmd.RunSql from another web page.
Code:
DoCmd.RunSQL = "INSERT INTO tbl_Results ( QuestionID, Response ) SELECT tbl_Results.QuestionID, tbl_Results.Response"
FROM tbl_Results
WHERE "tbl_Results.QuestionID = me.txtQ1 AND tbl_Results.Response = me.txtR1;"
Now I've added this to an on click event on the form, but I'm now stuck understanding what comes before and after it. I've looked around and can see that there are examples, but I don't really understand what the code is doing.
Private Sub CmdLawyersLetter_Click() 'command button name is CmdLawyersLetter
On Error GoTo Err_CmdLawyersLetter_Click
Dim sqlString As String
Dim ADPK As Integer 'CustomerID
Dim LetterID As Integer 'LetterID
Dim strUserID As String 'Database UserID
ADPK = Me.ADPK 'Sets ADPK to = Form Control Name ADPK
LetterID = 10 'Set LetterID to - 10 - relates to an ID number in letter options table
'Get current operator
strUserID = Me.strUserID ' also gets the userID from a form control
DoCmd.SetWarnings False ' stops any warnings
'Collect Data and append record to TblLetterSent
sqlString = "INSERT INTO TblLetterSent ( LID, MID, FullName, Fax, LIDOverdue, LetterType, LetterSubject ) " & _
"SELECT TBLLOAN.LDPK, TBLACCDET.ADPK, [ADFirstName] & "" "" & [ADSurname] AS FullName, TBLACCDET.ADFax, QryLoanCurrentBalanceResult.LoanCurrentBalance, TblLetterOptions.LetterID, TblLetterOptions.LetterType " & _
"FROM TblLetterOptions, QryLoanCurrentBalanceResult INNER JOIN (TBLACCDET INNER JOIN TBLLOAN ON TBLACCDET.ADPK = TBLLOAN.ADPK) ON QryLoanCurrentBalanceResult.LoanID = TBLLOAN.LDPK " & _
"WHERE (((TBLLOAN.LDPK)=" & LDPK & ") AND ((TblLetterOptions.LetterID)=" & LetterID & "));"
'Your sql should be shorter because you are just appending data from the form whereas in this example some data is found in related tables
DoCmd.RunSQL sqlString 'Append Letter Details to TblLetterSent
'Collect Data and append record to tblLoanComm
sqlString = "INSERT INTO tblLoanComm ( LoanID, CommNotes, Operator, CommDate, CommTime ) " & _
"SELECT TBLLOAN.LDPK, ""Sent "" & [TblLetterOptions].[LetterType] AS CommNotes, strUserID, Date() AS CommDate, Time() AS CommTime " & _
"FROM TBLLOAN, TblLetterOptions " & _
"WHERE (((TBLLOAN.LDPK)=" & LDPK & ") AND ((TblLetterOptions.LetterID)=" & LetterID & "));"
DoCmd.RunSQL sqlString 'Append Loan Communication to tblLoanComm
'Collect Data on Letter just printed and update the Letter Printed Flag
sqlString = "UPDATE TblLetterSent SET TblLetterSent.LetterPrinted = 2 " & _
"WHERE (((TblLetterSent.LID)=" & LDPK & ") AND ((TblLetterSent.LetterDate)=Date()) AND ((TblLetterSent.LetterPrinted)=1));"
DoCmd.RunSQL sqlString 'Update TblLetterSent Letter Printed Flag
DoCmd.SetWarnings True
Exit_CmdLawyersLetter_Click:
Exit Sub
Err_CmdLawyersLetter_Click:
MsgBox Err.Description
Resume Exit_CmdLawyersLetter_Click
End Sub
My example performs more then one task so you just ignore where you see can see the second or 3rd activity
PNGBill Thanks very much I have got it appending the 1st field and understand in a limited way what it's doing, also found out what a _ does at the end on the line from this post.
Below is the code I have in place that works, may not be perfect, but does the job. I've used your code and updated with my sql, now seeing what was wrong with my first attempt. For some reason I had the table I was trying to append to as the source and criterea as the form fields. I have a few questions if you have time to answer them.
Code:
Private Sub btnAppendResult_Click()
On Error GoTo Err_btnAppendResult_Click
Dim sqlString As String
' DoCmd.SetWarnings False ' stops any warnings
'Your sql should be shorter because you are just appending data from the form whereas in this example some data is found in related tables
'Collect Data and append record to tbl_Reponses
sqlString = "INSERT INTO tbl_Responses ( ResponseDate, GSFS, QuestionID, Score ) " & _
"SELECT Date(), [txtGSFS] AS GSFS, [txtQ1] AS QUESTIONID, [txtS1] AS SCORE; "
DoCmd.RunSQL sqlString 'Append Responses to tbl_Reponses
DoCmd.SetWarnings True
Exit_btnAppendResult_Click:
Exit Sub
Err_btnAppendResult_Click:
MsgBox Err.Description
Resume Exit_btnAppendResult_Click
End Sub
In the sql code, it appears that its just inserting the fields in order from left to right as they appear in the code. Just for knowledge how would you specify which field you wanted if they weren't in order?
Also where I've put '[txtGSFS] as GSFS' it seems to work fine without the AS GSFS part?
I see I could just add the SQL statement multiple times and just change the field names to update, but your Loop function sounds more efficient if adding further questions.
From your first reply, you recommend a loop and variables to change the field name. I've looked this up and after trying a few variations of the examples, I can't get it to work and I don't really understand. I've tried going through some posts here and an example here but I seem to just get errors!
Congratulations! you have done well to sort through this issue and your code looks clean and simple.
In the sql code, it appears that its just inserting the fields in order from left to right as they appear in the code. Just for knowledge how would you specify which field you wanted if they weren't in order?
I'm guessing here but this is an issue related to your table eg, to miss an important field may cause an error. try changing the order of your Insert sql.
The sql 1st part and 2nd part must be in the same order.
Also where I've put '[txtGSFS] as GSFS' it seems to work fine without the AS GSFS part?
the "As" can be dropped. I guess I used it to get the names the same but not needed. The Order and Format are the important issues eg, don't insert text into a date field.
I see I could just add the SQL statement multiple times and just change the field names to update, but your Loop function sounds more efficient if adding further questions.
For one, two or three questions you could repeat the code but after that it will get messy and what if you are told there will now be 100 questions?
From your first reply, you recommend a loop and variables to change the field name. I've looked this up and after trying a few variations of the examples, I can't get it to work and I don't really understand. I've tried going through some posts here and an example here but I seem to just get errors!
I should have enough info from your code to offer an example loop but how about posting your best try as well.
I need an hour or so to get back to you. 05:00hrs here.
You have to do a loop - should be examples available but you also have to change where the variable gets it's value from each time around.
What is GSFS ?
Remember, your controls on the form must all be named similar eg txtQ1, txtQ2
The Code will then use txtQ as the name and append 1, 2, 3, or 4 etc to the name as it loops around.
It will also help if you have a control on the form with the number of questions. This can be part of your Form eg here are your 25 questions where the 25 is a separate control or you can have the control Invisible so it isn't seen, except by the vba code, we hope.
This will let the code know how many times to loop.
Here is a Function that performs a Loop - after sorting out the data and when finished appends a record to a table - using rst.
Code:
Public Function LatefeesToBeCharged(LoanID As String) As Currency 'Calculate Late Fees for selected LoanID
'Thanks To Allen Browne's Web Site for the start of this Code
Dim rs As DAO.Recordset
Dim sqlString As String
Dim RepayFreq As String ' Repayment Fortnightly, Monthly or Weekly
Dim CurrentBalance As Currency ' Balance As At each 14 days from the Start Date of the Loan plus 14 days
Dim LateFeeRate As Currency ' Kina Amount of Late Fee to be charged each fortnight
Dim RepayTotal As Currency ' Kina amount of accumulated Repayments up to Given Date
Dim RepayPrev As Currency ' Kina Amount Repaid Up To Last Fortnight
Dim RepayAmount As Currency ' Kina Amount Due to be Repaid Each Fortnight
Dim CommenceDate As Date ' Loan Commencement Date - Start Date plus 14 days
Dim RepayCount As Integer ' Count of Fortnights as Function Loops - Increases by 1 each loop
Dim RepayNum As Integer ' Number of Repayments agreed to for Loan
Dim LateFeeDue As Currency ' Accummulated Late Fee now being calculated
Dim LateFeeNow As Currency ' Kina Charge of Late Fee to be charged as at this date
Dim LateFeesCharged As Currency ' Kina Amount of Late fees charged as at given date
Dim LoanTotalOwing As Currency ' Kina Amount of Loan to Pay
RepayCount = 1 'Set Variable to start at 1
LateFeeDue = 0 'Set variable to Zero
LateFeeNow = 0 'Set Variable to Zero
sqlString = "SELECT TBLLOAN.LDPK, TBLLOAN.LDSt AS StartDate " & _
"FROM TBLLOAN " & _
"WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
Set rs = DBEngine(0)(0).OpenRecordset(sqlString)
CommenceDate = rs!StartDate + 14
sqlString = "SELECT TBLLOAN.LDPK, TBLLOAN.LDPayK AS LDRepayK " & _
"FROM TBLLOAN " & _
"WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
Set rs = DBEngine(0)(0).OpenRecordset(sqlString)
RepayAmount = rs!LDRepayK
sqlString = "SELECT TBLLOAN.LDPK, TBLLOAN.LDPayNo AS RepayNo " & _
"FROM TBLLOAN " & _
"WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
Set rs = DBEngine(0)(0).OpenRecordset(sqlString)
RepayNum = rs!RepayNo
sqlString = "SELECT TBLLOAN.LDPK, TBLLOAN.LDPayFre AS LDRepayFreq " & _
"FROM TBLLOAN " & _
"WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
Set rs = DBEngine(0)(0).OpenRecordset(sqlString)
RepayFreq = rs!LDRepayFreq
sqlString = "SELECT TBLLOAN.LDPK, TBLLOAN.LoanLateFee AS LateFeeKina " & _
"FROM TBLLOAN " & _
"WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
Set rs = DBEngine(0)(0).OpenRecordset(sqlString)
LateFeeRate = rs!LateFeeKina
If RepayFreq = "Fortnightly" Then
FuncDeleteRecord "TblLateFeeCalculated", "LDPK", LoanID
Do Until Date <= CommenceDate
CurrentBalance = LoanBalanceToDate(LoanID, CommenceDate) 'Function to Get Loan Balance as at given date
LateFeesCharged = LateFeesToDate(LoanID, CommenceDate) 'Function to Get Late Fees Charged as at given date
RepayTotal = LoanRepaymentToDate(LoanID, CommenceDate) 'Function to Get Repayments To Date as at given date
LoanTotalOwing = LoanTotalToPay(LoanID) 'Function to Get Total Amount Yet to Pay on Loan
If RepayCount = 1 Then 'If First Repayment Due
If (CurrentBalance - LateFeesCharged) > (RepayAmount / 2) Then 'If Repament Made for less then Half of Repayment Amount
LateFeeNow = (LateFeeRate * 2) 'Charge double Late Fees
Else
LateFeeNow = 0 'No Late Fees to Charge
End If
RepayPrev = RepayTotal 'Set Repay Value For up to First Fortnight
ElseIf RepayCount > 1 Then 'If not First Repayment
If (CurrentBalance - LateFeesCharged) > 5 Then 'Check If Overpaid to date or paid in full
If RepayCount <= RepayNum Then 'And is Last Repay Date or earlier
If (RepayTotal - RepayPrev) < (RepayAmount / 2) Then 'Compare Any Recent Repayment to at least half the Agreed Repayment
LateFeeNow = LateFeeRate 'Charge Late Fee
Else
LateFeeNow = 0 'No Late fees Due
End If
RepayPrev = RepayTotal 'Reset RepayPrev to New Balance
Else
LateFeeNow = LateFeeRate 'Charge Late Fee
End If
Else
If (LoanTotalOwing - LateFeesCharged) < 6 Then 'Check If Original Loan fully repaid
CommenceDate = Date 'Loan Completed - set Commence Date to today's date to stop loop
LateFeeNow = 0 'No Late Fees Due
End If
LateFeeNow = 0 'No Late fees Due
End If
End If
If LateFeeNow > 0 Then
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("TblLateFeeCalculated", dbOpenDynaset)
rst.AddNew
rst!LDPK = LoanID
rst!LateFeeAmount = LateFeeNow
rst!LateFeeDate = CommenceDate
rst.Update
rst.Close
Set rst = Nothing
End If
CommenceDate = CommenceDate + 14 'Add 14 Days to get Next loop
RepayCount = RepayCount + 1 'Add 1 to RepayCount variable for next loop
LateFeeDue = LateFeeDue + LateFeeNow 'Add this fortnights Late Fee Charge to Accummulated Late Fees Variable
Loop
LateFeesCharged = LateFeesToDate(LoanID, CommenceDate) 'Refresh Late Fees Charged as at Today's date
LateFeeDue = LateFeeDue - LateFeesCharged 'Deduct any Late Fees already Charged
LatefeesToBeCharged = LateFeeDue 'Return Result to Function
Else
MsgBox "Loan Repayments Not Fortnightly - Calculate Late Fees Some other way" 'Calculator won't work for Weekly or Monthly repayments
End If
rs.Close
Set rs = Nothing
End Function
Actually, my first attempt (at a serious loop) so we are both learning a bit here.
The problem I have is I'm trying to reference you code, not knowing really where to start!
You mentioned a control on the form to specify how many questions, is there something similar on the code you posted? Also which part of your code takes the txtQ1 part and adds 1 as part of the loop?
GSFS is a text reference we have to fill in. No idea what it actually stands for, it just comes on a sheet of paper that we are currently adding on an excel sheet.
The problem I have is I'm trying to reference you code, not knowing really where to start!
You mentioned a control on the form to specify how many questions, is there something similar on the code you posted? Also which part of your code takes the txtQ1 part and adds 1 as part of the loop?
GSFS is a text reference we have to fill in. No idea what it actually stands for, it just comes on a sheet of paper that we are currently adding on an excel sheet.
Hi there. I yes the fields are text boxes and the gsfs doesn't change.
Posting from my mobile as my net connection has just died. I did find another example of the loop but can't see it on this phone. I've bookmarked it so i'll take a good look when I can get back on my PC.
This symbol ' means a remark only - not part of the code
You will need to add the control to your form for the number of times to loop.
Code:
Private Sub btnAppendResult_Click()
On Error GoTo Err_btnAppendResult_Click
Dim sqlString As String ' This is the sql Satatement to be run each time
Dim NumRecords As Integer ' This is the number of records to be appended
Dim Count As Integer ' this is the count of each loop action
Dim QStart As String ' this is the Start of the Question Control on the Form the query will look for
Dim SStart As String ' this is the Start of the Score Control on the form the query will look for
Dim txtQuestion As String ' this is the sql variable Question txt box control
Dim txtScore As String ' this is the sql variable Score txt box control
QStart = "txtQ" ' Set the Fixed part of the form Question Control Name
SStart = "txtS" ' Set the Fixed part of the form Score Control Name
Count = 1 ' Set Count to 1 to Start
NumRecords = Me.QuestionNumber ' Set NumRecords to be the value in the Control QuestionNumber on your form
Do Until Count = NumRecords ' Stop Loop when Count reaches number of records on form
txtQuestion = QStart & Count ' Set Control Name to be the full name for this Loop
txtScore = SStart & Count ' Set Control Nam eto be the Full Name for this Loop
DoCmd.SetWarnings False ' stops any warnings
'Collect Data and append record to tbl_Reponses
sqlString = "INSERT INTO tbl_Responses ( ResponseDate, GSFS, QuestionID, Score ) " & _
"SELECT Date(), [txtGSFS], " & txtQuestion & ", " & txtScore & "; "
DoCmd.RunSQL sqlString 'Append Responses to tbl_Reponses
DoCmd.SetWarnings True ' Start warnings
Count = Count + 1 ' Add 1 to Count
Loop
Exit_btnAppendResult_Click:
Exit Sub
Err_btnAppendResult_Click:
MsgBox Err.Description
Resume Exit_btnAppendResult_Click
End Sub
Now I can see what its up to knowing which bits are relating to the fields in my database it makes it a lot easier to understand. Oddly It also helped when I thought about it as like how I configure our telephone switch with Direct Dial Number to Extension translations. Well its similar as the switch replaces the first part of the DDI string with an extension which steps up. I know what I mean!
I was going to see if I could use your example it to set all the Combo's to Null values, but then I remembered I'd used a similar method with Control Types.
I have another project coming up next week which will need a loop, dont be surprised if I pop back!
However thats a little above my current ability, so a little help would be appreciated.
