Gradebook Database

[lraffel]

I am trying to design a gradebook database. Many students take a given test multiple times and my current system does not provide any trend analysis so I am trying to work with Access.

My primary tables are:
Students (Names, Address, etc)
Test Iteration (Date, # of participants, etc)
Questions (Question itself, parameters of acceptable answers, corresponding grades, and percent of overall grade they are worth)

I am trying to figure out the best way to join a student with their test iteration and the grades. Here are the 2 ideas I'm considering, but not sure which is a better solution (or if there are any other solutions out there)

Solution 1
Table: Assessments
Fields: Student ID, Test ID, Question ID, Score
Pros/Cons: To me this seems to put the table in a classic customer/product/order form, but I would have nearly 30 records for each student for each test. With 5-10 students/test and 24 test/year, that is alot of records very fast! How much data can Access really handle? How big can my table be on a typical at home computer?

Solution 2
Table: Assessments
Fields: Student ID, Test ID, Question 1, Score1, Question 2, Score 2, etc
Pros/Cons: This would seem to create a smaller database with everytime a student takes a test creating 1 record, but then it seems more difficult to reach back to the Questions tables in case I want to reformulate the weights (also there is calculation from the raw scores that are entered to what acutally becomes the final "score" for each question)

Any help would be appreciated or if you know where I can go for help outside of this forum would be great. I want to do this myself, but am worried about the pitfalls I may, as a novice, get myself into. I have about 2 months to get this up and running.

Thanks!

[June7]

Access size limit it 2 gigs. So it's not the number of records but the data structure and how much data is stored that determines how big the project can get. Also, design edits cause a file size to grow, need to run Compact & Repair periodically, definitely after major design changes.

Your first option complies with db design conventions for normalization of table structure and seems better suited for the data manipulations you want to do. The second option might be easier to design data entry for but as you noted will make data manipulation more difficult (not impossible). Access tables have a limit of 255 fields. I don't suppose you would have enough question/score pairs to hit this limit. Assuming every student would have every test and every question, both options have the same amount of data, just arranged differently.

[lraffel]

Couple of questions/responses:

1. I have "Compact on Close" checked under Access Options. Is this good enough to keep it running efficiently? Otherwise, where is "Compact and Repair"?

2. I like the table that conforms to normalization better. The way I understand your reply is that the amount of data stored will be the same (either more fields or more records), no matter which design I pick?

Thanks!

[June7]

1. Compact & Repair for 2007 is found on the Office Button (the big round MS icon that goes away in 2010), the one that took you to the Options dialog. Click the button then select Manage.

2. That's my understanding.

[lraffel]

Thanks for the assist on that question. One more design question that is bugging me.

I want to save expressions in fields: "<=1", "<=3", "<=5", and ">5" that I can later use as real expressions. So I have saved these parameters as text fields, but how can I later use them to actually compare to scores?

It seems easier to do this, then to depend on the expression builder in a query later. Just because for some questions high scores are good and for others low scores are good.

[June7]

Never done or seen anything like this.

You have 4 fields named "<=1", "<=3", "<=5", and ">5"? First of all, I would not use special characters in field names (same for spaces and punctuation - underscore is exception).

Or you have one field with string values "<=1", "<=3", "<=5", and ">5"?

Show example of expression.

[lraffel]

Basically, I have to turn scores into ratings.

It looks like this:

Data Table: (right now the parameter fields are text)

Questions|T1 Parameters | T2 Parameters |T3 Parameters|T4 Parameters
Question A |<=1 | <=3 | <=5 | >5
Question B |=100 | >=85 | >=75 | <74
Question C | NULL | Y | NUL | N

Then in a calculation query I want to be able to take raw scores and compare them to the parameters to get the ratings.

Ex. Question A, Score = 3. Compare 3 to Ratings (Pretty sure I can do this with a Switch statement, once I can get the formula to recognize the text or find someway to store the parameters as numbers). Thus, 3 = T2

[June7]

Expression could not be 3=[T2]. This would compile as: 3='<=3' and would always evaluate false. I did test by adding these fields to a table and trying a concatenation of the two fields to construct expression in a query. It does not evaluate, simply results in a string '3<=3'. Same thing when I test in the VBA immediate window. Need to use Eval function on the concatenated string.

[lraffel]

Understand the expression would not be 3=T2, just that I want an expression that takes the score and evaluates it as T2

So, if I have to store as text and can't import the text to make an expression, then any ideas on how to create a query that would evaluate my scores correctly? As in my example I generally have three types (all would need different expressions); even if I did save the set points as a number field.

Also, I will be leaving in a few months and I am trying to make manipulation of questions and ratings parameters as easy as possible.

[June7]

I was testing and editing while you posted. Check my previous thread again. The Eval function worked in query and code. However, complication arises because some of your parameters are Y/N boolean and the concatenation will not be valid and the Eval function will fail.

[lraffel]

Can you copy your expression here? I can probably figure out the syntax I need to use from that...otherwise I'm not entirely sure how you concatenated the string and then used the eval function.

[June7]

In a query I constructed a new field with:
Exp: Eval([Score] & [T1])
This evaluated True or False (-1 or 0)

I did not test with boolean value/parameters. Why would there be boolean values for a Score?

[lraffel]

Nice. I am at home and will have to test it with my database at work tomorrow. I'll let you know how it goes.

[lraffel]

Works like a charm. Thank you.