By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Assigning one or multiple lessons to a student

P: n/a
Hello Everyone,

I have created two tables to track my students' lessons.

Each student is responsible for most, but not all. of the lessons.

I've tried a junction table but I can't figure out an easy way to
assign one or multiple lessons to a student.

tbl_Students
Last - text - 50
First - text - 50
Class - text - 6
Period - byte - 1

tbl_Lessons
LessonNbr - integer (no dupes)
LessonType - text - 1
LessonDescr - text - 50
DueDate - shortdate - 8
Note - text - 50
Grade - Integer

Any suggestions would be appreciated.

Dave
Feb 9 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Dave White wrote:
>I have created two tables to track my students' lessons.

Each student is responsible for most, but not all. of the lessons.

I've tried a junction table but I can't figure out an easy way to
assign one or multiple lessons to a student.

tbl_Students
Last - text - 50
First - text - 50
Class - text - 6
Period - byte - 1

tbl_Lessons
LessonNbr - integer (no dupes)
LessonType - text - 1
LessonDescr - text - 50
DueDate - shortdate - 8
Note - text - 50
Grade - Integer

A common approach is to use a main form for the student data
amd a continuous subform for the junction table. The
subform's Link Master/Child Fields properties are set to the
studentID field. The subform would have a combo box with
RowSource to the Lessons table.

--
Marsh
Feb 9 '07 #2

P: n/a
On Fri, 09 Feb 2007 16:39:52 -0600, Marshall Barton
<ma*********@wowway.comwrote:
>Dave White wrote:
>>I have created two tables to track my students' lessons.

Each student is responsible for most, but not all. of the lessons.

I've tried a junction table but I can't figure out an easy way to
assign one or multiple lessons to a student.

A common approach is to use a main form for the student data
amd a continuous subform for the junction table. The
subform's Link Master/Child Fields properties are set to the
studentID field. The subform would have a combo box with
RowSource to the Lessons table.

Thanks Marsh for the above input and a great learning experience. I
still, however, have a lot to learn about Access.
--------------------------------------------------
I set up the the form/subform as follows:

My main form is the Student Table

A Junction table subform was set up which contains its own ID plus all
the fields from the Student and Lessons Tables
I made it a continuous form and set the link Master/Child Fields'
properties each to the StudentID Field.
I added a combo box pointing to the Lessons Table.

(1) If I change the StudentID in the Main Form Student Table it
updates only the StudentID in the Junction Subform but "not" Last
Name, First Name, Etc.

(2) If I use the Combo Box in the Junction Form to add a Lesson it
only changes the Lesson ID but "not" Lesson Type, Lesson Description,
etc.

How can I resolve (1) and (2) to enter all the fields for Students and
Lessons to update my junction subform/junction table?

Thanks in advance,
Dave
Feb 10 '07 #3

P: n/a
Dave White wrote:
>On Fri, 09 Feb 2007 16:39:52 -0600, Marshall Barton wrote:
>>Dave White wrote:
>>>I have created two tables to track my students' lessons.

Each student is responsible for most, but not all. of the lessons.

I've tried a junction table but I can't figure out an easy way to
assign one or multiple lessons to a student.

A common approach is to use a main form for the student data
amd a continuous subform for the junction table. The
subform's Link Master/Child Fields properties are set to the
studentID field. The subform would have a combo box with
RowSource to the Lessons table.

Thanks Marsh for the above input and a great learning experience. I
still, however, have a lot to learn about Access.
--------------------------------------------------
I set up the the form/subform as follows:

My main form is the Student Table

A Junction table subform was set up which contains its own ID plus all
the fields from the Student and Lessons Tables
I made it a continuous form and set the link Master/Child Fields'
properties each to the StudentID Field.
I added a combo box pointing to the Lessons Table.

(1) If I change the StudentID in the Main Form Student Table it
updates only the StudentID in the Junction Subform but "not" Last
Name, First Name, Etc.

(2) If I use the Combo Box in the Junction Form to add a Lesson it
only changes the Lesson ID but "not" Lesson Type, Lesson Description,
etc.

How can I resolve (1) and (2) to enter all the fields for Students and
Lessons to update my junction subform/junction table?

The junction table would only have the StudentID field as a
foreign key to the students table, the LessonID field as a
foreign key to the Lessons table, and any fields necessary
to describe this particular student's lesson such as the
lesson date. The other fields in the Students tables would
be redundant in the junction table and not be there. Nor
should they be displayed on the subform (they're already
displayed on the main form). The same is true for the data
in the Lessons table.

Typically, the combo box would display the CourseID or
course name. The combo box (and maybe the course date) is
(are) the only visible control(s) on the subform. Don't
forget that the combo box can display any number of fields
in its drop down list. If it is necessary to display more
data from the courses table, then you can use text boxes
with an expression like =combobox.Column(x) to display any
field in the combo box's RowSource query.

--
Marsh
Feb 11 '07 #4

P: n/a
On Sat, 10 Feb 2007 17:53:51 -0600, Marshall Barton
<ma*********@wowway.comwrote:

>>
(1) If I change the StudentID in the Main Form Student Table it
updates only the StudentID in the Junction Subform but "not" Last
Name, First Name, Etc.

(2) If I use the Combo Box in the Junction Form to add a Lesson it
only changes the Lesson ID but "not" Lesson Type, Lesson Description,
etc.

How can I resolve (1) and (2) to enter all the fields for Students and
Lessons to update my junction subform/junction table?


The junction table would only have the StudentID field as a
foreign key to the students table, the LessonID field as a
foreign key to the Lessons table, and any fields necessary
to describe this particular student's lesson such as the
lesson date. The other fields in the Students tables would
be redundant in the junction table and not be there. Nor
should they be displayed on the subform (they're already
displayed on the main form). The same is true for the data
in the Lessons table.

Typically, the combo box would display the CourseID or
course name. The combo box (and maybe the course date) is
(are) the only visible control(s) on the subform. Don't
forget that the combo box can display any number of fields
in its drop down list. If it is necessary to display more
data from the courses table, then you can use text boxes
with an expression like =combobox.Column(x) to display any
field in the combo box's RowSource query.
Marsh,

Thank you for all your help. The entire process was a great learning
experience for me. I have one more piece of the puzzle that I can't
figure out, but it probably makes more sense to start a new post.

Thank again,
Dave

Feb 11 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.