one-to-many-to-many relationships

[Guest]

I have a problem setting a form that will accept student scores based on
tables as follows:

Table [Roster] contains the Class Code and the student number
(The student number is a primary key). Staff enters the student number in
this table when the student is enrolled. Relationship: one-to-many.

Table [Subjects] contains the Class Code and the subject Codes (The subject
code is the primary key). Staff enters the subject codes for each class.
Relationship: one-to-many.

On the current database staff must type the student number and the subject
for every grade. Because the student number is already there and the subject
is already there too, I want to create a form that will accept the student's
grades without having to retype the student number and the subject for each
grade. relationship many-to-many.

I was able to create the form but is displays the error: : "This record set
is not updatable"

I tried to change the form's properties to: dynaset (incosistent updates)
but when a grade is entered the same grade goes to all the student's in that
subject code.

I hope this makes sense or I maybe way out of my head. I can do any changes
to the database tables that you may suggest.

Thank you for your help.

 

[Guest]

The description of your tables does not include what is related to what...but
I would suggest putting them into a query (your tables) and entering the
fields from your form into the query. Then base your form on the query. See
if that will get you started

 

[Guest]

The first point to emphasize is that Access does not support a "many-to-many"
relationship. If it looks like that is what you need to accomplish, you may
very well need to create an additional junction table using the primary keys
of two other tables as a compound primary key. That table can then be related
to the other two in separate one-to-many relationships.

There is not enough detail to allow a more specific reply, but if the basis
of your "many-to-many" translates to "each student takes many classes and
each class involves many students", this approach should put you on the right
track.

HTH |:>)

 

[Guest]

"Each student takes many classes and each class involves many students" is
the many-to-many relationship that I want to set up. The junction table that
you suggest has to have the student ID and the subject ID Code but they will
have to be retyped (that how it works right now) I wanted to find a way to
create this junction table without having to retype the information.

The tables are linked through the Class Code. One table contains Class Code
and Student ID. The other table contains Class Code and Subject Code.

Once again, I may be way out of me head but thank you for your help.

The first point to emphasize is that Access does not support a "many-to-many"
relationship. If it looks like that is what you need to accomplish, you may
very well need to create an additional junction table using the primary keys
of two other tables as a compound primary key. That table can then be related
to the other two in separate one-to-many relationships.

There is not enough detail to allow a more specific reply, but if the basis
of your "many-to-many" translates to "each student takes many classes and
each class involves many students", this approach should put you on the right
track.

HTH |:>)

I have a problem setting a form that will accept student scores based on
tables as follows:

Table [Roster] contains the Class Code and the student number
(The student number is a primary key). Staff enters the student number in
this table when the student is enrolled. Relationship: one-to-many.

Table [Subjects] contains the Class Code and the subject Codes (The subject
code is the primary key). Staff enters the subject codes for each class.
Relationship: one-to-many.

On the current database staff must type the student number and the subject
for every grade. Because the student number is already there and the subject
is already there too, I want to create a form that will accept the student's
grades without having to retype the student number and the subject for each
grade. relationship many-to-many.

I was able to create the form but is displays the error: : "This record set
is not updatable"

I tried to change the form's properties to: dynaset (incosistent updates)
but when a grade is entered the same grade goes to all the student's in that
subject code.

I hope this makes sense or I maybe way out of my head. I can do any changes
to the database tables that you may suggest.

Thank you for your help.

 

[Larry Daugherty]

The Student-Teacher-Class-Subject-Roster-Section-Assignment-Grade and
sometimes Attendance and sometimes Bus Number paradigm seems to come up in
one of the Access newsgroups fairly often. I have the feeling that I'd
probably agree with your definition of the entities you want to use but
would probably use and relate them differently.

Given the selected bits of information you've given, I can't piece together
what you have done with your tables. Could you post back and include the
name of each table and the name and datatype of each field in it. With that
information we might be better able to help.

Some things to think about:

One thing for sure, when you have your tables designed and related properly
and you have supporting forms and subforms you should never need to enter
Foreign Keys manually.

Once you have identified your entities and created tables to hold their
records it is important in the early going is to decide who is to use your
application and for what purposes. For example, if it's an adult enrolling
kids or an older student self-enrolling; Find your Name, find a Subject,
Find your Section/Roster;; Find next Subject, Find next Section, etc.
Everything should be point and click. When they're done they should be able
to ask for a printed report of their enrollment that will show every
Subject, section, teacher, meeting times and days, and any notes about that
section.

