Updates

[campgirl1989]

I have a student table where the student's name is input, a classes table where all the available classes are listed. I created a join table callled studentsbyclasses where I can easily input a students entire schedule.

Each class has its own seperate table and there are about 50 seperate tables for classes. Each table has a field for scouts names that only pulls up students in that specific class.

What's the best way to setup an automatic update so whatever information is in the studentsbyclasses table goes into each of the classes tables?

 

[jzwp22]

I'm a little confused by the following two statements:

...a classes table where all the available classes...
and

...50 seperate tables for classes...

I see a need for a table for students, a table for available classes and your junction table but what are the 50 separate tables? Could you explain further?

 

[campgirl1989]

Each class has a different set of requirements unlike any of the others so I created a seperate table for each.

 

[ajetrumpet]

Each class has its own seperate table and there are about 50 seperate tables for classes. Each table has a field for scouts names that only pulls up students in that specific class.

there is nothing wrong with this setup CampGirl, but any experienced Access developer would tell you that the data is not normalized. I would suggest importing all of your classes tables into Excel, running a consolidation function to create one table that is related to a "classes" table, and then import the result back in. 1 table for each class will probably get you in trouble down the line. Or, I suppose, you could do the consolidation inside Access itself.

 

[jzwp22]

Forgive me but I'm still a little confused and it might be because of the terminology. You have available classes and you have a combination of a class and its students which also seems to be called a class. I assume that the available classes can be called courses to aid in distinguishing the terminology. (Please correct me if I am wrong).

tblCourses
-pkCourseID primary key, autonumber
-txtCouseName


tblStudents
-pkStudentID primary key, autonumber
-txtFName
-txtLName

tblClasses
-pkClassID primary key, autonumber
-fkCourseID foreign key to tblCourses
-fkStudentID foreign key to tblStudents

Now as to your requirements, if there are multiple requirements for a class this describes a one (class) to many (requirements) relationship. Perhaps the following structure could substitute for your 50 separate tables

tblClassRequirements
-pkClassReqID primary key, autonumber
-fkClassID foreign key to tblClasses
-txtRequirement


Now if some requirements can be shared among some classes, you can save yourself some effort by creating a table to hold all requirements and then tying them to tblClassRequirements via a foreign key. That structure would look like this:

tblRequirements
-pkReqID primary key, autonumber
-txtRequirement

tblClassRequirements
-pkClassReqID primary key, autonumber
-fkClassID foreign key to tblClasses
-fkReqID foreign key to tblRequirements


In thinking about your application, I started to wonder why would a course/student combination have certain requirements. Let me explain, let's say you have a class taking Course A that is comprised of 5 students. You also have another class taking Course A that is comprised of 10 students. I would think that the requirements are independent of the number of students and that would suggest that the requirements are only dependent on the course being taken. That would require a different structure than what I proposed above. Am I out in left field in my thinking? Could you explain your application further?

 

[campgirl1989]

there is nothing wrong with this setup CampGirl, but any experienced Access developer would tell you that the data is not normalized. I would suggest importing all of your classes tables into Excel, running a consolidation function to create one table that is related to a "classes" table, and then import the result back in. 1 table for each class will probably get you in trouble down the line. Or, I suppose, you could do the consolidation inside Access itself.

Yes! I will be doing that in the future, but I'm at a point where I have to work with what I have because the system is already in place so its too late to go back and normalize it. But like I said, I'll make this change at some point in the future.

 

[campgirl1989]

I would think that the requirements are independent of the number of students and that would suggest that the requirements are only dependent on the course being taken. That would require a different structure than what I proposed above. Am I out in left field in my thinking? Could you explain your application further?

The requirements are independent of the number of students in the class.

tblStudents
-StudentsID primary key, autonumber
-txtStudentName

tblClasses
-ClassesID primary key, autonumber
-txtClassName

tblStudentsbyClasses
-StudentsbyClassesID primary key, autonumber
-ClassesID (lookup column that shows classname in datasheet view)
-StudentsID (lookup column that shows studentname in datasheet view)

tblArt/Sculpture (This is an example of a class table)
-Art/SculptureID primary key, autonumber
-StudentsInClass, number, (this is a lookup column that shows only students with classesID of 12, for artsculputre)

I would like the StudentsInClass field to be fully automated, so any additions, deletions, and updates in StudentsbyClasses table are reflected in this field.

 

[jzwp22]

If the requirements are independent of the number of students then should the requirements should be tied to the course not the class?

tblArt/Sculpture (This is an example of a class table)
-Art/SculptureID primary key, autonumber
-StudentsInClass, number, (this is a lookup column that shows only students with classesID of 12, for artsculputre)

This sounds like a calculated value, the number of students in a class. This value should not be stored. You can calculate it with 1 totals query.

 

[campgirl1989]

StudentsInClass shows the names of the students in the course. The number/amount of students is unimportant. I need the names to show up automatically.

 

[jzwp22]

You would still just use a query, there is no need to duplicate data that is already entered in the database. If you have a database that can be posted (any sensitive data removed or changed), we can help you design the queries.