show all records of a table and add related new records for them in another table

[rishi3881]

I've three table with fields given in brackets.

1. TblDate (Year, Month, Day)
2. TblStudents (sName, Roll No) (suppose : 10 records are added)
3. TblMarks (Science, Math, Computer)

I want to make a form which contains field to select year and month and type date in main area and in subform of same form i want to display the list of all 10 students with Name and Roll No. in same subform i want to enter marks of all students in science, math & computer. Please help me how can i obtain this.

my plan
----------------------------------------------------------------
| |
| Year[Combo Box] Month[Combo Box] Day [Text Box] |
| |
| |
| subform |
|---------------------------------------------------------------|
| sName | Roll No | Science | Math | Computer |
|-------------------------------------------------------------- |
| John | 1 | | | |
| Sita | 2 | | | |
| Gita | 3 | | | |
| Rancy | 4 | | | |
| Powel | 5 | | | |
------------------------------------------------------------------
When form is first open there should be display Sname and Roll number and i should be able to enter marks in different subject in the above chosen date.

any help will be highly appriciated.

thanks.

 

[MarkK]

- Also, time is a scalar value, so you don't need a table of dates unless those dates are significant as data points. This might be the case, for instance, in a payroll system where you'd need a list of statutory holidays.
- In your case, if you want a date associated with a mark, then you may want it in the same table that Uncle Gizmo has correctly outlined...

StudentID, markSubject,        markResults,       markDate
5................3..................7.............6 sep 08
5................6..................6............12 sep 08
5................4..................9.............8 jun 08
11...............3..................7............12 jul 08

- With this approach you can do tons of summaries, like average grade per subject per year, students average grade per year, a students highest grade per subject over four years, and limitless others.

 

[rishi3881]

thans for the suggestion

I'm new in ms access. I know subjects should be in subject table. But i don't know how i can turn different subjects of subject table into fields to enter marks obtained by the students.


TblStudent (StuID, SName, Class)
TblSubject (SubID, SubName, StuID)


while entering data i need form in following format
SName----------Class-------English-------Science-----Maths

i don't have idea how can i convert subjects entered into TblSubject into fields to enter mark.


thansk.

 

[rishi3881]

The final form should look as my attached outline image. I am trying to make a form in which if i select class one, it should display the record of class one. if i select second term it should display the records of class one for second term. there are different subjects in different class and it should display the appropriate subject for each class.

i'm trying these things. please help how to go ahead.

i'm trying to make tables in following way.

TblClass (ClassID, ClassName)
TblExam (ExamID, ExamName)
TblLink (ClassID, ExamID) (For many to many relationship)
TblStudent (StuID, StuName, StuAddress, ClassID)
TblSubjects (SubID, SubName, ClassID, StuID) (Relation 1: One Class Many Subject, Relation 2: One student many subject)

waiting for openion and some guidence.

thank you very much...