It will be up to you, the application developer, to build in all of the of
bullet-proofing and User Friendliness you can. Section already full?
Pre-requisites met? Schedule conflict?

HTH
--
-Larry-
--

"Each student takes many classes and each class involves many students" is
the many-to-many relationship that I want to set up. The junction table that
you suggest has to have the student ID and the subject ID Code but they will
have to be retyped (that how it works right now) I wanted to find a way to
create this junction table without having to retype the information.

The tables are linked through the Class Code. One table contains Class Code
and Student ID. The other table contains Class Code and Subject Code.

Once again, I may be way out of me head but thank you for your help.

The first point to emphasize is that Access does not support a "many-to-many"
relationship. If it looks like that is what you need to accomplish, you may
very well need to create an additional junction table using the primary keys
of two other tables as a compound primary key. That table can then be related
to the other two in separate one-to-many relationships.

There is not enough detail to allow a more specific reply, but if the basis
of your "many-to-many" translates to "each student takes many classes and
each class involves many students", this approach should put you on the right
track.

HTH |:>)

I have a problem setting a form that will accept student scores based on
tables as follows:

Table [Roster] contains the Class Code and the student number
(The student number is a primary key). Staff enters the student number in
this table when the student is enrolled. Relationship: one-to-many.

Table [Subjects] contains the Class Code and the subject Codes (The subject
code is the primary key). Staff enters the subject codes for each class.
Relationship: one-to-many.

On the current database staff must type the student number and the subject
for every grade. Because the student number is already there and the subject
is already there too, I want to create a form that will accept the student's
grades without having to retype the student number and the subject for each
grade. relationship many-to-many.

I was able to create the form but is displays the error: : "This record set
is not updatable"

I tried to change the form's properties to: dynaset (incosistent updates)
but when a grade is entered the same grade goes to all the student's in that
subject code.

I hope this makes sense or I maybe way out of my head. I can do any changes
to the database tables that you may suggest.

Thank you for your help.

 

[Guest]

Sorry for the delay on my response but there is to much to do...
These are the tables involved:
Table Name: Student Data
Field Name

The Student-Teacher-Class-Subject-Roster-Section-Assignment-Grade and
sometimes Attendance and sometimes Bus Number paradigm seems to come up in
one of the Access newsgroups fairly often. I have the feeling that I'd
probably agree with your definition of the entities you want to use but
would probably use and relate them differently.

Given the selected bits of information you've given, I can't piece together
what you have done with your tables. Could you post back and include the
name of each table and the name and datatype of each field in it. With that
information we might be better able to help.

Some things to think about:

One thing for sure, when you have your tables designed and related properly
and you have supporting forms and subforms you should never need to enter
Foreign Keys manually.

Once you have identified your entities and created tables to hold their
records it is important in the early going is to decide who is to use your
application and for what purposes. For example, if it's an adult enrolling
kids or an older student self-enrolling; Find your Name, find a Subject,
Find your Section/Roster;; Find next Subject, Find next Section, etc.
Everything should be point and click. When they're done they should be able
to ask for a printed report of their enrollment that will show every
Subject, section, teacher, meeting times and days, and any notes about that
section.

It will be up to you, the application developer, to build in all of the of
bullet-proofing and User Friendliness you can. Section already full?
Pre-requisites met? Schedule conflict?

HTH
--
-Larry-
--

"Each student takes many classes and each class involves many students" is
the many-to-many relationship that I want to set up. The junction table that
you suggest has to have the student ID and the subject ID Code but they will
have to be retyped (that how it works right now) I wanted to find a way to
create this junction table without having to retype the information.

The tables are linked through the Class Code. One table contains Class Code
and Student ID. The other table contains Class Code and Subject Code.

Once again, I may be way out of me head but thank you for your help.

The first point to emphasize is that Access does not support a "many-to-many"
relationship. If it looks like that is what you need to accomplish, you may
very well need to create an additional junction table using the primary keys
of two other tables as a compound primary key. That table can then be related
to the other two in separate one-to-many relationships.

There is not enough detail to allow a more specific reply, but if the basis
of your "many-to-many" translates to "each student takes many classes and
each class involves many students", this approach should put you on the right
track.

HTH |:>)

:

I have a problem setting a form that will accept student scores based on
tables as follows:

Table [Roster] contains the Class Code and the student number
(The student number is a primary key). Staff enters the student number in
this table when the student is enrolled. Relationship: one-to-many.

Table [Subjects] contains the Class Code and the subject Codes (The subject
code is the primary key). Staff enters the subject codes for each class.
Relationship: one-to-many.

