Question Brand new and need help!

[TonyTone]

Hey all - just learned of this forum and it's looking good. I'm a teacher and I want to use Access 2007 to create a gradebook. The problem is I don't know how to set up my relationships.

The breakdown is as follows: I have about 140 students in four different classes. There are four quarters in the school year and in each of those quarters they will have many tests, labs, and homework grades. I also want to be able to use their contact data (i.e. their address, parents names, phone numbers).

I will have no problem with the query logic. It's just that I'm confused with how the relationships will be.

As it stands - I'm setting up the CLASS ID (primary - 1 to many), STUDENT ID (not primary related to class and 1 to 1 related to contact). I get so confused trying to make sense of the relationship between grades as they should relate to tests and quarters.

I realize this might be a lot ask, but I'm hoping someone might be want to give me some direction here.

Thanks

 

[HiTechCoach]

Check out these :

Data Models

 

[TonyTone]

Check out these :

Data Models

Thanks so much - but it looks like the link is dead.

 

[georgedwilkinson]

Looks like coach's link got a "v" stuck at the beginning:
http://www.databaseanswers.org/data_models/top_ten_data_models_students.htm

Building a database is more complex than troubleshooting why an URL doesn't work.

 

[TonyTone]

Looks like coach's link got a "v" stuck at the beginning:
http://www.databaseanswers.org/data_models/top_ten_data_models_students.htm

Building a database is more complex than troubleshooting why an URL doesn't work.

Building a database is indeed much more complex - sorry, I figured out the link problem but did not post that I had done so.

Thank you kindly for posting your response. I appreciate the assistance.

 

[georgedwilkinson]

Sorry, I didn't mean to come off as mean. But it is a good point. Building a database will tax your planning and troubleshooting abilities to the max.

 

[TonyTone]

Sorry, I didn't mean to come off as mean. But it is a good point. Building a database will tax your planning and troubleshooting abilities to the max.

lol - I'm a newbie and I'm very quickly learning that the planning can be as much a challenge as the building. I'm dying over here and haven't even started - lol - I'll get it though.

 

[Rabbie]

lol - I'm a newbie and I'm very quickly learning that the planning can be as much a challenge as the building. I'm dying over here and haven't even started - lol - I'll get it though.

Actually if you do the planning correctly the building should be straight forward. Most people start building before they have completed the planning and wonder why they have problems

 

[nealberk]

Tony, It looks like you are going to have to have more tables than you initially envisioned. I would have a student table, a class table, a test table, and tables to join them (student to class, test to class, and student to test to class) Just a very quick thought but I have done similar databases before. For assignments you would have to have a similar structure to that used for tests.

Fun, isn't it?

 

[The_Doc_Man]

I'll toss in this thought. Though some reference to calendar quarters will occur, it might not be relational. It might just be data used for confirmation or just report headers.

I'm leaning towards the following approach, and you should search this forum for some of the things I highlight.

I would use synthetic (=autonumber) keys for student, test, and class tables, then enforce the relationships using the synthetic keys. The reason I say that is because you will have lots of repetition of students taking various classes, lots of repetion of tests, and if the system is based on classes changing every quarter, lots of repetition on class rolls. The reason I lean this way is that a synthetic key is always fairly short.

Considering that you would teach the same classes from year to year (a presumption on my part, but for teachers, probably a good one), I would use a child table to list the instances of each class. I.e. you taught (say) Math 101 in Q1 of 2009 and again in Q1 of 2010.

I would use a junction table between the individual students and the child of the master class table to identify individual class rolls. Your calendar quarter would be part of the junction through the class-child tables, because a date is related to a particular instance of a class. You would use the calendar quarter to establish grouping.

Exams would then depend on your viewpoint, but I would make the exams another junction table between the class-child and some list of "standard" exam titles, like "First full exam", "first spot quiz", etc. etc. as you need them. Then grades would be a junction between the exam-junction and the student.

Sounds complex? That's what the others have mentioned earlier - that designing a database can tax your patience, ruin your eyesight and appetite, and make you generally intolerable to live with until you get the design cleared up. This is because to make a database that tracks some real-world process, you have to stop doing things by rote and actually think about what you are doing. This is not so easy as it sounds, sometimes. Which is why I sometimes drop my "Old programmer's rules." In this case, I'll drop one apropos to your current stage: "You can't do it in Access if you can't do it on paper." I.e. if you don't understand it well enough to do EVERY STEP by hand, you will never be able to tell Access how to do it by machine.

I'll drop another rule on you: "Access won't tell you anything you didn't tell it first." I.e. if you want to see xyz on a report, you need to assure that you CAPTURE or COMPUTE xyz in the input phase or report generation phase of what you are doing. Access can only remember data you told it, or remember a formula for that data. If neither of those supports your desired output, you screwed the pooch in your design phase.

Good luck!