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.

 

[Uncle Gizmo]

>>>3. TblMarks (Science, Math, Computer)<<<

This table is constructed incorrectly it should be constructed in the following manner:

tblMarks:
Fields:
StudentID, markSubject, markResults

And an Example of the way the table would be populated would be thus:
StudentID, markSubject, markResults
5................3..................7
5................6..................6
5................4..................9
11................3.................7

where student:
5 = Fred
11 = Sue

where subject:
3 = French
6 = history
4 = mathematics

For more information on how to structure tables in MS Access please follow this link.

 

[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.

 

[Uncle Gizmo]

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

You may think you need that format, and indeed you can probably design your database with it As is your right. However you will run into all sorts of problems that no one has any idea of how to fix.

Lagbolt Said:
- 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.
You should heed his advice, any other way of doing it is just heading for major problems.

BTW, With MS Access, the table structure is just for storing the information, this should be done in the most efficient way possible. The presentation of the information takes place on the forms. I haven't seen a form constructed in the way you suggest from data in the correct format, mainly because the risk is if a new subject is added in the future, the user would have to wait for the programmer to modify the form. (Not a very well designed Database)

By using a sub form for entry of the marks received for each subject, if a new subject is added in the future the inherent design of your database just adds this as another entry in the subform list. No intervention required from the programmer.

 

[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...