Big table relationship problem

[SashaSandy]

i have 6 tables
-tblStudents_info( includes students names,address..etc)
-tblStudent_Classes(includes students classes and streams)


-tblStudent_Subjects(includes students subjets based on a certain combination.)
-tblStudent_Marks(includes student marks in various subjects)
-tblExam_Name(includes examID,Exam_Name)
-tblExam_Period(inclues PeriodID,Period_Name)


problem comes in how do i relate the tables (tblStudent_Marks,tblExam_Name and tblExam_Period)in the case where there are 3 Exams done in each period(3 periods)

[ssanfu]

I've been thinking about your problem (in my spare time ).
Is it possible for you to post the dB with the tables? No data or just a couple of test records?

[mrojas]

It appears to me that your main table, tblStudents_info, must have a primary key, say an auto-number field named StudentID, and all other tables should have a StudentID field which would in turn have a relationship to the main table.

[ItsMe]

ssanfu is onto something. If you already have a lot of data in your tables you will be limited on how you can relate them. Hoping you only have some test records here are some ideas........

It seems like you will need an additional table to enter student exams into. The tables shown here seem to be information that does not change often. Get a new student and you add a record to the info table. Offer a new subject and create a new record in the subjects table. Create a new exam and enter an exam name into thetblExam_Name. A seventh table that stores a new record every time a studentdoes something (takes an exam) will give you the ability to track one studenttaking 20 exams in 4 periods for 3 subjects in 2 classes. This additional table would store the primary key for the StudentID, ExamID, GradeID, and maybe a date stamp you entered (created) the record.

Your tblStudent_Marksmay be an exception. Unless it is a list of grades such as A,B,C, etc. itcould store the StudentID, the semester or quarter, their grade, the subject,etc. Since this table would not be updated every time a test was given, I wouldhave these records in a separate table from the test scores. I would probably build out some other tablesto store lists of quarters, lists of possible grades, etc. to support thistable. You could have a pulldown menu query your list of possible grades andpossible quarters to keep the records' uniformity.

Spend some time to distinguish tables that store information that changes often from tables that store information that does not. The tables that change often will store the primary keys of records from the tables that don't get new records added as often.

[ssanfu]

From the OP's first post in this thread, there is not enough information on the table structure or field types to be able to give a knowledgeable answer.
Is the table structure normalized? What are the table PKs/FKs.

Previous posts have attached pics of a table with field names of "Maths", "Science" and "English" for a record. This is not a normalized structure. What happens when/if another subject has to be added? The table structure has to be modified, the queries have to be modified, the forms have to be modified and the reports have to be modified. The all of the code in the form modules and standard modules has to be reviewed/modified.
This has been named "Committing Spreadsheet". The tables have been designed to resemble a spreadsheet.

problem comes in how do i relate the tables (tblStudent_Marks,tblExam_Name and tblExam_Period)in the case where there are 3 Exams done in each period(3 periods)

With a properly normalized table structure, there is no problem. This question, to me, indicates there is a problem with the table structures.

FWIW, just my thoughts....