Denormalizing for form only

[Amy Blankenship]

The table structure is wrong, therefore the form structure cannot be
right.

ScoreSet
===========
ScoreSetId-Autonumber, PK
SubjectName-reading, writing, math
ScoreSetDesc-will allow users to select this set later and associate
it with an exam

ScoreSetItems
============
ScoreID-Autonumber PK
ScoreSetID-FK to scoreset
WritingScore-score to index this on (will be 0 for subjects that
don't apply, 0-6 for writing)

NewTable
============
NewTableID-Autonumber PK
ScoreID-FK to scoresetItems
RawScore-the actual score on the multiple choice questions


The following should be a calculated value, not a table field.
SATScore-the scaled SAT score in the given subject

There's no advantage to this structure (what's the point of providing a fk
that just goes to a number?), and the scaled SAT cannot be calculated, and
so must be ENTERED BY HAND.

Since you haven't grasped that concept, I'm guessing you're not going to be
able to provide a form that will allow that to happen efficiently. I
appreciate the time you've taken, though.

 

[Bob Quintal]

There's no advantage to this structure (what's the point of
providing a fk that just goes to a number?), and the scaled SAT
cannot be calculated, and so must be ENTERED BY HAND.

Since you haven't grasped that concept, I'm guessing you're not
going to be able to provide a form that will allow that to happen
efficiently. I appreciate the time you've taken, though.

When a distinguished but elderly scientist states that something is
possible, he is almost certainly right. When he states that
something is impossible, he is very probably wrong.
---Arthur C. Clarke

I say it can be calculated.

As to the advantage, it allows normalization.

 

[David Benyo via AccessMonster.com]

Amy,

I understand your frustration. I've designed a couple report card
applications for various schools and each was pretty tough to normalize and
yet make data entry easy.

Could you post a sample dataset of how it should look to the user? I feel
like there's something that I'm not understanding and maybe looking at the
final product would help.

I've always found solving the mazes on the back of a cereal box easier to
start from finish than start.

 

[Amy Blankenship]

Amy,

I understand your frustration. I've designed a couple report card
applications for various schools and each was pretty tough to normalize
and
yet make data entry easy.

Could you post a sample dataset of how it should look to the user? I feel
like there's something that I'm not understanding and maybe looking at the
final product would help.

I assume you mean to the data entry person rather than the actual end user,
who will only see the end result of the calculation. I ultimately decided
to go with an excel import.

Here are a few sample rows from excel (note that the first row is the
writing score, and the first column is the raw score). I ran it through
notepad to remove the excel formatting:

Raw Score 0 1 2 3 4 5 6
49 650 670 690 710 750 780 800
48 630 640 660 690 720 760 780
47 600 620 640 660 690 720 760
46 580 600 620 650 680 710 740
45 570 580 600 630 670 700 730
44 560 570 590 620 660 690 720

Here is what this looks like after import:

ScoreID ScoreSetID WritingScore RawScore SATScore
1 2 0 49 650
2 2 1 49 670
3 2 2 49 690
4 2 3 49 710
5 2 4 49 750
6 2 5 49 780
7 2 6 49 800
8 2 0 48 630
9 2 1 48 640
10 2 2 48 660
11 2 3 48 690
12 2 4 48 720
13 2 5 48 760
14 2 6 48 780
15 2 0 47 600
16 2 1 47 620
17 2 2 47 640
18 2 3 47 660
19 2 4 47 690
20 2 5 47 720
21 2 6 47 760
22 2 0 46 580
23 2 1 46 600
24 2 2 46 620
25 2 3 46 650
26 2 4 46 680
27 2 5 46 710
28 2 6 46 740
29 2 0 45 570
30 2 1 45 580
31 2 2 45 600
32 2 3 45 630
33 2 4 45 670
34 2 5 45 700
35 2 6 45 730
36 2 0 44 560
37 2 1 44 570
38 2 2 44 590
39 2 3 44 620
40 2 4 44 660
41 2 5 44 690
42 2 6 44 720

This is the import routine I use, in case it helps anyone:

Sub importScoreConversion(catID, SetDesc)
'with thanks to Danny Lesandrini
'http://www.databasejournal.com/features/msaccess/article.php/3557541

'database vars
Dim db As DAO.Database, rst As DAO.Recordset, strSQL As String
'vars that have temp uses
Dim s As String, dlg As Variant, i As Integer, j As Integer, k As
Integer, strDoWhat As String
' Excel object variables
Dim appExcel As Excel.Application, wbk As Excel.Workbook, wks As
Excel.Worksheet
'vars for dealing with the worksheet once it's open
Dim endRow As Integer, endCol As Integer, sheets As Integer, startRow As
Integer
'variables that contain actual values to append
Dim sheetName As String, rawScore As Integer, writingScore As Integer
Dim scaledScore As Integer, setID As Integer

On Error GoTo Cleanup

' let user select excel file
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
strDoWhat = "finding file"
With dlg
'note this line doesn't actually work as expected, but it gets close
enough
.InitialFileName = Left(CodeProject.Path, InStrRev(CodeProject.Path,
"\\"))
If .Show = -1 Then s = .SelectedItems(1)
End With
strDoWhat = "Opening workbook"
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(s)
sheets = wbk.Worksheets.Count
startRow = 2
Set db = CurrentDb()

