Help with Academic Database (1 Viewer)

MissBrandyLea

New member
Local time
Today, 03:36
Joined
Jan 11, 2012
Messages
1
I would appreciate any help on this… I have made several attempts at this over the past three years, each time hitting a “wall,” and settling for a lesser solution (with compounded headaches for the trouble).


I am the guidance counselor for a small school of approximately 100 students. The software which manages our grades is woefully lacking with respect to reports, calculations, etc. I would like to create an Access database to which I can append weekly data, allowing us to view a number of traditional reports not just “as of now,” but “as of date X.” More importantly, my goal is to be able to select a student (or students) and see a trend of grades in various subjects over time.

My source of data is a large print-out from our current software. Upon printing (to Excel), the printout will provide the following information:

Student Name Class Current Average/Grade Gradelevel Teacher

Currently the very basics of my database are as follows: (* = primary key)
tbl_Averages
*ID Average Date(of average) Lookup to tbl_Class Lookup to tbl_Student

tbl_Class
*Class Teacher

tbl_Student
*Student Gradelevel


I can create a basic query uniting this data back into a single form, which I call qry_Base:
qry_Base
Gradelevel Student Class Teacher Average



What I want to accomplish:

1. Appending of data: I would like to be able to add additional data (to tbl_Averages) weekly without creating a new table for each set of data. I can add the appropriate date stamp to the data while in Excel if necessary.
2. Base Query: I would like to be able to view the basic query above, but filtered by a modal asking for which Date set (preferably from a drop-down box).
3. Student Query: I would like to be able to select a student (probably from a drop-down box in a modal), and view for that student a query (with the goal of porting to a report) to look something like the following:
4. Print reports based on both the Base Query and the Student Query.

Proposed qry_Student

1/9/12 1/16/12 1/23/12 1/30/12
English8 90% 95% 91% 87%
Math8 78% 80% 84% 84%
Science8 56% 60% 62% 65%
History8 95% 90% 80% 76%

Those are the primary goals; most other manipulation of data into reports I should be able to handle on my own, once I learn how to wrap my brain around implementing dates.

What I have done in the past:
My poor workaround in the past was to have multiple dates columns in the tbl_Average, one for each data set. i.e:

tbl_Average
ID lookup to tbl_Class lookup to tbl_Student Average_01-09-2012 Average_01-16-2012 Average_01-23-2012 Average_01-23-2012

Each week I would print the new data to excel, ensure that is was in exactly the same order as the prior week, copy the averages column only, and paste it into the newest Averages_Date column.

This accomplished Goals 2 and 3 quite nicely, with a few minor caveats and one major ones:
Minor caveats:
Access seems a bit wonky about having multiple columns with only different numbers at the end. Some minor workaround seem to work out, but I don’t have a firm enough grasp on Access’s original complaint to feel confident about the method.
I have the nagging feeling that there is some lost flexibility in dates being fixed columns, instead of sort-able, filterable, values.

Major caveat:
With this method, the only way new information can be added to the database each week is for the printout from our grading software to be in EXACTLY the same order as every week in the past. However, a living school changes, and throughout the semester, students change classes, are added to our enrollment, or leave the school. In the past, I’ve tried to go into the database and adjust it manually for each change to our students’ schedules, but getting in over my head, I’ve often done more harm than good. Knowing the process is a royal headache, I often just abandon the project.

What I can do:
I have reasonable experience in the following Access skills:
Creating, modifying tables
Within tables, creating basic lookup relationships
Creating, modifying one-to-one relationships
Creating, modifying basic select queries
Creating, modifying basic forms, subforms
Creating and modifying basic reports

What I have experience in, but would need to brush up on:
Modifying some very rudimentary Visual Basic
Creating filtering modal boxes for queries, forms, reports.
Other things I don’t remember. Most of my original Access work was on Access 2003; I’m now running Access 2007.

I have virtually no experience in Crosstab Queries. I have tried playing around with them a bit, as they do seem particularly well suited to my visual goal of grades/dates, but have had little-to-no luck getting it to actually do what I want. Additionally, if there is a way to create a good report to “match” a crosstab query, I was entirely unable to discover it. If you propose a crosstab query as a solution, please provide me a bit more details, as simply “Oh, use a crosstab query!” won’t really be enough to get me started.


I apologize for the length here, but it is my first time requesting information of this magnitude, and I wanted to provide as much up-front data as possible.

I really do appreciate any help, even if it’s just “You’re going to have to deal with adjusting the order each time; no other way to go about it.”

Thanks!

BrandyLea
 

jzwp22

Access Hobbyist
Local time
Today, 06:36
Joined
Mar 15, 2008
Messages
2,629
Welcome to the forum!

Any relational database starts with a good table structure (tables and relationships) which means that it follows the rules of normalization.

My poor workaround in the past was to have multiple dates columns in the tbl_Average, one for each data set. i.e:

The above is an example of a table that is not normalized.