On the current database staff must type the student number and the subject
for every grade. Because the student number is already there and the subject
is already there too, I want to create a form that will accept the student's
grades without having to retype the student number and the subject for each
grade. relationship many-to-many.

I was able to create the form but is displays the error: : "This record set
is not updatable"

I tried to change the form's properties to: dynaset (incosistent updates)
but when a grade is entered the same grade goes to all the student's in that
subject code.

I hope this makes sense or I maybe way out of my head. I can do any changes
to the database tables that you may suggest.

Thank you for your help.

 

[Guest]

Sorry I click on the wrong button! Again these are the field involved in my
problem. Each table has too many fields to be listed.

Table Name: Student Data
Field Name Field Type
Student ID Autonumber (primary key)
Class Code Text

Table Name: Subject Information
Field Name Field Type
Subject ID Text (Primary Key)
Subject Description Text

Table Name: Class Information
Field Name Field Type
Class Code Text (Primary Key)
Class Description Text

Table Name: Grades per Class
Field Name Field Type
Class Code Lookup -lookup Class code in class information table
Subject ID Lookup -lookup Subject ID in Subject info. Table
Grade Lookup -lookup the grade in a grading table

This last table is the one used for the data entry form. Class Code is the
field that links the student data with the Subject IDs through the last
table. As I mentioned before, the database works but staff must entered the
Student ID and the Subject ID for each student and obviously the grade.

I would like to create a form where only the grade should be entered because
I already have the class code, the student ID and the subject ID.

I hope these tables may help you to see what my problem is. Thank you very
much for your help.

The Student-Teacher-Class-Subject-Roster-Section-Assignment-Grade and
sometimes Attendance and sometimes Bus Number paradigm seems to come up in
one of the Access newsgroups fairly often. I have the feeling that I'd
probably agree with your definition of the entities you want to use but
would probably use and relate them differently.

Given the selected bits of information you've given, I can't piece together
what you have done with your tables. Could you post back and include the
name of each table and the name and datatype of each field in it. With that
information we might be better able to help.

Some things to think about:

One thing for sure, when you have your tables designed and related properly
and you have supporting forms and subforms you should never need to enter
Foreign Keys manually.

Once you have identified your entities and created tables to hold their
records it is important in the early going is to decide who is to use your
application and for what purposes. For example, if it's an adult enrolling
kids or an older student self-enrolling; Find your Name, find a Subject,
Find your Section/Roster;; Find next Subject, Find next Section, etc.
Everything should be point and click. When they're done they should be able
to ask for a printed report of their enrollment that will show every
Subject, section, teacher, meeting times and days, and any notes about that
section.

It will be up to you, the application developer, to build in all of the of
bullet-proofing and User Friendliness you can. Section already full?
Pre-requisites met? Schedule conflict?

HTH
--
-Larry-
--

"Each student takes many classes and each class involves many students" is
the many-to-many relationship that I want to set up. The junction table that
you suggest has to have the student ID and the subject ID Code but they will
have to be retyped (that how it works right now) I wanted to find a way to
create this junction table without having to retype the information.

The tables are linked through the Class Code. One table contains Class Code
and Student ID. The other table contains Class Code and Subject Code.

Once again, I may be way out of me head but thank you for your help.

The first point to emphasize is that Access does not support a "many-to-many"
relationship. If it looks like that is what you need to accomplish, you may
very well need to create an additional junction table using the primary keys
of two other tables as a compound primary key. That table can then be related
to the other two in separate one-to-many relationships.

There is not enough detail to allow a more specific reply, but if the basis
of your "many-to-many" translates to "each student takes many classes and
each class involves many students", this approach should put you on the right
track.

HTH |:>)

:

I have a problem setting a form that will accept student scores based on
tables as follows:

Table [Roster] contains the Class Code and the student number
(The student number is a primary key). Staff enters the student number in
this table when the student is enrolled. Relationship: one-to-many.

Table [Subjects] contains the Class Code and the subject Codes (The subject
code is the primary key). Staff enters the subject codes for each class.
Relationship: one-to-many.

On the current database staff must type the student number and the subject
for every grade. Because the student number is already there and the subject
is already there too, I want to create a form that will accept the student's
grades without having to retype the student number and the subject for each
grade. relationship many-to-many.

I was able to create the form but is displays the error: : "This record set
is not updatable"

I tried to change the form's properties to: dynaset (incosistent updates)
but when a grade is entered the same grade goes to all the student's in that
subject code.

I hope this makes sense or I maybe way out of my head. I can do any changes
to the database tables that you may suggest.

Thank you for your help.