 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / July 2005Gradebook Databse Setup
Hello! I currently use Excel to hold my gradebook (each semester is a different workbook, with a worksheet for each class), and since I would like to start learning how to use Access, I would like to create a gradebook database. I currently teach at three colleges, and in some cases I teach the same course at more than one college. I don't expect that I will have the same student at more than one college, but will certainly have the some of the same students in more than 1 class at a particular college. I would like a single database to hold all student grade and course data for every class I teach. I know that the table structure is important, and would like to get some advice before I start creating the tables. I think I need the four main tables with the fields below. The numbers in parentheses are the number of different fields for that data type. Main Table Fields: Student ID # (Primary key) First name Middle Initial Last name College Phone Eail Class Table Fields: (Will need one table for each class I teach each semester to hold the actual grade data. This will mean adding 6-10 tables every semester depending on how many classes I teach.) Manual Data Entry Fields Student ID # Quiz grades (5 separate quizzes, so will need 5 fields) Quiz Max Score (5 fields) Homework grades (5) HW Max Score (5) Class Participation (15) Participation Max Score Exams (3) Exam Max Score (3) Weight of each item (about 6 fields) Extra credit points (if any) Grade curve (if any) Grade Awarded (Will usually be the same as the Grade Earned below, but may be adjusted upward) Calculated Fields Participation Grade Quiz average HW average Exam score in % (3) Overall average Grade Earned Course List Table Fields: College Course ID (ex: MAT 0024) Course Name (ex: Algebra) Grade Scale Table Fields: Overall average Grade Earned (some kind of lookup table to automatically find the Grade Earned based on the Overall average. Something similar to Vlookup in Excel) Is my goal reasonable, given that I will have to add a bunch of new tables each semester? I hope to be able to copy and paste the new tables since they would be very similar to tables previously used. I think I would need a new table for each class each semester because the mix of assignments and weights for each will vary on occasion. Thank you in advance for any advice you can provide. If your are 'adding tables' and/or 'adding columns' your design is not right (period, end of discussion) Sounds like you have: students colleges courses college-semester (each college has a different semester, tri-mester arrangement) (college-semester)(course) courseEnrollment (people in a course taught during a semester) grades. each college has many semesters, each semester has many courses, each Semester/course has many student enrollment, each student (enrollment) has many grades in a course. Each grade is earned by a student, enrolled in a course, during a semester, at a college. (their final grade is calculated based on the grades earned during the semester). Also might want to pull out email and phone as a 'child table' of students. at least some students will have multiple phones and multiple email addresses. They also may need to have more than one address (daddy, mommy, school) The above logic should put you in the right ball-park. Table normalization is both easy and complex! Lots of luck Ed Warren > Hello! > [quoted text clipped - 100 lines] > > Thank you in advance for any advice you can provide. > I know that the table structure is important, and would like to get > some advice before I start creating the tables. you're right, Lewis. designing the tables/relationships is the FIRST, *and* most important, step in developing your database. suggest you stop where you are, step away from your computer, and read up on relational data modeling. to learn to do it right, so you don't have to keep re-doing and re-doing it all school year long, one good text is Database Design For Mere Mortals by Michael Hernandez. you can also find helpful links (as well as tons of links to many other aspects of database development) at http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html beginning with the Starting Out and Database Design 101 links. these newsgroups are also an excellent resource, when you have a specific problem or question that you need a hand with. hth > Hello! > [quoted text clipped - 99 lines] > > Thank you in advance for any advice you can provide. Tina and Ed, Thank you for your replies. I'm off to the library and/or bookstore before I go any further. Lewis But wait.....here's some free resources that you can access from home: Database Design You should spend some time gaining an understanding of database design and normalization before attempting to build something in Access (or any RDBMS software for that matter). Here are some links to get you started. Don't underestimate the importance of gaining a good understanding of database design. Brew a good pot of tea or coffee and enjoy reading! http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf http://www.datadynamicsnw.com/accesssig/downloads.htm (See the last download titled "Understanding Normalization") http://support.microsoft.com/?id=234208 http://support.microsoft.com/?id=289533 Also recommended: Find the copy of Northwind.mdb that is probably already installed on your hard drive. Study the relationships between the various tables (Tools > Relationships...) Naming Conventions Special characters that you must avoid when you work with Access databases http://support.microsoft.com/?id=826763 Commonly used naming conventions http://www.mvps.org/access/general/gen0012.htm http://www.xoc.net/standards/default.asp Using a Naming Convention http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/dec onusingnamingconvention.asp Reserved Words Reserved Words in Microsoft Access http://support.microsoft.com/?id=286335 List of reserved words in Jet 4.0 http://support.microsoft.com/?id=321266 Don't attempt to store the results of any calculations. Doing so violates both 2nd and 3rd normal form of database design. To amplify a bit on what Ed Warren stated: "If your are 'adding tables' and/or 'adding columns' your design is not right (period, end of discussion)" The need to add new fields or tables to accomodate similar data should be a big red flag. Something to remember about good database design: "Fields are expensive, records are cheap". Anytime you need to add more fields (or tables) you will need to modify existing queries, forms and reports. Tom ________________________________________ Tina and Ed, Thank you for your replies. I'm off to the library and/or bookstore before I go any further. Lewis |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.