Hi Folks - i've been studying Access tutorials and reading lots of great stuff here, but am still a little baffled as a new user of Access. My real world problem is this: I work in our guidance department at a high school, where many large scale exams are administered to select populations. There are end-of-course exams for certain subjects (currently algebra, geometry, and biology) as well as our state's high school exit exam. Multiple grade levels are involved in each of these exams. Our school district's main data base provides the names and unique ID code of each student in the various testing populations, so that is easily imported data for either Excel or Access. Another download from our mainframe provides student schedules, however, in this download, the unique ID, as well as last name/first name are all listed 7 times to account for the 7 class periods in a school day. What i would like to do is bring daily schedule data for each student to those test groups, in one database, so if/when a student doesn't show up to their respective test, i can easily "hunt" him or her down and get them tested. As it is right now, we have to manually key in each of those errant students' ID code to look up their schedules, and then go find them. Time consuming.
I thought i could use the ID as my primary/foreign keys in both tables, where one table allows duplicates and the other does not, but i'm just not sure if that's even accurate or the right approach. I think i'm comparing many to many, in that courses will be duplicated as well as building and room numbers . . .
The data fields i stipulated in my first table were for all students school wide:
ID (PK?)
LN
FN
Period (1-7)
Class Name
Building Number
Room Number
Second Table -Test populations only
ID (FK?)
LN
FN
I'm just not sure how to get what i want out of this set up, or if it's even right, so i'm just sitting here scratching my head after much reading! What i would love to do is download a complete master schedule list for the entire school, then as we test, be able to sort that list by just the subject code of the upcoming test - resulting in full schedules of each kid in that population (taking that course/test).
If there's another post that describes this mess, i'd be happy to go look there. I couldn't find anything that directly related, although there was a referene in a previous post by BruceM that mentioned using a junction table with this sort of data. Any help you can offer will be greatly appreciated.
ML
Full Version: Student Testing/scheduling Problem
Welcome to UtterAccess.
Databases are, by their very nature, complex creatures. You'll probably never find a template that exactly fits what you need, either. That's because your business rules determine what information you need to track and how it has to be stored to be useful. There are similarities, of course, between many kinds of databases. Yours, for example, is involved with students and testing and those two things are required in many situations. The difference, of course, is how you need to handle that data.
It's not 100% clear how much information you truly need to track. The following is a fairly comprehensive list of tables you'd want to set up to track students and classes fully. You may only need part of this to find students at any given time. A a minimum I think you need a table of students, a table of classes and a junction table between them showing which student is in which class.
In a more fully developed database, though, you'd want the following.
You need a table of Students, correct. This table tracks information specific to a student, information that does NOT depends on any other thing, or entity: First and Last Names, DOB (if you track that), perhaps contact information (cell phone, home phone, etc.) and the unique StudentID as issued to that student by your school.
You need a table of class subjects. This table tracks information specific to subjects taught and to nothing else: A unique subject ID and a subject name.
You need a table of classes. This table tracks information specific to classes scheduled for subjects: A unique class ID, a SubjectID (foreign key to subjects), a period ( 1 to 7) , a weekday schedule, and a room number.
You need a table of rooms, and a table of buildings. The table of buildings identifies the various buildings on your campus: A unique buildingID and a building name.
The table of rooms identifies the rooms where classes can be held: A unique room ID, a building ID (foreign key to the buildings table) a room number and a room capacity. Capacity determines class size.
You need a junction table that joins students to classes, so you can find any given student at any given time.
This table has its own Primary Key, a foreign key to the student table and a foreign key to the class table. If the class table is fully developed, it will have enough date and time information in it to locate a student without having to add that date/time information to this junction table.
Good luck with your project./
Databases are, by their very nature, complex creatures. You'll probably never find a template that exactly fits what you need, either. That's because your business rules determine what information you need to track and how it has to be stored to be useful. There are similarities, of course, between many kinds of databases. Yours, for example, is involved with students and testing and those two things are required in many situations. The difference, of course, is how you need to handle that data.
It's not 100% clear how much information you truly need to track. The following is a fairly comprehensive list of tables you'd want to set up to track students and classes fully. You may only need part of this to find students at any given time. A a minimum I think you need a table of students, a table of classes and a junction table between them showing which student is in which class.
In a more fully developed database, though, you'd want the following.
You need a table of Students, correct. This table tracks information specific to a student, information that does NOT depends on any other thing, or entity: First and Last Names, DOB (if you track that), perhaps contact information (cell phone, home phone, etc.) and the unique StudentID as issued to that student by your school.
You need a table of class subjects. This table tracks information specific to subjects taught and to nothing else: A unique subject ID and a subject name.
You need a table of classes. This table tracks information specific to classes scheduled for subjects: A unique class ID, a SubjectID (foreign key to subjects), a period ( 1 to 7) , a weekday schedule, and a room number.
You need a table of rooms, and a table of buildings. The table of buildings identifies the various buildings on your campus: A unique buildingID and a building name.
The table of rooms identifies the rooms where classes can be held: A unique room ID, a building ID (foreign key to the buildings table) a room number and a room capacity. Capacity determines class size.
You need a junction table that joins students to classes, so you can find any given student at any given time.
This table has its own Primary Key, a foreign key to the student table and a foreign key to the class table. If the class table is fully developed, it will have enough date and time information in it to locate a student without having to add that date/time information to this junction table.
Good luck with your project./
Thank you so much for your reply. This is more complicated than i thought it would be, but you make perfect sense. I will still tinker (perhaps on summer break!) with advancing my knowledge base here and with Access, as i find it to be entirely useful for all sorts of reasons. However, as i'm in the throes of testing mania right now, i believe i'll have to "table" this project for another time. Thanks very much for your thoughts and time.
Marylea
Marylea
It's both more complex than a simple spreadsheet and not so complex as it might seem when you first look at it.
Basically, to just track students and where they are (or should be
) during each period of the day, you don't need a lot of complexity. However, to make the database flexible for future use, you do need to plan for ways to update the data, and that does call for more well-thought out table design.I know that you don't have a lot of extra time for study (no one involved in schools has a whole lot of extra time), but you can work at it a little at a time.
Continued success with your project.
QUOTE (GroverParkGeorge @ Apr 30 2012, 07:57 PM) 
It's both more complex than a simple spreadsheet and not so complex as it might seem when you first look at it.
I know that you don't have a lot of extra time for study (no one involved in schools has a whole lot of extra time), but you can work at it a little at a time.
Continued success with your project.
I know that you don't have a lot of extra time for study (no one involved in schools has a whole lot of extra time), but you can work at it a little at a time.
Continued success with your project.
And i absolutely will continue to work at it, mainly because what our MIS folks do and don't do frustrates me. I am frequently asking them for certain reports out of the main database for our school . . . things that any of the schools in our entire district would benefit from but apparently don't seem to know how to ask - and that's a big factor. There are left hand/right hand issues all over the place, but i will keep at this. It's worth it.
Thank you again for your insights and encouragement. I'll be reading!
Marylea
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.