Confused on this table relationship.

[calvinle]

Hi guys,

I am making 3 tables but I am a bit confused in how to setup the relationship between them.

Here are my table:
tblStudent:
StudentID - PK
Student Number
Full Name

tblSubject:
SubjectID - PK
StudentID
Subject Type
ErrorID???

tblError:
ErrorID - PK
SubjectID
Subject Type??
Error Reason

My scenarios are:
There are list of student. (Student ID)
Each student have different homework (SubjectID) which can be : Math, Physic, Science (Subject Type).

Now, my problem is the error.. Is the error depends on the SubjectID ? or the error depends on ErrorID??

Should I link the :
tblSubject:[SubjectID] ->tblError:[SubjectID] ??
or
tblSubject:[ErrorID] ->tblError:[ErrorID] ??

Please help thanks.

 

[spikepl]

Until you actually explain to outsiders what "error" is, we can only guess so I guess position of planets and feng shui.

 

[plog]

Agree with spike--you've not explained what an error is only told us that you don't know if it relates to a student or a subject. With that said, I do I see an error in your tables which might lead you to the right path.

I believe the relationship between subjects and students is incorrect. As it is now you have a 1 to many relationship between subjects and students. In your database a subject can have many students, but a student can only have 1 subject. Is that correct? Can't a student have multiple subjects?

