Multi-student Forms & Reports

[MNM]

Greetings once again,
I created a database to record lesson information (see attached sample). When there was only one student per record, everything worked fantastic.

However, management would like to have one record per lesson, which means that up to eight students would need to be accounted for.

Adding additional Student_IDs to the lesson record table and the associated links to the student table was simple.
Inputting lesson information was pretty straightforward.

The problem arises with output.
There is one report to review an individual student's past lessons.
Also, there is a form in which to export the lesson's public comment.
Both use different queries, but with the same name as the form and report.
Both the report and exported Excel file are blank, no data.

Any assistance, as always, is much appreciated.
M. McAllister

P.S. I'm building this in A2010, but saved as A2003 (working environment).

 

[plog]

Your table structure is improper. You need to forget about reports and forms for now and focus on setting up the proper table structure for your data.

Just to get it out of the way, your query is failing because its all INNER JOINS. INNER JOINS act as criteria--if one side has data, but the other side has no matching data, the record will not show. TBL_LessonRecord has one record, that one record has no values in the Student4_ID to Student8_ID so those fields can never find a match in their corresponding TBL_Student_ table. No match, no record.

A proper table structure will help this issue (and the others you are sure to face). Whenever you start numerating field names (e.g. Student1_ID, Student2_ID, STudent3_ID...) its time for a new table to hold all that data. You need a new table to hold whose enrolled in each LessonRecord.

Let's call that new table TBL_Enrollment. This would be its structure:

TBL_Enrollment
Enrollment_ID, autonumber, primary key
StudentLesson_ID, number, foreign key to TBL_LessonRecord
Student_ID, number, foreign key to TBL_Student

That's it, just 3 fields. Then you can add as many (or as few) students as you need to a LessonRecord. Further, it will make querying at a student level, so much simpler.

 

[MNM]

plog,
Thank you for your input. It gives me hope.

Whenever you start numerating field names (e.g. Student1_ID, Student2_ID, STudent3_ID...) its time for a new table to hold all that data.

So, I would need to incorporate this into the basic relationship level (to replace the many TBL_Student references)?
Would this mean the TBL_LessonRecord would have one field which is linked to the new table, which has up to eight fields/controls?
Does anyone know of an online example??

Further, it will make querying at a student level, so much simpler.

One query is for a specific student's past record.
The other is for the 'group' private comment. This can be one(1), up to three(3), or up to eight(8) students (depending on the ContractType).


Could anyone supply a relevant example?
In other words, how is this worked into a query?
TIA
M. McAllister

 

[plog]

I gave you an example of the new table you would need. TBL_LessonRecord already has the field it needs to link to the new table (StudentLEsson_ID), it would just need to be accomodated in the new table.

 

[MNM]

Plog,
I slept on it and I think I know where your going.
I would need to link the FRM_LessonRecordEntry to a query and not the TBL_LessonRecord.
Inside the query would be the eight (8) student references.
I've set up the table & relations, see attached image.
I'll keep you posted.
Mark

 

[jdraw]

You may get some insight and ideas from this free generic Student Roster data model.
Since it is a generic model, it does not necessarily address your specific problem/opportunity/case. It represents the things typically involved in a student-course-class- teacher... database.

Please note that it also identifies if a student attended a particular class.

Good luck with your project.