If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
one-to-many-to-many relationships
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. |
#2
|
|||
|
|||
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 "Ricoy-Chicago" wrote: 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. |
#3
|
|||
|
|||
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 |:) "Ricoy-Chicago" wrote: 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. |
#4
|
|||
|
|||
"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. "CyberTaz" wrote: 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 |:) "Ricoy-Chicago" wrote: 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. |
#5
|
|||
|
|||
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- -- "Ricoy-Chicago" wrote in message ... "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. "CyberTaz" wrote: 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 |:) "Ricoy-Chicago" wrote: 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. |
#6
|
|||
|
|||
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 "Larry Daugherty" wrote: 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- -- "Ricoy-Chicago" wrote in message ... "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. "CyberTaz" wrote: 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 |:) "Ricoy-Chicago" wrote: 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. |
#7
|
|||
|
|||
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. "Larry Daugherty" wrote: 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- -- "Ricoy-Chicago" wrote in message ... "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. "CyberTaz" wrote: 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 |:) "Ricoy-Chicago" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mixed up with Relationships..help! | KrazyRed | New Users | 3 | January 26th, 2005 05:03 AM |
Deleting Relationships... | Kojones | General Discussion | 3 | December 17th, 2004 11:46 PM |
Relationships getting redefined? | raylitalo | General Discussion | 3 | December 15th, 2004 08:31 PM |
Saving Relationships | Leon | Database Design | 2 | November 2nd, 2004 10:41 AM |
Not seeing all relationships in layout window | jettabug | General Discussion | 3 | June 18th, 2004 05:42 PM |