OK, so let's set everything else aside and work on the table structure. The following is just a brainstorm of possible structures based on your description. It will probably need some modification, but it will at least get you thinking about the relationships that exist.

First, you will need a table to hold the basic information about people (students and teachers)

tblPeople
-pkPeopleID primary key, autonumber (pk=primary key)
-txtFName (txt=text field)
-txtLName
-fkRoleID foreign key to tblRoles
(you probably have some other fields that would go in this table)

The tblRoles holds the various roles a person can play (student, teacher, principal etc.). I am assuming that a person only has 1 role, do if that is not the case in your application please let me know.

tblRoles
-pkRoleID primary key, autonumber
-txtRoleName

Now you mention grade levels, so a table to hold those

tblGradeLevels
-pkGradeLevelID
-lngGradeLevel (lng=long integer number field--you may want something else)

I assume that you want to track things over many years, so a table to hold information about a school year

tblSchoolYears
-pkSchoolYearID primary key, autonumber
-txtSchoolYearDescription
-lngYearStart (or a date field)

Assuming that a student will attend school for many years and associated with each year attended is a grade level (assuming a student cannot change grade levels during a school year), we need to relate those

tblStudentYearGradeLevel
-pkStudentYrGradeLevID primary key, autonumber
-fkPeopleID foreign key to tblPeople (fk=foreign key, the foreign key field must be a long integer number)
-fkSchoolYearID foreign key to tblSchoolYears


You also have terms (quarters or semesters etc.)

tblTerms
-pkTermID primary key, autonumber
-txtTerm (or you can use a number field)

Each school year has many terms, so a table to relate them

tblSchoolYearTerms
-pkSchoolYearTermID primary key, autonumber
-fkSchoolYearID fk=foreign key to tblSchoolYears
-fkTermID foreign key to tblTerms

You also have classes/subjects, so a table to hold those.

tblClasses
-pkClassID primary key, autonumber
-txtClassName

It sounds like you have some classes that are associated with more than one grade level (math8?), so a table to relate classes and the grade levels in which they are taught.

tblClassGradeLevel
-pkClassGradeLevID primary key, autonumber
-fkClassID foreign key to tblClasses
-fkGradeLevelID foreign key to tblGradeLevels

Now for each term (of a year)/class/grade level combination you will have a teacher (assuming that each class only has 1 teacher)

tblTermClassGradeLevelTeachers
-pkTermClGrLevTeachID primary key, autonumber
-fkSchoolYearTermID foreign key to tblSchoolYearTerms
-fkClassGradeLevID foreign key to tblClassGradeLevel
-fkPeopleID foreign key to tblPeople (teacher)

For each combination of term/class/grade/level/teacher, there will be many students

tblClassStudent (I shortened the name for convenience)
-pkClassStudentID primary key, autonumber
-fkTermClGrLevTeachID foreign key to tblTermClassGradeLevelTeachers
-fkPeopleID foreign key to tblPeople (students)

Each student in the class will have many scores/grades

tblClassStudentGrades
-pkClassStudentGradeID primary key, autonumber
-fkClassStudentID foreign key to tblClassStudent
-dteGrade (dte=date field associated with the grade)
-spGrade (sp=single precision number field to hold the actual grade)
 

jzwp22

Access Hobbyist
Local time
Today, 06:36
Joined
Mar 15, 2008
Messages
2,629
Thinking about what I posted last night. We may not need the following table as the data is already captured via other tables.


tblStudentYearGradeLevel
-pkStudentYrGradeLevID primary key, autonumber
-fkPeopleID foreign key to tblPeople (fk=foreign key, the foreign key field must be a long integer number)
-fkSchoolYearID foreign key to tblSchoolYears
 
Local time
Today, 03:36
Joined
Feb 25, 2008
Messages
410
@jzwp22
Nice post! It's been a while since I've posted but this got my creativity flowing.
Your model is well on it's way to becoming a fully-fledged Academia Management System, capable of assigning classes to students for their formal schedules. All it needs now are a set of tables outlining class times, durations, prerequisites, passing periods, lunches, disciplinary actions or occurrences etc.
Add a security model and a nice multi-user interface made available to each teacher and have them enter assignments and grades individually or via batch and you got yourself a game changer.
 

jzwp22

Access Hobbyist
Local time
Today, 06:36
Joined
Mar 15, 2008
Messages
2,629
RossWindows,

I have helped others on this forum and a couple of others with similar academic mgmt models, some included the session breakout (meeting dates/times), prereqs etc.

Are you designing a model of your own?
 
Local time
Today, 03:36
Joined
Feb 25, 2008
Messages
410
nope. I just enjoy a challenge. I could program and build systems 'till the day I die; the more complex the better.
 

jzwp22

Access Hobbyist
Local time
Today, 06:36
Joined
Mar 15, 2008
Messages
2,629
I do this for fun to make up for my not-so-challenging job!
 

Users who are viewing this thread

Top Bottom