By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,462 Members | 1,126 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,462 IT Pros & Developers. It's quick & easy.

Student Grades database

Seth Schrock
Expert 2.5K+
P: 2,951
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:
Expand|Select|Wrap|Line Numbers
  1. Class     Semester       Grade
  2. English   1              85%
  3. English   2              87%
  4. Math      1              91%
  5. Math      2              90%
  6.  
But would allow the form to look like this:
Expand|Select|Wrap|Line Numbers
  1. Class     Semester 1 Grade       Semester 2 Grade
  2. English   85%                    87%
  3. 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.
Feb 28 '14 #1

✓ answered by 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

Share this Question
Share on Google+
10 Replies


Expert Mod 2.5K+
P: 2,545
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
Feb 28 '14 #2

Seth Schrock
Expert 2.5K+
P: 2,951
That is kind of what I figured, but I wanted a second opinion from someone with more experience than me. Thanks Stewart.
Feb 28 '14 #3

Rabbit
Expert Mod 10K+
P: 12,430
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.
Feb 28 '14 #4

NeoPa
Expert Mod 15k+
P: 31,768
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!
Mar 1 '14 #5

Ammarhassan48
P: 3
Compsite key will be better instead of subforms
Mar 3 '14 #6

Rabbit
Expert Mod 10K+
P: 12,430
I don't see how a composite key allows them to enter the data in the way they want.
Mar 3 '14 #7

NeoPa
Expert Mod 15k+
P: 31,768
"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.
Mar 4 '14 #8

zmbd
Expert Mod 5K+
P: 5,397
wouldn't a crosstab query return the desired report format, or did I miss something here?
Mar 4 '14 #9

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Mar 4 '14 #10

zmbd
Expert Mod 5K+
P: 5,397
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. (^_^)
Mar 4 '14 #11

Post your reply

Sign in to post your reply or Sign up for a free account.