Storing calculated values in a table for a good reason (I hope)

[MotherZen]

I have read all the arguments here that calculated values should not be
stored in a table, but have to bounce it off of ya'll one more time. I am
developing an access form that will grade test scores, compare pre- and
post-tests for change in knowledge and then calculate the total change in
knowledge for each test taker.

The data reflects change in pre- and post-test scores for a single
educational class. I have a nice user friendly access form that
automatically grades each item of the tests as they are entered (IIf
[item]=correct answer,1,0), subtracts each pre-test scored value from it's
respective post-test scored value to determine change in knowledge
(=[postitem]-[preitem]), and then adds up all the item changes in knowledge
for each record (=[changeitem1]+[changeitem2]+...).

However... I have to export all this into SPSS for statistical analysis to
examine change in knowledge as it relates to SES data connected to the
population educated. Yes, I could do these calculations in SPSS too, but
then I would be doing the calculations twice, as the Access forms will be
printed for class documentation. So. Want it on the Access table!

Prior to adding the additional calculation fields to the Access form I had
tried exporting the SES data and test answers to excel and then copy/paste
the formulas. Since this does take time and opens the chance for additional
errors, I hope to automate the grading process, preferredly in Access. I
then tried the calculations in a query, and only returned a single, blank row
on the table, with no data appearing in the new fields on the form.

When the form is finished, the data will be keyed and will be exported at
set intervals for review. Once keyed, the records will not change (unless
there is a typo). Maintaining the integrity of the data is important.

Suggestions?

 

[Jamey Shuemaker]

On the surface this structure doesn't seem to violate normalization
because you are creating a recordset that, while not entirely
inimitable, is not easily duplicated outside your test grading
(code-level) procedure. If all test results, on a question-by-question
comparative basis, are stored in your database, then you could
duplicate the results of your posttest-pretest calculation. It is
likely not feasible, natural, or within the constraints of a machine
running this many consecutive calculations, advisable to duplicate
these results each time you look at the dataset for marked
incremental/decremental changes in test scores.

In general, if the resulting dataset cannot be easily or quickly (i.e.
efficiently) duplicated it is not entirely inadvisable to store the
dataset, regardless of inimitability, and in light of current
constraints. That is to say, if reproducing these results exceeds the
current capabilities of your current hardware/software or any
hardware/software that will likely be useful and scalable to meet such
demands in the perceivable future, you should likely store these
values. That is not to say that the bounds of your current strata are
not likely to be exceeded by concurrent invention, rather to say that
"You just don't have the means, at present, to adequately display these
values, otherwise."

By rule, your normalization will be flawed, but it is not likely that
you are storing resuts on a question-by-question basis, as this will
inevitably lead to one muther of a database. As such, storing the
quantities you suggest is neither incorrect, nor inadvisable.

In short, there's nothing wrong with the proposed structure except that
it violates the basic premise of relational databases (storing
calculated and retrievable values in lieu of fetching them later).
Still, under this structure, the values are not easily retrievable or
duplicable, and should be stored.

In the long-run, you may actually save space by returning test-by-test
results vice question-by-question, and this satisfies the overarching
concern of byte-dependent database management -- "Size is everything".

Form over fashion, unless fashion is inexpensive.

 

[Marshall Barton]

I have read all the arguments here that calculated values should not be
stored in a table, but have to bounce it off of ya'll one more time. I am
developing an access form that will grade test scores, compare pre- and
post-tests for change in knowledge and then calculate the total change in
knowledge for each test taker.

The data reflects change in pre- and post-test scores for a single
educational class. I have a nice user friendly access form that
automatically grades each item of the tests as they are entered (IIf
[item]=correct answer,1,0), subtracts each pre-test scored value from it's
respective post-test scored value to determine change in knowledge
(=[postitem]-[preitem]), and then adds up all the item changes in knowledge
for each record (=[changeitem1]+[changeitem2]+...).

However... I have to export all this into SPSS for statistical analysis to
examine change in knowledge as it relates to SES data connected to the
population educated. Yes, I could do these calculations in SPSS too, but
then I would be doing the calculations twice, as the Access forms will be
printed for class documentation. So. Want it on the Access table!

Prior to adding the additional calculation fields to the Access form I had
tried exporting the SES data and test answers to excel and then copy/paste
the formulas. Since this does take time and opens the chance for additional
errors, I hope to automate the grading process, preferredly in Access. I
then tried the calculations in a query, and only returned a single, blank row
on the table, with no data appearing in the new fields on the form.

When the form is finished, the data will be keyed and will be exported at
set intervals for review. Once keyed, the records will not change (unless
there is a typo). Maintaining the integrity of the data is important.

Don't confuse yourself between storing calculated datat and
exporting data. They are two separate operations. I don't
think this a justifiable case for storing calculated values
in a table. You can already display the calculated values
on your form/report without storing them in a table so that
part's a no brainer. For exporting (and if the calculations
are not too complicated) you can calculate th values is a
query and export that instead of exporting the table.

OTOH, if it gets too complicated, I wouldn't lose a lot of
sleep over storing the calculated values in a table as long
as you can guarantee that there is absolutely no way a user
can edit those values.

 

[MotherZen]

Marsh,

Thank you for being concise. I did try to use a query to capture the
caculated values. - it just did not work. I will play with the query-calc
thingie inbetween projects to master it. Meanwhile, I think I will export to
..dbf and recaculate in SPSS.

One of these days I WILL conquer Access! *grin*

MZ

 

[MotherZen]

Marsh,

Thank you for being concise. I did try to use a query to calculate the
values - It just did not work. I will play with the query-calc thingie
inbetween projects to master it. Meanwhile, I will just export to .dbf and
recaculate in SPSS.

One of these days I will conquer Access! *grin*