Duplicate a table and toggle the relationship in a query!

[Jeff]

Hi All,

I have a form with student details of the course they are studying. The
form has an ObjectSource of a querry based on two tables. One of the
tables' information will change each semester.

The problem is that every semester, some of the data changes (course
being studied details). This info is used to make the payment books. The
payment books need to be made in advance of the next semester, so we
need to work with two sets of similar data - semester1 and semester2.

What I'd like:
The user (our secretary) can click on a button and the course-table is
copied and renamed. The yes/no field on the main info table set to 'no'
for all students. Then make a button that toggles the relationship in
the querry between 'maintable and coursedetils-1' or 'maintable and
coursedetails-2'.

We have to toggle the relationship in the query because many reports and
forms use the data from this query and creating a new query with a
different name would destroy those data relationships.

Is this possible?

New table is ready for the next semester - only have to input the new
semester data as each student enroles for the new semester.

Any ideas really appreciated as this aspect is very important and I
haven't got a clue how to do this!

Thanks a lot

Jeff

 

[Allen Browne]

The problem is with your table design. You don't need bucket loads of
tables, with different ones for for each course and semester.

Instead, set up a relational design between your entities:
- One Subject (course choices) is offered many times (in different
semesters).
- One Class (course instance in a semester) has many enrolments (students).
- One Student enrols in many classes.

So, the tables you need are (at minimum):

Student table: one record for each student, with these fields:
StudentID AutoNum
Surname Text
...

Subject table: one record for each subject e.g. Math101.
SubjectID AutoNum
SubjectName Text
...

Class: One record for each instance of a subject:
ClassID AutoNum
SubjectID foreign key to Subject.SubjectID
StartDate when this subject was run
TeacherID foreign key to Teacher.TeacherID

Enrol: One record for each student in a class:
EnrolID AutoNum
ClassID foreign key to Class.ClassID
StudentID foreign key to Student.StudentID

Once you get that set up, you will be able to continue working over the
years without having to create more tables. You will also be able to query
back to any subject, in any semester, or results for any student in any
class, all using the same query.

 

[Allen Browne]

The problem is with your table design. You don't need bucket loads of
tables, with different ones for for each course and semester.

Instead, set up a relational design between your entities:
- One Subject (course choices) is offered many times (in different
semesters).
- One Class (course instance in a semester) has many enrolments (students).
- One Student enrols in many classes.

So, the tables you need are (at minimum):

Student table: one record for each student, with these fields:
StudentID AutoNum
Surname Text
...

Subject table: one record for each subject e.g. Math101.
SubjectID AutoNum
SubjectName Text
...

Class: One record for each instance of a subject:
ClassID AutoNum
SubjectID foreign key to Subject.SubjectID
StartDate when this subject was run
TeacherID foreign key to Teacher.TeacherID

Enrol: One record for each student in a class:
EnrolID AutoNum
ClassID foreign key to Class.ClassID
StudentID foreign key to Student.StudentID

Once you get that set up, you will be able to continue working over the
years without having to create more tables. You will also be able to query
back to any subject, in any semester, or results for any student in any
class, all using the same query.

 

[John Nurick]

Hi Jeff,

It would be much simpler to add two fields to the course table, one for
CourseYear and one for Semester. Then you'd have all the course data in
one table, and instead of having to do complicated stuff to switch
queries from using one table to using another (and different tables
again next year), all you need do is add to the WHERE condition of your
queries, e.g.

WHERE Course.CourseYear = 2005 AND Course.Semester = 2

 

[Jeff]

Hi Jeff,

It would be much simpler to add two fields to the course table, one for
CourseYear and one for Semester. Then you'd have all the course data in
one table, and instead of having to do complicated stuff to switch
queries from using one table to using another (and different tables
again next year), all you need do is add to the WHERE condition of your
queries, e.g.

WHERE Course.CourseYear = 2005 AND Course.Semester = 2

Thanks John,

Wonderful. I added the fields and updated the WHERE clause. Problem
solved. Couldn't see the tree because of the forest!

Thanks a lot,

Jeff