I am working on a solution for managing school information. I have a table called "Classes" containing a 'Class_number' field (ex. 604) and a table called "Subjects" containing a 'Subject_Code' field (ex. MATH). I would like to calculate a 'Course' which would be a 'Class_Number' plus a 'Subject_Code' (ex. 604 MATH). If there are 12 different subjects (MATH, SCI, SS, etc.) then there would be 12 different 'courses' for 604. I was able to accomplish this with a script, looping through all the class records, but this becomes problematic when certain class numbers change or subject codes are added or deleted. I don't care if the 'Course' field is in an existing table or in a new table, a repeating or non-repeating field, but I would like the solution to be automatic (sans script). Does anyone have an idea? All input is valued and appreciated.

Each record in the Subjects table would have Course, calculated, unstored =

Class_number & " " & Subject_Code, where the foreign key in Subjects is Class_number and the primary key in Classes is Class_number. The primary and foreign key would be linked in the Relationships Graph.

Thank you for the reply. Since I am self-taught, my understanding of foreign and primary keys is very sketchy. Could you elaborate on your solution? I appreciate the help.

Each table in a solution must have a key--a unique field for each record in that table. The primary key is a field in the original table. The foreign key is a field in another table which relates it to the original table. A join table will have two foreign keys.

the primary key in Classes is Class_number

I feel the urge to caution here with the following rules for primary keys - which ought at least some kind of consideration:

1) Primary Key Fields Should Never Be Empty

2) Primary Key Fields Should Have Unique, If Not Serialized Data In them

3) Primary Key Fields Information Should Never Change

So isn't there a chance that class 604 should be something that users could fiddle with? Strictly speaking shouldn't the foreign key be any part of something human readable.

--sd

Thank you again for your help. I think I actually understood the concept of a key, I'm just not used to the terminology.

SD- I do follow your 3 rules for keys, but I don't understand the last staement in your post. Could you elaborate? And just so you know, each class has a unique number.

Transpower- The only part of your explanation I don't understand is how can my Subjects table have a Class_number field if none of the Subjects are related to any Classes until after I solve my issue.

Somewhat related question- Is there a way to move/copy one field's data(All records) from one table into the repetitions of one field in another table?

Thanks again for the input.

SD- I do follow your 3 rules for keys, but I don't understand the last staement in your post. Could you elaborate? And just so you know, each class has a unique number.

...It's rule #2 that makes your users not being able to touch the key, only the name class 604 name, say it was a woman who merries herself to a new surname this isn't going to change her SSN, well thats how it works here abouts.

Then the last question: Since the point in relations is to ensure integrity, isn't any data moved - just showed in new ways via the relations it goes into. By moving is the issue how to sync the updates?

--sd