If they can, then you need a junction table (https://en.wikipedia.org/wiki/Junction_table). That type of table establishes a many-many relationship between 2 tables. With a junction table many students can take many subjects and many subjects can have many students. If that is the case, then you would remove StudentID and ErrorID from tblSubjects and create a new table (the junction) using this layout:

tblEnrollment
Enrollment_ID, autonumber, primary key
ID_Student, number, foreign key to tblStudents
ID_Subject, number, foreign key to tblSubjects


With that table multiple students can be in multiple subjects and vice versa. I think that table might help you realize where this Error should go. Possible it might be linked to the Enrollment ID or it might even be in tblEnrollment.

Again, though, you've said nothing about what an Error actually is.

 

[calvinle]

The situation is like:
There can be many student. Each student are given honework to do. Each homework are different so they are SubjectId. Each homework can be type of subject such as Math, Physic, Science. For each homework, for sure, they can make mistake. Each evaluation are different, so thats why there are error id. The evaluation for math will be like:
Error in equation
Error in calculation

The error in science can be:
Theory Error
Experimentation Error

The error in Physic can be:
Missing variable in formula
Missing application of theory vs fact.
Knowledge

Thats the error.

I would say, the error will need their own ID, becuz they depends on each Work Type.??

 

[plog]

Sounds like that guy in the 3rd post nailed it. You should use the table structure identified there.

Then you would have 2 more tables. One for errors like so:

tblErrors
Error_ID, auto number, primary key
Subject_ID, number, foreign key to tblSubects
Error_Description

Then another junction table between tblerrors and tblEnrollment

TblErrorsMade
ErrorsMade_ID, auto number, primary key
ID_Enrollment, number, foreign key to tblEnrollment
ID_Error, number, foreign key to tblErrors

 

[calvinle]

For the subject, there is only Math, Physic and Science as a choice. Can I just change them to a Combo box with list of "Math", "Physic" or "Science" or do I need a table for that?

 

[calvinle]

I am getting more confused, because I don't think that I will need that much table do I?

1/ Each student has a unique student number. It has to be text as it could be "P998".
So for this, I will have:
tblStudent:
[StudentID-PK]
[Student Number]
[Full Name]

2/We can assign for each student many CaseID which can be either Math, Physic or Science. So let's say, for the Student 1, I can give him 2 cases of Math, but with they have a different Case ID. I can assign 5 homework of Physic and they have different ID. So which mean, the CaseID has to be unique.
tblWork:
[CaseID-PK:Number]
[StudentID:Number]
[SubjectType: Combobox "Math", "Physic", "Science"] *Do I really have to create a table just to have them listed in 3 records? rather can I just have a combo box enumarting them?
[Date]
etc.

3/ Each of their work, under CaseID(since they are unique), they can make mistake. But the error type are not related to the case ID, but rather related to the SubjectType. So, that's why I need to create the SubjectType in a separate table to link the error to it right?
tblSubject:
[SubjectIDK]
[Subject Type:Text]

4/ Each of the error are different depending on the subject, so in this case, I wil need to create 3 diferents table for each of the Subject?
tblErrorMath:
[ErrorID-PK]
[SubjectID]??
[Error_Type]

tblErrorPhysic:
[ErrorID-PK]
[SubjectID]??
[Error_Type]

tblErrorScience:
[ErrorID-PK]
[SubjectID]??
[Error_Type]

Does this work??

See:


Thanks for your help.

 

[plog]

The real question is do you have more data to go with Subjects? Will you want to store the teacher? First class date? Level? If you have any data that needs to be stored with the Subject then you need a Subject table. If not you would be fine to do it in a combo box like you asked.

 

[calvinle]

No. There is no more data need for the Subject. However, if I dont create a tblSubject then how am I suppose to relate the error type to the subject?

 

[plog]

Now you've introduced a new entity--work. I believe the structure I have been describing is the correct one and this new table would exist between the Enrollment and Errors tables I laid out before.

Under no circumstances would you have a table for each subject as your screenshot shows. These would be your tables:

Students
Enrollment
Work
Errors

Subjects have been eliminated and became a drop down in Enrollment.

 

[calvinle]

But my concern now is, if I eliminate that subject table, how can I link the error type to the subject? So the subject shouldn't be eliminated right?

Thanks

 

[plog]

You had previous mentioned making the subject just a drop down in the Enrollment table.

 

[jdraw]

Calvinle,

I suggest you describe to readers exactly what you are trying to do in plain English, business terms --no Access/database jargon. Based on the thread so far, I don't think you have a clear picture (or plan) of what you are trying to or are being asked to do.
From experience, get a clear understanding of the requirement; do some analysis; clarify any issues; make a plan.... then work on the solution.

Good luck.

 

[calvinle]

I already explained the situation/scenarios of the work in the topic above.

In a class, there are many student with each a student number.
Each student are assigned different case id to work on.
Each case id are different.
The case id can be subject to: Math, Physic, Science for now, but later maybe also Chemistry, etc.
For every case id that they work on, they can do error.
The error depends on the subject.
So will need to elaborate the error as per the subject.(Math: Knowledge, Calculation, Equation. Physic: Theor, Application, etc..)

Thats it.

 

[jdraw]

Sorry Calvin but that doesn't help me. If it is clear to you , then by all means proceed by asking others. If English is not your native language, then describe it clearly as you would to a colleague and then(google) translate to English and repost.

If your above description is the best you can do, I think you're in trouble as others have suggested.

Homework seems to have been dropped since your original post???

Good luck with your project.

 

[calvinle]

Becuz I tried to shortcut things so I add unecessary things to the old topic. This one is the real description.

 

[calvinle]

Basically,

I have a group of student.
I am giving them homework to do. On the table, I have prepare many folder with an Folder # on it.
My student will pick those folder to work on.
They have to entered them onto a tracking spreadsheet as follow:
[Student ID] [Date] [Case ID] [Subject]

I will then evaluate their homework.
For each rows, I will give 100%, or x %.
If I give less than 100%, I will need to provide the explanation of the error.

The whole process was on Excel, however, because the lack of using the excel all at once, I find maybe it's better to use access to accessing, controlling, reporting, etc.
I did shared the excel (in case you ask) but it's really slow somehow, especially when the file is on LAN.
And I had to create different tab worksheet for each Subject as the Error explanation for each subject are different.

Hope it clarify a bit of this project.

 

[Sean O'Halloran]

Calvinle,

I think you lost your forum audience because you did not explain what you are trying to do in simple language. Here is what I think they were looking for: "I have students who attend several different classes. In each class the student has homework assigned. I will correct the homework they turn in, and I need some way to capture the score I give them on their homework."

I may very well be wrong about this; both in saying that you lost your forum audience, and also in my attempt to summarize your issue. Please understand that I am just trying to help you receive assistance from the experts here who generously give of their time and hard-won knowledge.

 

[Sean O'Halloran]

Sorry - I neglected to wish you good luck in your project!

Be well,

Sean

 

[calvinle]

Hi,

Yes, I believe that was the reason too, but I finally made it work on my own

Thanks for replying to this thread.