Calculate field using different field from different table

[k31453]

So i have got this table

Table 1:-- List of available classes

Design view

Class -- Primary key ---text

Subject ----text

Num. Lessons---- Number

Enrolments ---- Number

Max Class Size--- Number

Action -- Calculated because if enrollment is higher then max. class size then its says full otherwise it will tell enrol




Table 2:-- Teacher And Class Income

Class --- text

enrolment --- number

max class size ---- number

income per enrolment ----- currency

incomer per class ----- currency ???



1) BUT WHAT I WANNA DO IS IF I CHANGE ENROLLMENT and max class size VALUE IN TABLE 1 ... I WANT THAT CHANGE IN TABLE 2 ENROLLMENT and max. class size FIELD AS WELL

For e.g. if i change enrollment =25 and max class size = 30 ,,, i want that change in table 2 .......so i want that table 2 should display Enrollment = 25, max class size = 30

I want this things in TABLES not queries so can anybody tell me please what should i do... and i compulsory have to use access... NOT EXCEL

2) then i want that Total Incomer per class (In Table 2) = Enrollment * Income per enrollment so that value should be display on total incomer per class ....

So if i change enrollment value ... then the total income per class value has to change ....

3) i want in table that my total income in all class should display in same table ( Table 2)

I am attaching some files that might can help you.. and i need this done in this 3 days .. so please

 

[bob fitz]

I want this things in TABLES not queries so can anybody tell me please what should i do

Why don't you want to use queries.

 

[Pat Hartman]

You have come across one of the many reasons for not storing calculated values expecially in multiple places - you have to figure out when and how to update the duplicate values so they stay in sync. The best solution is to not store the calculations at all, let alone twice.

Your schema needs a little help.
You need a table with Teacher information.
Teacher and class information is a junction table and should contain the PKs for class and teacher and also the payment per student.

If the application has the detail enrollment data, you should not be storing the enrolment count in the class table. It should be calculated in the query. If it comes to you as a summary, then it is OK where it is.

and i need this done in this 3 days .. so please

Welcome aboard but don't expect us to do your homework for you. And especially don't expect us to do it wrong - you need to do the calculations in queries rather than storing calculated values in tables.

 

[k31453]

Why don't you want to use queries.

how do you do it can tell me step by step guide ..


i attatach access database

View attachment Access.accdb

 

[k31453]

You have come across one of the many reasons for not storing calculated values expecially in multiple places - you have to figure out when and how to update the duplicate values so they stay in sync. The best solution is to not store the calculations at all, let alone twice.

Your schema needs a little help.
You need a table with Teacher information.
Teacher and class information is a junction table and should contain the PKs for class and teacher and also the payment per student.

If the application has the detail enrollment data, you should not be storing the enrolment count in the class table. It should be calculated in the query. If it comes to you as a summary, then it is OK where it is.

Welcome aboard but don't expect us to do your homework for you. And especially don't expect us to do it wrong - you need to do the calculations in queries rather than storing calculated values in tables.

the problem is i cant make a relationship

thats a problem that i cant do that

 

[Alansidman]

Here is a starting point for building queries.

http://www.databasedev.co.uk/queries.html

Alan

 

[Pat Hartman]

What is the error you get when you try to create the relationship? The columns you connect must be of the same data type (and length if they are text) and to enforce RI (which you should always do), the data in the many-side table must be valid. If it is not, you'll need to fix it or delete it.

 

[Pat Hartman]

I looked at the database and the foreign keys are not correct.
Remove SubjectID from teachers
Remove SubjectID from students
SubjectID makes more sense than "class" as the PK for available and "class" makes more sense as the PK for classes provided.
The table you are missing is the one that connects students with classes which is a many-to-many relationship and so needs a "junction" table.
tblStudentClasses:
ClassID (PK field 1, FK to classes provided table0
StudentID (pk field 2, FK to students table)

Hint - it will save you some aggavation later if you rename all your tables and columns now to get rid of the embedded spaces and special characters. You could have trouble with them in VBA and they force you to enclose all offending names in square brackets. Use CamelCase or the_underscore to separate words and make the names more readable.

 

[k31453]

What is the error you get when you try to create the relationship? The columns you connect must be of the same data type (and length if they are text) and to enforce RI (which you should always do), the data in the many-side table must be valid. If it is not, you'll need to fix it or delete it.

so what should i do how do i fix in can u explain in detail please because i m not good with database

 

[k31453]

I looked at the database and the foreign keys are not correct.
Remove SubjectID from teachers
Remove SubjectID from students
SubjectID makes more sense than "class" as the PK for available and "class" makes more sense as the PK for classes provided.
The table you are missing is the one that connects students with classes which is a many-to-many relationship and so needs a "junction" table.
tblStudentClasses:
ClassID (PK field 1, FK to classes provided table0
StudentID (pk field 2, FK to students table)

Hint - it will save you some aggavation later if you rename all your tables and columns now to get rid of the embedded spaces and special characters. You could have trouble with them in VBA and they force you to enclose all offending names in square brackets. Use CamelCase or the_underscore to separate words and make the names more readable.

my task says ;----

a guy want to build online tutoring service
so when any student registered it provoded
with username and next step for them is to they can see the list of available classes.....

so i just want to make a relationship sp can u please at do that me ...
if this replationship correct u can make re
queries but without that i got lot of errors such as


mismatch expression
undetermine the relationship and what is juction table what fields should i include and how can i relate that table to otherrs...

i know my problem is that same subjects class run by many teachers

but in one table i got teacher id as autonumber and ine of them i go number as a datatype ... i cant make that to auto number because same teachers are running two class so i cant make it as a primary key ....

 

[k31453]

=Pat Hartman;1161295
SubjectID makes more sense than "class" as the PK for available
The table you are missing is the one that connects students with classes which is a many-to-many relationship and so needs a "junction" table.
tblStudentClasses:
ClassID (PK field 1, FK to classes provided table0
StudentID (pk field 2, FK to students table)

I know subject Id is make more sense but i can make as pk because there are duplicate value such as there is two sci 8 .. the reason why its duplicate because there are many classes for science 8 ...

and what else should i include in student classes table ??????