Creating a DB with friendly forms

[caerulea]

My wife is a school teacher, and I am trying to help her by creating a database that can track student performance and calculate grades. The basic structure has 3 tables:

Students (e.g., [StudentID], [Name], etc.)

Assignments (e.g., [AssignmentID], [Subject], [Discipline], [Date], etc.), and

Grades (e.g., [StudentID], [AssignmentID], [Score]).

However, she does not want to have to enter the students' names or even ID # every time she creates a new assignment. My solution was to create a form based on the Assignments table, then include subforms based on the other two tables. Ideally, the students sub-form would be populated with all of the students, and she would only have to enter scores for each student. Unfortunately, I cannot get it to work.

Should I try a different approach? Is there a good source of example databases that might have a similar structure?

I would appreciate any helpful suggestions! Thanks!

 

[chubby]

Why not base your form design on query that’s links the tblStudent, tblAssignment and tblGrade.

Then design some reports that basically does the same thing, uses a query to pull from the three tables to get what ever type of information she want from the three tables.

 

[caerulea]

Hi Chubby,

Thank you for responding! I appreciate you trying to help me out. Unfortunately, I think I don't get it. When I build a form based on a query that links the 3 tables, for each assignment, the user still needs to re-enter each student name before assigning a score. I don't think I will have any problems generating reports that summarize the information for her, but I wanted to make it as easy as possible for her to enter the data. I think there is a way to do this, but I'm not savvy enough to figure it out, (or even explain it clearly!).

 

[Spyder757]

This is a completely diffrent approach but if I didn't want to remember each students ID, or type their whole name I'd make a combo box with each students name.

Make a table called "Students". In this table key in each students name.

On the form you use to enter the new data make a combo box for the students name and have it pull directly from the "Students" table.

Just my suggestion.

Spyder757

 

[caerulea]

Thanks for the suggestion, Spyder. While that would reduce the effort required for the user to input the data for each assignment, it's still more effort than the user (my wife) is used to doing in Excel. Instead, I've come up with an unconventional solution. I have created one table with [Date], [AssignmentType], etc. plus each student is an additional field. This allows me to create a form in which the user can define the assignment for each record, and then user can tab to each student's name and just use the number pad to enter the score. Not the most sophisticated or robust design (especially for adding new students), but ease of data entry was the most important criteria in this case.