April 8, 2004 at 8:59 am
Hello,
I have a table that is structured (not including nonclustered indexes) as follows
tblADAnswerData
RecID int IDENTITY (1,1) Not Null Primary Key,
TestGrade SmallInt Not Null,
TestShortName Nvarchar (8) Not Null
Permnum Nvarchar (12) Not Null
Q1 Nvarchar (3) Null,
…
Q75 Nvarchar(3) Null,
Processed Bit Not Null Default 0,
Completed Bit Not Null Default 0,
InsertDate SmallDateTime Not Null Default (GetDate())
The table is designed to hold answers to test questions, where the tests have 75 questions (Q1..Q75). ‘Permnum’is basically the StudentID.
Data winds up in this table via an input form where the student indicates a question answer by making a selection from a dropdown list box on each question. Each combo box provides an A-F answer.
As part of a processing step, I need to convert the alpha data in tblADAnswerData for the Q(x) fields to numeric data, and then insert the numeric data into a table called tblMMStanTestScores. The alpha data needs to remain unchanged tblADAnswerData.
The alpha data will be represented numerically as follows:
A = 0, B = 1, C = 2, D = 3, E = 4, F = 5
In each CASE statement, the value of a field called ‘Qnumber’ from a table called ‘tblAnswerKey’
During the processing, each Q(x) field’s data will become stored in a single aliased field called ‘SA’ (Student Answer) through the use of CASE statements. This is accomplished by using a Cross Join between tblADAnswerData and another table called tblADAnswerKey. The structure of the CASE statements basically looks like this:
CASE
When KY.QNumber = 1 Then AD.Q1
When KY.QNumber = 2 Then AD.Q2
…
When KY.QNumber = 75 Then AD.Q75
END as SA,
'Q'+ str(KY.QNumber,2) as QNum
FROM tblADAnswerData AD CROSS JOIN tblADAnswerKey KY
The question on my mind is this: How do I convert the alpha data to numeric data, and can I do that within this CASE statement?
One idea I had was to set up a conversion table sort of like this:
NumValue AlphaValue
0 A
1 B
2 C
3 D
4 E
5 F
Then join each Q(x) field to the ‘AlphaValue’ field in a view and set up the CASE statement sort of like this:
When KY.Qnumber = 1 Then View.NumValue1
I also thought that maybe I could use a function that might work kind of like this:
Function ConvertAnswer(QRaw as Nvarchar(3))
Dim QValue as Nvarchar(3)
Set QValue = QRaw
Case "QData"
When QValue = 'A' Then 0
When QValue = 'B' Then 1
When QValue = 'C' Then 2
When QValue = 'D' Then 3
When QValue = 'E' Then 4
When QValue = 'F' Then 5
End
End Function
Then use the Function in the CASE statement like this:
When KY.QNumber = 2 Then ConvertAnswer(AD.Q2)
Or maybe I could have a small table with Alpha and equivalent Numeric data, and have the drop down lists bind to the numeric data, but show the alpha data. The just insert the numeric data into the table, while the user will always see the Alpha representation (and this idea has just occured to me).
So, maybe this message was an exercise in futility, but I haven't thought out every requirement for this application.
Could you please assist me in coming up with a solution for this?
Thank you for your help!
CSDunn
April 8, 2004 at 9:04 am
select ascii(alpha) - 65
or
select ascii(upper(alpha)) - 65
April 8, 2004 at 10:12 am
Wow! Thanks!
CSDunn
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply