Data are Same for Allthe record in the subform

[akhon]

I have created a 3 table name:- Perf, student and subject. now i have created a datasheet for pref table and created a form in which i have kept field from student and sub-form of pref now problem is the record of pref are same for every student if changed the the value in pref sub-form of one student it get changed every student. in have also attached the database file.
OR

in form1 the subject Performance remain same for every student if i changed the value of one student it get changed the value of other student also.
plss look at the database i have attached and tell me what i have done wrong..... and if possible pls make it correct.I have also upload screenshot

 

[MajP]

You need a many to many junction table.

Each student has many Subjects, and One subject is taken by many studets

tbl_Students_Subjects
  Student_ID_FK  ' key related to a student
  Subject_ID_FK  'Key related to a subject
  Grade_Value 'A1...D2

1 1 A1   Student 1 in Math  has A1
1 2 A2
1 3 B2   Student 1 in Urdu has B2
2 1 C1
2 2 D1  Student 2 in English has D1

 

[Cronk]

MajP, I would have thought the OP's Perf table would be that junction table.


Akhon, have you set the ParentChild relationship for your sub form on StudentID?

 

[MajP]

MajP, I would have thought the OP's Perf table would be that junction table.

No that table only has a single foreign key to subject. That could become the junction table, but it is not normalized. That would require every student to have the same courses, which may or may not be correct.

 

[Cronk]

I didn't download and presumed StudentID would have been included. Good you've corrected that.

 

[MajP]

Actually I see I may have got it wrong. For each subject it appears you get a reading grade and a writing grade. Does not makes sense to me how you get a reading and writing grade in math. Must be the new math.

The junction table should still be
StudentID_FK
SubjectID_FK
ReadingGrade
WritingGrade

 

[Uncle Gizmo]

There's an example on my website which is similar but with students and hobbies. You should get a good idea of how it all fits together, see my website here:-

http://www.niftyaccess.com/many-to-many-relationship/

There's also a sample database which you can get for free if you subscribe to my newsletter...

Your data entry person might find it laborious if they have to enter each subject name manually. If that's the case then I also have a set of YouTube videos showing how to automatically fill a list of data in the subform. It's on my website here:-

http://www.niftyaccess.com/add-a-check-list-to-your-ms-access-database/

I actually demonstrate filling a check list automatically, however it can be adapted to comboboxes textboxes and you can even have a multichoice version.

I haven't included any sample code, as it was basically an exercise in getting people to learn how to program in VBA. It's been very popular, with Eight Thousand views!

 

[akhon]

thankyou guyz for replying

Actually I see I may have got it wrong. For each subject it appears you get a reading grade and a writing grade. Does not makes sense to me how you get a reading and writing grade in math. Must be the new math.

The junction table should still be
StudentID_FK
SubjectID_FK
ReadingGrade
WritingGrade

so i need to create a 3 table one is for student table for student details 2nd for subject name and 3rd table for junction in which i have create a above mention (StudentID_FK,SubjectID_FK,Reading, and writing). is this right.
and
this is for nursery student who joined the school and teacher need to submit a report on progress of the student in which they have to show that how a particular student is performing. it is not an exam report card.
by the way thanks
sorry for bad English

 

[MajP]

so i need to create a 3 table

As Cronk pointed out your Performance table is not complete or correct.
It should be
StudentID_fk 'relating to the student table
SubjectID_fk ' relating to the subject table
Reading_Grade ' the grade for reading for that student for that subject
Writing_Grade ' the grade for writing for that student for that subject

Still not sure how math has a reading and writing grade.

 

[akhon]

Still not sure how math has a reading and writing grade.

Hahaha

Thanks for reply
I will try this here.

 

[akhon]

You need a many to many junction table.

Each student has many Subjects, and One subject is taken by many studets

tbl_Students_Subjects
  Student_ID_FK  ' key related to a student
  Subject_ID_FK  'Key related to a subject
  Grade_Value 'A1...D2

1 1 A1   Student 1 in Math  has A1
1 2 A2
1 3 B2   Student 1 in Urdu has B2
2 1 C1
 2 2 D1  Student 2 in English has D1

Tried this but still same result....
plss anyone any solution

 

[Uncle Gizmo]

Well, I have not been able to code for a week as I'm on holiday. I have pointed you to video instructions, that I'm sure will offer some insight. However, if they are not helping, then I'll be at my PC Wednesday, and will be able to advise. Hopefully someone will get to you before!

Sent from my SM-G925F using Tapatalk

 

[MajP]

Tried this but still same result...

Then you did not do it correctly. Did you link ID to studentID in the junction table?

 

[akhon]

Well, I have not been able to code for a week as I'm on holiday. I have pointed you to video instructions, that I'm sure will offer some insight. However, if they are not helping, then I'll be at my PC Wednesday, and will be able to advise. Hopefully someone will get to you before!

Sent from my SM-G925F using Tapatalk

i have seen video but not helpful......

 

[akhon]

Then you did not do it correctly. Did you link ID to studentID in the junction table?

yep i linked both student table and subject table with pref table (junction table) but same result

 

[MajP]

See the attached file.

 

[akhon]

Thankyou bro what i have done wrong...

 

[MajP]

Thankyou bro what i have done wrong.

1) There was a relationship in the relationship window to subjectName, that made no sense. Had to delete that
2) You had subject Name in the Perf table. That should not be there only the subjectID should be stored.
3)Need link from student ID to studentID_fk for the subform

I also indexed StudentID_FK and SubjectID_FK in the Perf table. That way no student can get the same subject repeated.

 

[MajP]

A couple other things.
I went back and looked at the db I posted. The relationships did not get deleted. Go into the relationship window and select ShowAll. Then click on the lines and then right click. Delete the relationships. Now in the Perf table you need to change the SubjectID to a number field from short text. Suprised it actually worked.
If you do that you can add this code

Private Sub Form_Load()
  FilterCombo (False)
End Sub

Private Sub SubjectID_GotFocus()
 FilterCombo (True)
End Sub

Private Sub FilterCombo(FilterOn As Boolean)
  Dim strFilter As String
  strFilter = "SELECT SubjectID, SunjectName FROM subject ORDER BY SunjectName"
  Me.SubjectID.RowSource = strFilter
  Me.SubjectID.Requery
  If FilterOn Then
    strFilter = "SELECT SubjectID, SunjectName FROM subject "
    strFilter = strFilter & "WHERE SubjectID NOT IN (SELECT SubjectID FROM Perf WHERE StudentID = " & Me.Parent.ID & ") "
    strFilter = strFilter & "ORDER BY subject.SunjectName"
    Me.SubjectID.RowSource = strFilter
  End If
  'Debug.Print Me.SubjectID.RowSource
End Sub

The combobox will only show the classes you have not yet selected. Once you select English it no longer appears in the list as a choice for that student.

 

[akhon]

thanks you Guyz. Admin Lock this thread as solved