Windows 7 64bit 
Access 2007
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
Reply With Quote
