473,322 Members | 1,610 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Assigning one or multiple lessons to a student

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
4 2439
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Tony Johansson | last post by:
Hello experts! I have a small program that is using multiple inheritance. There are 4 classes involved I get 4 compile error that I can't figure out why. It's this row which is located in the...
2
by: Bodger | last post by:
Hi, Bearing in mind that I am new to this game, understand very little about SQL and don't work in IT, I was wondering if someone could give me some friendly advice on how to design...
22
by: Matthew Louden | last post by:
I want to know why C# doesnt support multiple inheritance? But why we can inherit multiple interfaces instead? I know this is the rule, but I dont understand why. Can anyone give me some concrete...
4
by: Dave White | last post by:
Hello everyone, I would like to assign a lesson to many students as a group rather than individually. Any suggestions would be appreciated. Dave White
5
by: Hypnotik | last post by:
Hello everyone. I'm working on multiple inheritance program. I have a pretty specific problem that I'm not sure how to overcome. I'm going to try to explain this as simple as possible. There is a...
13
by: Eric IsWhoIAm | last post by:
I have four tables created so far: Courses, Instructors, Courses and Instructors (which shows the Course and Instructor Name fields, but holds their IDs since those are the keys), and Students....
3
by: Sheldon | last post by:
How can this be done and what is the syntax? Thanks, Sheldon Potolsky
11
kordric
by: kordric | last post by:
Hello Im trying to figure out how to do what the title says. Here is what my structure looks like //Structure for the Student Recored struct studentRecord { int studentID; string...
1
by: psyvanz | last post by:
HOW TO MAKE A STUDENT SCHOOL SUBJECTS IN COLLEGE USING Visual Basic 6? WITH FIXED SUBJECTS AND ASSIGNING THE SUBJECT TO A STUDENT USING "DATAENVIRONMENT"? THIS IS IN "FORM1" THIS IS IN...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.