Update expression between the data

[k31453]

Hi i have a Table 1:



That has Enrolment, Max class size

I got another Table

That has a same field

It has Enrolment, max class size

The data of both table is same ..but i need a expression so that

If i change the value in Table 1

Emplyee or Max class size


i want it automatically update to Table 2 fields

which is Enrolment , Max class size

For e.g I change the value of Enrolment in Table 1 to 20 to 15 i want..
that same change in Table 2 Enrolment as well automatically

does anybody know how do u do it ??

[JoeM]

You could create an Update Query that compares the two tables, and updates all records where the values are not equal.
However, you may want to step back and take a look at your database design, as it sounds like your database may not be normalized. Do you have two tables that you want to be identical. Why? What is the purpose of having two tables instead of just one?

[k31453]

My Table 1: List Of Available Classes

student id
Class
Teacehr Id
Subject ID
Subject
Description
Number of lessons
enrolment
max class size
action is Full OR available to enrol

Table 2 : Tutoring Income

Class
Teacher ID
enrolment
max class size
income per class


so i need enrolment, max class size in both table ...

[John_G]

Your database design is not normalized properly (if fact, not at all).

If Table1 is List of Available Classes, then

- Remove StudentID - it has no bearing on a class description
- Keep Subject ID, but create another table for Subjects: SubjectID, Subject, Description
- Remove enrolment ; current enrolment is easily calculated
- ADD a field for Tuition Fee
- If the same class can be given by different teachers, then take TeacherID out of Table1.

Table2 -

- Remove TeacherID - it is already in Table1
- Remove enrolment - it is easily calculated
- Remove Max Class size - it is already in Table1

Actually, I don't think you need Table2 at all - all the data in it can be calculated using queries

You need three other tables:
Student, for keeping data on students
Teacher, for data on the Instructors
Subjects, as I described above

StudentClass, to track which students take which classes.
This one is the most important, because the relationship between Students and Classes is many-to-many: a student can take many classes, and each class can have many students.
Fields in it could be:
StudentID (Required)
Class (Required)
Start date ( because a class could be given more than once)
End data
TeacherID (if it is not in Table1)
Location
.... and any other data you need

Try redesigning your database along these lines, and you should see how you can get the data you need with queries.

John