Student Grades database

[Seth Schrock]

I'm working on a database that will log student's grades. These grades are currently in paper format and they need to be place in an electronic format. Currently these grades are split up by semester, so there is a column for semester 1 and another for semester 2. For the sake of normalization, each class should then have two records: one for the first semester and one for the second semester with a field to differentiate between the two. However, this also makes for another record to fill out. Does anyone have a trick up their sleeves that would allow for a table design setup like this:

Class     Semester       Grade
English   1              85%
English   2              87%
Math      1              91%
Math      2              90%

But would allow the form to look like this:

Class     Semester 1 Grade       Semester 2 Grade
English   85%                    87%
Math      91%                    90%

I know how to make a query that would display it this way, but the query wouldn't allow edits and that would defeat the purpose. I thought about making side-by-side subforms, but that wouldn't allow for easy tabbing between fields as if they were on the same form. I ask this because this is how it is entered on the paper form and it makes entering onto the computer easier if the formats are the same.

Or is it okay to break the rules of normalization for this? In this situation, my system is only to get the paper grades into a computer format easily and not to continue putting new grades into it, so basically this is a one time project and the program will go away once all the grades (between 1970 - 2010ish) are entered.

[Stewart Ross]

Hi Seth. In this instance I'd go with the non-normalised table with the two semester grades in it. It's an intermediate step to take, and is by far the quickest and simplest way to get the legacy data input.

You'd have the same issue if you implemented, say, automatic scanning to enter the data from the paper copies themselves - the data would have to transfer to a single table if you were scanning them, from which you'd extract the two semester grades separately.

There are many occasions when we have to balance the strict theoretical requirements against the other practical requirements which compromise the ideal approach. In this case a normalised approach is going to be very hard to implement for one-off requirements like this - I'd go with the 'quick and dirty' one myself!

-Stewart

[Seth Schrock]

That is kind of what I figured, but I wanted a second opinion from someone with more experience than me. Thanks Stewart.

[Rabbit]

I think unnormalized is fine in this situation as well. In this case, it's unlikely that you will gain "semesters" and most every student will have both semesters filled out. So the benefits of normalization are minimized in this scenario.

But for those wondering, I've done this in the past with subforms. The main form would return the distinct students. And you would have 2 subforms that return the rows for each semester.

[NeoPa]

UNION queries being non-updatable are a feature specific to Jet (MS Access) SQL as far as I understand. As such, using Access, it is much easier in this case to go for the de-normalised approach. It's what I'd recommend in these circumstances.

However, I'm interested to see that our resident SQL super-expert has again come up with something for my I didn't know that category. It's still not fully flexible, but is nevertheless a very interesting technique to store away for a rainy day.

Kudos Rabbit!

[Ammarhassan48]

Compsite key will be better instead of subforms

[Rabbit]

I don't see how a composite key allows them to enter the data in the way they want.

[NeoPa]

"Compsite key will be better instead of subforms"
No. This is misleading and wrong.

If you have a reason for making this bald statement then please provide it. Otherwise you mislead the readers who may assume you know what you're talking about simply because you've posted.

[zmbd]

wouldn't a crosstab query return the desired report format, or did I miss something here?

[Seth Schrock]

I'm needing a way to enter the data, not just view it. So yes, crosstab would allow me to view the data stored "normalized " in the correct format, but it wouldn't allow me to enter it the way I'm needing.

[zmbd]

This is one of those times when I will use automation and Excel; however, I don't know if that is any easier than what the others have suggested. (^_^)