Data are Same for Allthe record in the subform (1 Viewer)

akhon · Jan 20, 2019

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 · Jan 20, 2019

You need a many to many junction table.

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

Code:

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

Code:

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 · Jan 20, 2019

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 · Jan 20, 2019

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 · Jan 20, 2019

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

MajP · Jan 20, 2019

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 · Jan 20, 2019

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 · Jan 21, 2019

thankyou guyz for replying

MajP said:
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 · Jan 21, 2019

Code:

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 · Jan 21, 2019

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

Hahaha

Thanks for reply
I will try this here.

akhon · Jan 21, 2019

MajP said:
You need a many to many junction table.

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

Code:

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

Code:

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 · Jan 21, 2019

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 · Jan 21, 2019

Tried this but still same result...

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

akhon · Jan 21, 2019

Uncle Gizmo said:
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 · Jan 21, 2019

MajP said:
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 · Jan 21, 2019

See the attached file.

akhon · Jan 21, 2019

Thankyou bro what i have done wrong...

MajP · Jan 21, 2019

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 · Jan 21, 2019

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

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 · Jan 22, 2019

thanks you Guyz. Admin Lock this thread as solved

Data are Same for Allthe record in the subform (1 Viewer)

Registered User.

Attachments

You've got your good things, and you've got mine.

Registered User.

You've got your good things, and you've got mine.

Attachments

Registered User.

You've got your good things, and you've got mine.

Nifty Access Guy

Registered User.

You've got your good things, and you've got mine.

Registered User.

Registered User.

Nifty Access Guy

You've got your good things, and you've got mine.

Registered User.

Registered User.

You've got your good things, and you've got mine.

Attachments

Registered User.

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

Registered User.

Similar threads

Users who are viewing this thread