Many thanks jdraw, worked. However, You have touched the point that if the student doesn't pass the level, then how to handle it ? in my school, it is allowed for each student to fail three times per level. Please extend your model with some ideas.Here is a draft model to consider.
Students Database structure for high school help (1 Viewer)
- Thread starter AlefAelol
- Start date
- Local time
- Today, 04:43
- Joined
- Jan 23, 2006
- Messages
- 15,379
In the model ExamSdtdCrsLvl, you could query the SdtExamMark and if the Mark is a FailingMarkCount of at any Lvl is more than 3, the Stdt is then disqualified.
I think this is a logic/calculation issue, not the model per se.
The logic involved would be along this:
Select FailingMarks and Group By Level and Student. You know the details of the Marking scheme. Try it and see with the model. You do not have to build the complete database to check this.
You can mock up a table, use some test data and see if you can use an algorithm to find Students who are now disqualified.
Good luck
I think this is a logic/calculation issue, not the model per se.
The logic involved would be along this:
Select FailingMarks and Group By Level and Student. You know the details of the Marking scheme. Try it and see with the model. You do not have to build the complete database to check this.
You can mock up a table, use some test data and see if you can use an algorithm to find Students who are now disqualified.
Good luck
Just a technical question, can I make the student register for the level first in separate junction table, then go to the StdCrseAtlvl. I tried to make many to many relation between Students and Levels and then link them (by their link table) to StdCrseAtlvl but I got error "No unique index in the reference field ". I need the database steps looks something like:
1- There is a CourseLevel table predefined (as in table LevRegCourse)
2- A students Register for the level first (as in the table I suggested)
3- A students get the courses specified by the level taken.
4- And got to the mark table
My aim is to facilitate the data entry form in a way that there is a main form with two sub-forms. The main form is for student information, then the first sub-form is for students level registration, finally, the second sub-form would for the course registration of the student at the level taken. Sorry for bothering you.
- Local time
- Today, 04:43
- Joined
- Jan 23, 2006
- Messages
- 15,379
That is probably the normal Student, but could you get 1 or more students transferring to your area and they could start in Level3 and Level6 (a brother and sister move to your area??)
This is a model to test your business facts. Build some test data and try to stump the model. Work through various scenarios. You must get your tables set up to meet your requirements.
You know your details better than any reader. If you want a registration table or a separate Marks table --if that is part of your requirement, then create them and test the model against your facts. Adjust as necessary.
I'm not saying that the draft model I provided meets your requirements. I am saying this is my draft model based on what I gleaned from your posts.
Good luck.
- Local time
- Today, 04:43
- Joined
- Jan 23, 2006
- Messages
- 15,379
For FailingStudent I mocked up a database.
Here is my sample data
Here is a query to Find and Group students who have Exam marks less than 50. I am showing the COUNT of exams with marks <50. You could have another query to identify only those with count 3.
With my test data, I get the following.
I'm sure there are issues with the model and the details, but this is just a mock up to see who Failed. More code may be needed since it must check for 3 Failed exams at 1 specific level. That is if your failure info allows someone to fail 2 times at each level and still be successful.
Here is my sample data
Code:
StudentID OtherStudentInfo OtherLevelInfo CourseID StdtExamMark
1 John Level1 1 15
1 John Level1 2 20
1 John Level1 3 22
2 Sam Level1 1 40
2 Sam Level1 2 60
2 Sam Level1 3 67Here is a query to Find and Group students who have Exam marks less than 50. I am showing the COUNT of exams with marks <50. You could have another query to identify only those with count 3.
Code:
SELECT Count(CountOfStdtFailExamMark) AS CountOfCountOfStdtFailExamMark
,StudentID
,OtherStudentInfo
,OtherLevelInfo
FROM (
SELECT ExamStdtCrseLvl.StdtExamMark AS CountOfStdtFailExamMark
,ExamStdtCrseLvl.StudentID
,Student.OtherStudentInfo
,LEVEL.OtherLevelInfo
FROM (
Student INNER JOIN (
StdtCrseAtLvl INNER JOIN [Level] ON StdtCrseAtLvl.LevelID = LEVEL.LevelID
) ON Student.StudentID = StdtCrseAtLvl.StudentID
)
INNER JOIN ExamStdtCrseLvl ON (StdtCrseAtLvl.CourseID = ExamStdtCrseLvl.CourseID)
AND (StdtCrseAtLvl.LevelID = ExamStdtCrseLvl.LevelID)
AND (StdtCrseAtLvl.StudentID = ExamStdtCrseLvl.StudentID)
WHERE (((ExamStdtCrseLvl.StdtExamMark) < 50))
)
GROUP BY StudentID
,OtherStudentInfo
,OtherLevelInfo;With my test data, I get the following.
Code:
CountOfStdtFailExamMark StudentID OtherStudentInfo OtherLevelInfo
3 1 John Level1
1 2 Sam Level1I'm sure there are issues with the model and the details, but this is just a mock up to see who Failed. More code may be needed since it must check for 3 Failed exams at 1 specific level. That is if your failure info allows someone to fail 2 times at each level and still be successful.
Last edited:
jdraw, Yes, more new students to come and start at any level, brothers and sisters involved in the school system too.
OK, the students data entry is look like the attached picture
I created a form based on the Students table which hold students ID, Name and another information. Then placed an unbound Combo Box with LevelID as row source. A sub-from is also build to show the corresponding courses for the level that user selects at the Combo Box. This worked perfectly by using the following code:
Code:
Dim strSQL As String
strSQL = "INSERT INTO StudentCourseLevel(StudentID, LevelID,CourseID,Credit) " & _
"SELECT " & StudentID & ", LevelID , CourseID, Credit" & _
"FROM QueryCourseLevel " & _
"WHERE LevelID = " & Me.cbolevelID.Value
CurrentDb.Execute strSQL, dbFaiOnError
Me.StudentLevelCourseSubForm.Requery- Local time
- Today, 04:43
- Joined
- Jan 23, 2006
- Messages
- 15,379
Good stuff. But don't be too quick to build the database.
As one of my colleagues says:
Show us your revised model. Make a list of key business facts, then (on paper) see if your model supports those facts.
As one of my colleagues says:
Show us your revised model. Make a list of key business facts, then (on paper) see if your model supports those facts.
Last edited:
Dear jdraw, I have revised the model and it works very nice to me. However, some issues in the progress appear. Let say,
I have a table StdCourseLevel with the following fields:
Code:
StudentID LevelID YearID CourseID Credit
Code:
StudentID LevelID YearID
Code:
LevelID CourseID Credit
Code:
LevelCourse.[LevelID] = StdLevelYearSub.[LevelID]- Local time
- Today, 04:43
- Joined
- Jan 23, 2006
- Messages
- 15,379
Please post your tables and relationships.
You mention subform, but not a form??
The tables and relationships represent the things involved in your business and how they inter-relate (business facts/rules).
Your forms are in fact the user interface to your application. Users, using forms, update, retrieve data from the underlying tables. Form are like windows through which users see/modify data that resides in your tables.
In some cases --this is typical but not specific to your application -- a user will select a value from a combobox; that selection may limit possible values in a second (cascading combobox) and the selection from that second combo may be used with other data selected on the form, or entered by the user to modify an underlying table. The modification may be a routine in vba using the various parameters to create or update
a record in the underlying table.
Do you have an updated version of your requirements? I see Credit and Year now.
You mention subform, but not a form??
The tables and relationships represent the things involved in your business and how they inter-relate (business facts/rules).
Your forms are in fact the user interface to your application. Users, using forms, update, retrieve data from the underlying tables. Form are like windows through which users see/modify data that resides in your tables.
In some cases --this is typical but not specific to your application -- a user will select a value from a combobox; that selection may limit possible values in a second (cascading combobox) and the selection from that second combo may be used with other data selected on the form, or entered by the user to modify an underlying table. The modification may be a routine in vba using the various parameters to create or update
a record in the underlying table.
Do you have an updated version of your requirements? I see Credit and Year now.
Last edited:
The form is based on the students table, then the subform located in it
- Local time
- Today, 04:43
- Joined
- Jan 23, 2006
- Messages
- 15,379
Again, I want to say that the model I presented was a draft built based on my interpretation of info in your posts.
You should work through your model with your requirements and some test data and scenarios. Make sure your model can handle your scenarios. Adjust as necessary, then build your database.
You should work through your model with your requirements and some test data and scenarios. Make sure your model can handle your scenarios. Adjust as necessary, then build your database.
Again, it works very nice to me. Could you just help me how to achieve the job I wanted from my previous post ?
Last edited:
Oh! now I noticed that I can't insert into table StudentCourseLevel. I got a warning "you cannot add or change a record because a related records is required in table StudentCourseLevel ", What the wrong I'm doing? please help
Last edited:
- Local time
- Today, 04:43
- Joined
- Jan 23, 2006
- Messages
- 15,379
When you have tables that are in 1 to many relationship, such as a Parent and Child(ren);you must have a record in the Parent before you can add Child(ren) to that relationship.
For example, in your model (capture.jpg) you must have:
- a Student record
- a Course record
- a Level record
- a StudentLevelYear record
- a LevelYear record
before you can insert a record into table StudentCourseLevel.
Look at which field(s) are used in the relationship(s). This will show you which field is causing the problem in the table you're trying to update. For whatever value you are trying to enter into this field, the same value must first exist in the related field in table.
You have to test your model with sample data and some test scenarios. This is a critical step. It appears that you model does not represent/support your business rules. You could review the RogersAccessLibrary tutorials to make sure you are following the process to create and test the model.
Good luck.
Last edited:
Now I can't get the relationships in the right way. Please see the photo attached. I am getting error "no unique index found for the referenced field of the primary key". That happened when I deleted the relationships between the "StudentCourseLevel" table and MarkTable and re-create it. Wonder why this error appeared!! it was just working. I didn't change any of the relationships elements.
- Local time
- Today, 04:43
- Joined
- Jan 23, 2006
- Messages
- 15,379
Have you tested your model with test data and scenarios --- on paper?
You have to design and test and adjust the model based on your requirements. Do not accept the model I provided as anything more than an attempt.
Look at some of the Student data models from DatabaseAnswers.org
https://www.google.com/search?q=dat...tudents&ie=utf-8&oe=utf-8&client=firefox-b-ab
http://www.databaseanswers.org/data_models/student_rosters/index.htm
http://www.databaseanswers.org/data_models/student_marking/index.htm
Can you show us your list of detailed requirements/business facts?
You have to design and test and adjust the model based on your requirements. Do not accept the model I provided as anything more than an attempt.
Look at some of the Student data models from DatabaseAnswers.org
https://www.google.com/search?q=dat...tudents&ie=utf-8&oe=utf-8&client=firefox-b-ab
http://www.databaseanswers.org/data_models/student_rosters/index.htm
http://www.databaseanswers.org/data_models/student_marking/index.htm
Can you show us your list of detailed requirements/business facts?
I just want to make a student to register to the level in separate table added the academic year to that table, then register for the courses that belongs to level registered. So the user. So, the user will have the following data entry forms:
1- LevelCourse form with subform to assign the courses to each level. That is ok to me and I have no problem with.
2- Another form and two subforms. The main form would be for student information entry. The first subform based on the table StudentLevelYear (as in the captured picture) and having three fields
Code:
StudentID LevelID YearID