HELP- trying to create a database to create Student Progress repor

[mrsr84]

I work in a school that has students from grades 5-8. We have almost 1000
students (approx 250 per grade). Each student has the 6 basic classes and
then the Specials- Gym, Art, Music, Library, Computers and a few other
electives. Most of out staff is not computer savvy and have been filling out
Progress Reports by hand. We use InterGrade Pro for our gradebooks but these
do not talk to one another for Progress Reports.
I have created multiple queries to divide the grade 6 into groups by Subject
(ie Reading) and ask the user to input the Teacher name. I then built the
accompanying form based on the query. I have used option group buttons to
have the user choose if the student is passing, marginal or failing but the
information is not being stored- or ifi it is, I don't know where.
When I tried to build a report to include all the fields from each form, was
told that I had too many fields. Should I build a form that would then be a
subreport?

I definitely need help- it's been awhile since I used Access and I am using
XP2003 version.
Thanks in advance for the assistance.

 

[BruceM]

I can't quite figure out your database structure, but I wonder if you need
separate queries for each subject. If the form is based on a query and the
user is required to enter the teacher name (see Validation in Help for more
ideas on this), then the report can be based on the same query. You can set
it up as a parameter query so that you can select a single subject for the
report, or you can use report grouping to separate by subject, then student,
or vice versa. You may be stuck in spreadsheet-think.
To be any more specific it will be necessary to see your database structure.
You certainly need a student table and a teacher table, and then something
like a subject or course table, but there are a lot of choices along the way.
Whatever else you do you should probably be storing primary keys from the
teacher and student tables rather than the teacher and student names. A
primary key functions like a social security number (although a SS number
should probably not be used, for security reasons if nothing else) in that no
matter if the person's name changes, all records for 123-45-6789 relate to
that person.

 

[Duane Hookom]

We don't know how your tables are set up. If you have subjects as field
names, I would start over. There is a somewhat appropriate data model at
http://www.databaseanswers.org/data_models/student_assignments/index.htm.
The Student_Course_Assignments table could be used to store scores on
assignments. The Student_Course_Registrations table could store the final
course/class grade.

I would probably not use multiple fields in the primary keys of tables but
the basic model is sound.

 

[mrsr84]

Thanks so much for the suggestions. Would it be helpful for me to upload the
database at this point? If so, how can I do this? It I can upload the
database, then the structure would be able to be talked about more
intelligently. Let me know if this is possible.
Thanks again.

 

[mrsr84]

Thanks for the reply. I looked at the database you suggested but did not
find it applying to our needs. We are not using this to track student work
and assignments by teachers. Part of the structure would work. Is there a way
for me to post the database that I have built so far? Then we can talk
intelligently about it.
Please let me know if this is possible.
Thanks again.

 

[Duane Hookom]

You can type your table structures into a reply in this thread. Also provide
a specification of how you would like to use your tables or what
functionality you would like to provide for your users.

Attachments are generally frowned on in news groups.

 

[mrsr84]

I am on my 4th try in building 1 grade's database. I have imported the
student information and created a student table. The fields and data types
are as follows:
StudentID primarykey- Number
Last name text size 25
First Name text size 25
Homeroom Number Text 3 (we have portable classrooms whose number starts with
the letter P)
SectionID text size 10 (combination of letters and numbers)
Course Title Text size 25
Teacher Name text size 25
Term Code text size 3
Course Status number byte (using this with option group in form as toggle
buttons)
Comments text size 200

Another question- I have built the form off of a query that has parameters
asking for term code input by user so only certain courses would show up. I
want the Homeroom number to show throughout the use of the form. Currently it
only shows up when the course "Homeroom" shows up. I do not want to have
"Homeroom" show up in the course. Any suggestions?
Thanks in advance.

 

[Duane Hookom]

My suggestion is to go back to the link I provided earlier and normalize
your table structure. I would not have Course and Teacher information in a
student table.