For i = 1 To sheets
Set wks = Nothing
Set rst = Nothing
strDoWhat = "parsing sheet " & i
Set wks = wbk.Worksheets(i)
s = wks.UsedRange.Address
endRow = CInt(Mid(s, InStrRev(s, "$")))
s = Mid(s, InStr(s, ":$") + 2, (Len(s) - InStrRev(s, "$")) - 1)
endCol = Asc(s) - 64 ' only works up to Z, but expect max of H
sheetName = wks.Name

'insert set record
strDoWhat = "inserting new set"
strSQL = "INSERT INTO ScoreSets (KCategorySetID, SetDesc) " & _
"SELECT KCatSetID, '" & SetDesc & " " & sheetName & _
"' FROM KCategorySet WHERE KCatSetDesc = '" & sheetName &
"'"

'execute query
db.Execute strSQL
'retrieve new ID
strDoWhat = "retrieving new score set ID"
strSQL = "SELECT Max(ScoreSetID) FROM ScoreSets"
Set rst = db.OpenRecordset(strSQL)
If Not rst.EOF Then
setID = CInt(rst(0))
Else
Err.Raise 999, , "Score set could not be created or retrieved"
End If
'associate this set with the catID
strDoWhat = "attaching score set to category"
strSQL = "INSERT INTO CategoryScoreSet (CategoryID, ScoreSetID) " &
_
"VALUES (" & catID & ", " & setID & ")"
db.Execute strSQL
For j = startRow To endRow
rawScore = CInt(wks.Cells(j, 1))
For k = 2 To endCol
writingScore = k - 2
scaledScore = wks.Cells(j, k)
'Debug.Print "rawscore " & rawScore & " writingScore " &
writingScore & " scaledScore " & scaledScore
strDoWhat = "inserting " & sheetName & " raw " & rawScore &
" writingscore " & writingScore
strSQL = "INSERT Into ScoreSetItem (ScoreSetID,
WritingScore, RawScore, SATScore) " & _
"VALUES (" & setID & ", " & writingScore & ", " &
rawScore & ", " & scaledScore & ")"
db.Execute strSQL
Next
Next
Next

Cleanup:
If Err.Number Then
Debug.Print "An error occurred in " & strDoWhat & " (" & Err.Number
& "): " & Err.Description
End If
On Error Resume Next
wbk.Close
Set wbk = Nothing
Set appExcel = Nothing
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

 

[Amy Blankenship]

When a distinguished but elderly scientist states that something is
possible, he is almost certainly right. When he states that
something is impossible, he is very probably wrong.
---Arthur C. Clarke

I say it can be calculated.

As to the advantage, it allows normalization.

OK, get me the formula the college board uses, and I'll calculate it .
If I _could_ calculate it, then all the data entry wouldn't be necessary, so
I wouldn't need a form. However, the table structure _is_ normalized. The
problem is that Access doesn't make it easy to build forms that work with a
properly normalized structure in many cases.

I was hoping when I asked this question that there was something I'd been
missing all along, but it looks like if there is, I'm in really good
company, since everyone else missed it too!

 

[rquintal]

OK, get me the formula the college board uses, and I'll calculate it .
If I _could_ calculate it, then all the data entry wouldn't be necessary, so
I wouldn't need a form.  However, the table structure _is_ normalized.  The
problem is that Access doesn't make it easy to build forms that work with a
properly normalized structure in many cases.

I was hoping when I asked this question that there was something I'd been
missing all along, but it looks like if there is, I'm in really good
company, since everyone else missed it too!- Hide quoted text -

- Show quoted text -

google is your friend.

These sites explain pretty well how to generate the scaling algorithm
http://en.wikipedia.org/wiki/SAT#Raw_scores.2C_scaled_scores.2C_and_percentiles
http://www.colinfahey.com/oldpages/2003apr5_sat/original_2003apr5_sat.htm
http://professionals.collegeboard.com/data-reports-research

 

[Amy Blankenship]

OK, get me the formula the college board uses, and I'll calculate it .
If I _could_ calculate it, then all the data entry wouldn't be necessary,
so
I wouldn't need a form. However, the table structure _is_ normalized. The
problem is that Access doesn't make it easy to build forms that work with
a
properly normalized structure in many cases.

I was hoping when I asked this question that there was something I'd been
missing all along, but it looks like if there is, I'm in really good
company, since everyone else missed it too!- Hide quoted text -

- Show quoted text -

google is your friend.

These sites explain pretty well how to generate the scaling algorithm
http://en.wikipedia.org/wiki/SAT#Raw_scores.2C_scaled_scores.2C_and_percentiles
http://www.colinfahey.com/oldpages/2003apr5_sat/original_2003apr5_sat.htm
http://professionals.collegeboard.com/data-reports-research

--------------------------------------------

No they don't. If you knew the percentile for a given test (which we
don't), you could use link 1. Link 2 shows an example curve for _one_ test,
but any idiot can see it's not actually a smooth curve so there wouldn't be
a way to generate it with a mathematical function (plus he doesn't give the
exact process used to arrive at the data points, just a general
explanation). Link 3 doesn't seem to provide any relevant information. If
you dig, you can find the percentiles that got a given _scaled_ score, but
that's pretty much useless given link 1 (circular). I repeat, if you can
come up with a particular mathematical formula that could be used, I would
use it (at least for entering prototype data), but it seems pretty clear
that this is not available.

But I do thank you for the time you have continued to put into this. I
would submit, though, that even if you did find an exact algorithm that
would work in all cases, probably my client would still want the ability to
input the numbers by hand.