Help on how to handle this complex data entry task

[SurreyNick]

I could do with some help regarding the best way to approach the following tasks. It’s to do with entering students’ exam scores for the schools database I’m developing and it’s the thorniest task I’ve confronted so far. It’s not a simple case of just entering a student's total score for each exam taken, but how to go about capturing the scores for each and every question of exams that may have as many as 100 questions.

To help illustrate what I am trying to achieve I have included two pictures. The first is an illustration of the objects I think I need in a data entry form and the second shows the relevant tables in my database which would populate the form and also receive the data input. The ultimate goal is to populate a table (tblResults) with the student's “Score” for each question of an exam they have taken and along with this score I need to store a corresponding QuestionID and also a TestID this latter item being specific to each student i.e. different for each student.

If you look at the illustration for the data entry form you will see I want to include two combo boxes from which the user chooses an exam paper and a class ref. These combo boxes are named cboExamPaper and cboClass respectively and I want the selections from these two combo boxes to query the database and populate a list box with the names of each student in the selected class cohort who has taken the selected exam and along with each student name returned also return a unique reference number for each student. This reference number (tblTests.TestID) is one of the three values I need to store in my results table and is a combination of ExamPaperID+StudentID+TestDate. It is also via this tblTests table that the students need to be drawn for the selections made in the two combo boxes.

From the combo box selection of exam paper I also want to populate another list displaying all the question numbers for that particular exam, alongside which for every question is an empty box for the user to enter the score the student got for each question (this Score is another of the three values I need to store in my results table). Although the list displays all the question numbers for the exam the value it actually holds for each question is tblQuestions.QuestionID and this is the third of the three values I need to store in my results table.

The way I envision the form working is that the user clicks on one of the student names in the list box. This highlights the student name and also the tblTests.TestID (the value need to capture). The user then enters the score the student got for each question (moving from field to field by either tabbing or preferably hitting the enter key after each input). As they enter each score the calculated value in the Total Score field on the form gets updated. When the user finishes entering the scores for that student they click the “UPLOAD” button. When this UPLOAD button is clicked the code carries out the following validation checks:

1. That every question field has a corresponding (valid) score entered i.e. there are no Null values. Zeros are acceptable, but nulls are not.
2. That the score entered for each question does not exceed the marks value for that question i.e. by checking the score entered against the value in tblQuestions.MarksValue for that QuestionID.

If errors are found they are dealt with by relevant message boxes.

Once any errors are dealt with the user is presented with a “Are you sure you want to continue” message box, which prompts the user to check they have selected the same name in the list box of the data entry form as that written on the exam paper. Assuming everything is correct the code routine then needs to take the score entered for each question and include with it the the QuestionID and the TestID. This information needs to be appended to the tblResults table. Once this routine is complete the list of student names in the list box on the form needs to be refreshed so it displays just those for whom scores have yet to be uploaded.

I’m sure everything I want to do is possible but I really could do with some specific advice on the approach to take with the overall task and each step within it.

Many thanks in advance.

Nick.

 

[plog]

The Access development process should be Tables then Reports (and the queries they need) then Forms. I don't think your tables are correct, so I think you should put aside this issue about forms for a bit.

Without knowing your data I see a big issue: Circular Relationships. There should only be 1 way to trace a path between tables. You've created a loop among tblResults, tblQuestions, tblExamPapers and tblTests which means you can draw 2 paths (clockwise and counter-clockwise). This is incorrect.

Without knowing your data, I think you have unnecessary/wrong relationships.

Are exams papers really directly related to questions?
Are exams really directly related to tests?
Why are tests and questions not directly related?
How come one question can have multiple results?
You presumably have a class table (tblClasses_Students) but tests are directly related to students without tests being related to a class?
Same with exam papers--they are not related to a class.

Can you answer and explain those things?

 

[vba_php]

Nick,

to start, 2 tables. 1) TESTS. 2) QUESTIONS (id field, score field, etc...)

alternative tables => 3) STUDENTS, etc...

you have a little too many tables. consolidate that stuff.

 

[SurreyNick]

I don't think your tables are correct..[text cut]...Without knowing your data I see a big issue: Circular Relationships....[text cut]....Without knowing your data, I think you have unnecessary/wrong relationships.....[text cut]....Can you answer and explain those things?

Hi plog

I’m going to have to explain this in an analogous way. I have included a copy of the relationships for the entire database and its tables and the way I look at it is this:

Exam Papers = Company
Questions = Products (each question being a product)
Assessment Objectives = A set of properties of products (questions)
Biology Subjects = another set of properties of products (questions)
Students = Customers
Tests = Customer orders
Results = Product quantities of orders

On this basis the relationship between tblExamPapers and tblTests is a redundant one and I accept the criticism about the circularity. However, I have been hesitant to remove it because it requires the inclusion of fewer tables in queries and so I have left it in place for convenience.

To answer your questions in order….

• Are exams papers really directly related to questions? In the same way that products are related to companies, yes, questions are directly related to exam papers.
• Are exams really directly related to tests? If you mean are ExamPapers directly related to Tests, no, this is a redundant relationship but I have left it in place for convenience.
• Why are tests and questions not directly related? Because if tests are customer orders, results are unit quantities of each product (question) ordered.
• How come one question can have multiple results? One question can’t have multiple results except insofar as each question can be answered by multiple students, but each student can have only one answer for each question. (Although in much the same way that a customer can eat more than one of the same brand of chocolate bar a student can answer a question more than once if they sit the exam paper i.e. test more than once).
• You presumably have a class table (tblClasses_Students) but tests are directly related to students without tests being related to a class. Yes, as you can see from the relationship structure there is a tblClasses_Students. If you assume classes to be the town where the customers (students) live then that will explain the indirect relationship between tests and classes and also between exam papers and class. I hope that makes sense.

Nick.

 

[plog]

Your analogy confuses more than helps. It's not like the actual entities you are working with are completely foreign to anyone (Tests, Students, Questions, etc). I just can't load your terms and that relationship screenshot in my head, disregard my knowledge of how those term should function and use a different set of terms and ideas in their place.

With that said, all those questions I asked about the relationships were to make you reconsider and confirm to yourself that they are right. You know your data better than me, so as long as you considered them that's good enough for me to accept them as good.

However, circular relationships are wrong. With the new screenshot, you've introduced more. I just think that if I was right about some of the things I questioned it would go a long way to eliminating those circular relationships.

 

[SurreyNick]

plog, I didn't intend to insult your intelligence and I'm sorry for offending you.

I tried to adhere to the rules of normalization and I did follow the standard development process (tables then reports, and the queries they need) and this is the best I have been able to come up with. Right now I don't think I can afford to start over and honestly, given my very limited experience, I don't think I could come up with a better solution. It's ugly. I'll take it on the chin.

For the time being however I'd really like to press on so I can give my son something he can use, even if it's only a temporary solution, and in terms of output I am reasonably confident it will deliver everything that's needed of it. All said, I'll understand if people are reluctant to offer assistance in light of what I've said.

Nick.

 

[MajP]

So if I understand correctly. You have filled in table test data prior. So there is a record showing what student took what exam.
On your form you pick an Exam and a Class from the combos. The first sub form is a query that shows all students in that class that took the exam.
You are link tblTest, tblStudent,tblclass,tblExamPapers
You can simply link the subform to the two comboxes
linkMasterFields: [cmboClass];[cmboExam]
linkChildFields:[ClassID];[ExamID]

You can do it using standard subform linking or you can set the recordsource of the subform on the combo after update.

Now I would run an insert query after that on tblResults add a key for every student, and every question for that exam. If you index these two fields then you will not get duplicates and you can attempt to insert whenever you want.

Now link the second subform to the first subform. Either use a standard linking or manually set the recordsource of the second subform on the oncurrent event of the first.
The second subform is results. You already did the insert so you will have a blank record for every question. Then just fill in the values. Since each question has a possible value, verify that on the afterupdate to make sure it is not exceeded.
Looks to me like you have all the parts, I am not sure what part you are having an issue with.

 

[Galaxiom]

Have a look at the exam grades entry sample database in Post #3 of this thread.

It shows how to do it all in the form without running the often suggested insert query to pre-populate a results table. It uses a subform in a slightly unusual way. Although the records show in the subform they don't exist in the source table until data is entered into another field.

The key to it is the outer join in the recordsource query of the subform. Many developers seem to struggle to understand how it works but I believe it is because they are expecting something much more complex. There is only a tiny amount of code.

 

[SurreyNick]

Have a look at the exam grades entry sample database in Post #3 of this thread.

Thanks Galaxiom. I like the look of this and will almost certainly use for one of the forms I need to create, just not sure if it's the solution I want for this. I may be wrong though!

Nick.

 

[SurreyNick]

Looks to me like you have all the parts, I am not sure what part you are having an issue with.

Hi MajP thank you for taking the time to reply to my post and I think you’ve given me all the clues I need in order to achieve what I want but perhaps I can check a few things.

First off, yes, you are correct the Tests table will be populated prior to students' test scores being input.

One of the first issues I had was how to use the selections of the two combo boxes to generate the list of students in the first list box. This isn’t covered in any of the books I have, but now you have pointed me in the direction with the linkMasterFields and link ChildFields properties of the subform I should be able to go away and learn how to do this. Thank you.

The next issue I had was how to get the student scores for each question into the results table. I thought I had to somehow create a dummy field on the form or in a table to temporarily store the data and then append it to the results table. However, I think what you’re saying is that once the selections in the first two combo boxes have been made and a student is selected from the list box I should run an update query to tblResults with all the tblQuestions.QuestionID’s and the tbl.Tests.TestID, which will then give me a blank field into which the user can enter the scores and use this to populate the second list box. Is that correct?

I can see how that will make it much easier to input the data and also verify that the values entered for each question don’t exceed the max value for the questions. Very elegant

I guess the only issues that might leave are:

1. how to verify that the user has selected the same name on the form as that written on the exam paper? and...
2. how to clear the second list box once the final score has been entered and then refresh the first list box to remove the name of the student whose data has just been uploaded.

For the first issue do you think the best method would be to employ a yes/no message box on the before update property of the list box and use the user's response to this to trigger the update query or cancel the action? As for the second issue what method can I employ to trigger the refresh of the two list boxes once the last question's score has been entered? Bearing in mind that the number of questions for each exam is different.

Do I understand correctly that you recommend I remove the primary key in the Results table which is currently set on the ResultsID field and instead index the TestID and the QuestionID fields? At the risk of exposing my ignorance, I don’t know how to do this. I know how to index a field in table properties to disallow duplicates but not how to index two fields together.

I don’t understand what you’re advising me to do when you say… Now link the second subform to the first subform. Either use a standard linking or manually set the recordsource of the second subform on the oncurrent event of the first. Is this an explanation of using the data in the Results table from the initial update query to populate the second list box?

Have I understood? Thanks.

Nick.

 

[MajP]

Thanks Galaxiom. I like the look of this and will almost certainly use for one of the forms I need to create, just not sure if it's the solution I want for this.

What Glaxiom is suggesting would work equally well on the right subform. That is a left outer join of
a query consisting of TblQuestions, student, exame to tblResults joined by question ID and studentID filtered by examID, and student id.
It would be easy to build but hard to explain without having some test data to demo.
This has some advantages and disadvantages over the insert query. It will simplify some upstream processing and make it a little harder on some down stream. By down stream, knowing what is missing. With the insert you can easily see what students have not been filled. With this method you would need another query using an outer join to show this. Not hard. So I would not dismiss this as an option. I prefer just to do the insert for simplicity, but would not say it is a better approach and may be just as easy to do an outer join as long as you can make the outer join updateable.
You can link subforms to unbound controls and it works the same as linking to values in a main table. You can link to two or more unbound controls. So you can try it that way. However, it is just as easy to set the filter on the subform based on the two controls.
To link two subforms on a mainform you can make an invisible txtBox (txtBoxLink). In the oncurrent event of the first subform set the value of txtbxlink to the studentID. Then link the second subform to txtbxlink
linkMasterField: [txtbxLink]
linkChildField:[studentID]
That will require not code, but will only work in this case with the insert query because if not there is no record for studentID. If using the left join you would just set the filter on the second query. However, that query is going to be somewhat complicated. I think you will need a cartesian and left join. I am not sure if I can do that one in my head without trying.

Do I understand correctly that you recommend I remove the primary key in the Results table which is currently set on the ResultsID field and instead index the TestID and the QuestionID fields?

No. I personally hate composite keys, but you could. However, you can make a composite index. In table view click indices and create a name and add two fields

IdxStudentQuestion            QuestionID
                                                       StudentID

Set unique to true

how to verify that the user has selected the same name on the form as that written on the exam paper? and...

Not sure what you are asking here, but that does not sound like a database question. When the user enters into the tblTest which student took the exam, I assume they pull from a combobox the possible users. If the student wrote JJ. Smith and they are in the db as John Smith then that is a data entry issue. Often people store Fname, PreferredName, LName

how to clear the second list box once the final score has been entered and then refresh the first list box to remove the name of the student whose data has just been uploaded

This may be simpler with the insert. I would have maybe another subform or a popup that shows which student results have not been added. Or you could write code to pop up "all student info added for said exam".

any chance you can post a small demo. You can search on this site how to scramble data. My guess the only thing you really need to scramble is student information. You can strip that table to simply id and names then scramble the names.

Although that sounds like a lot. You can build that form with very little code and queries. The problem that makes it hard, is that there are lots of small different pieces working together.

 

[MajP]

Here is a couple of examples linking multiple subforms together using just the master child links. However, as I said once you get into multiple controls it may be just as easy to build your own filter.

 

[SurreyNick]

Hello MajP

Let me begin by saying how grateful I am for your help. It really is appreciated.

I didn’t reply to your posts yesterday because I was busy trying to implement some of your suggestions and I’m pleased to say I have made a bit of progress.

I do like the elegance of the method suggested by Glaxiom but I have decided to go with the insert query method because I’m familiar with it and there are other skills I need to learn for this form.

I spent quite a lot of time looking at the two examples you posted linking multiple subforms together and went as far as unpicking the simplified form for drilling down through hierarchies to return records and even had a go at adapting it for my form. Alas I couldn’t get it to work so in the end I decided to follow your advice and use a subroutine to filter the subform based on the two controls. I am pleased to say I did manage to get this script to work so I now have a subform on the form which displays the names of the students and their TestID. I also created the composite index on the Results table in the manner you described.

Today I’m planning to move onto the insert query that will populate the second subform for the data entry and after that all the other small different pieces that are needed to finish the form.

I should have explained more clearly what I meant by “how to verify that the user has selected the same name on the form as that written on the exam paper”. You’re right it’s not a database question, except insofar as I want to include a message box at some stage in the process that gets the user to check that the scores they are entering are for the correct student i.e. the name on the exam paper matches the name of the student they have selected in the subform! I just have to decide at what point in the process to initiate this message box.

As I see it these are the tasks I still need to undertake to complete the form:

• Sub routine for an “enter scores” button which on click checks to make sure there are selections in the two combo boxes and that a student record has been selected in the list box. If True then
  • Check the TestID for the selected student is not already in the Results table
  • Create temporary table (TestID, QuestionID and Score) insert into this table the TestID of the selected student and all the QuestionID’s where [tblQuestions].[QuestionID] = value in form combo box cboChooseExam
  • Display contents of temporary table in subform 2 ready for scores to be entered.
• Sub routine that checks as scores are entered that the value entered does not exceed the max value for that question number. I need to work out how to write this bit of code.
• Sub routine to make sure the data entry user starts entering scores at question 1 and that a value has been entered for a question before allowing the user to move onto the next question field. This will make sure the user doesn’t get out of sync.
• Sub function to calculate the total marks for the scores and which refreshes as each score is entered.
• Sub routine for an “upload” button which on click checks there are no null values in any of the question fields (basically to stop the user trying to upload the scores to the Results table before a valid score has been entered against every question), prompts the user to confirm they have entered scores for the correct student, copies the contents of the temporary table to tblResults, deletes temporary table, displays a message box to the user to confirm the upload was successful and then refreshes the first subform.

Still quite a bit to do.

You asked if I could post a small demo of my database – I tried, but even deleting a load of data and zipping it, it is still too large. Do you know what the max size is for the upload to work?

Nick.

 

[MajP]

@SurreyNick

1. Sub routine for an “enter scores” button which on click checks to make sure there are selections in the two combo boxes and that a student record has been selected in the list box. If True then
Check the TestID for the selected student is not already in the Results table

The way I do this is make a single Procedure

Private Sub FilterSub
If not isNull(me.comboStudent) and Not isNull(me.cmboExam) then
code to filter
code to append
end if
end sub

Then in the after upate of each combo call Filter sub. It does nothing unless both are filled in, and will do something if one is updated
FilterSub

2. Check the TestID for the selected student is not already in the Results table

Good idea, but the beauty of the composite index is that you do not have to. If all or some records do not exist then it appends. If they are already there then technically if fails, but if you do a currentdb.execute you will not get a message so it appears as if nothing happens. The potential benefit of this is that if one or more record got deleted, but not all it would repopulate those records.

3. Create temporary table (TestID, QuestionID and Score) insert into this table the TestID of the selected student and all the QuestionID’s where [tblQuestions].[QuestionID] = value in form combo box cboChooseExam

DO NOT DO A TEMPORARY TABLE. You will add a bunch of unneeded work, and make it harder. This should just be an insert from the query that links testID to questionID filtered by the combos.

4. Display contents of temporary table in subform 2 ready for scores to be entered.

NO TEMPORARY TABLE. This is the results table filtered by the selections in the combo.

5. Sub routine that checks as scores are entered that the value entered does not exceed the max value for that question number. I need to work out how to write this bit of code.

I would include in a query for sub form 2 the Potential score so you see it in subform 2 next to the entered score. Then in the before update simply
if enteredScore > Potential score then
msgbox "Score exceeeded"
cancel = true
end if

6. Sub routine to make sure the data entry user starts entering scores at question 1 and that a value has been entered for a question before allowing the user to move onto the next question field. This will make sure the user doesn’t get out of sync.

on the oncurrent event of subform2 you can return the recordset.absolute position and then do a dcount on the results table filtered by studentID and TestID. If the the absolute position is eqaul to the dcount then enabled = true else enabled = false and msgbox "out of synch".
I cannot remember if absolute position is 0 based or 1 based. So adjust accordingly.

7. Sub function to calculate the total marks for the scores and which refreshes as each score is entered.
This should be a function that uses a dcount. In the where it needs to reference the studentid and testid in the combos.

8. Sub routine for an “upload” button which on click checks there are no null values in any of the question fields (basically to stop the user trying to upload the scores to the Results table before a valid score has been entered against every question), prompts the user to confirm they have entered scores for the correct student, copies the contents of the temporary table to tblResults, deletes temporary table, displays a message box to the user to confirm the upload was successful and then refreshes the first subform.

Again this should not happen at all. No temporary table should be used. You are adding unnecessary complexity.
You will be able to do a check for partial filled results. This could be done in the afterupdate of the combos. You can have a query that returns all students with partial results.

9. You asked if I could post a small demo of my database – I tried, but even deleting a load of data and zipping it, it is still too large. Do you know what the max size is for the upload to work?

Create a new DB. Only add this form, and any code modules.
Import all tables.
Delete most information.
Leave a 3-4 students, exams, and the questions for these exams.

 

[SurreyNick]

Wow! Thank you MajP. That's really helpful

I hear the message about not creating a temporary table and if I can avoid it I will. My thinking was that a user might start entering scores for a student and then for some reason (perhaps they selected the wrong student) decide to abandon adding data. If they do this then I will have records in my Results table which are either incomplete or incorrect. It's really important I only have clean data in this table so I thought it made sense to hold the upload data in a temporary table until the user confirms they have done everything completely and correctly.

I have tried to remove all extraneous data from my db but I still can't get it small enough to upload. So here's a link to the cut down db. It's on the Microsoft OneDrive website: https://1drv.ms/u/s!AjCyYq5STei9gQX40WG49xpEtRxe?e=Qd1ZdC

Nick.

 

[MajP]

My thinking was that a user might start entering scores for a student and then for some reason (perhaps they selected the wrong student) decide to abandon adding data. If they do this then I will have records in my Results table which are either incomplete or incorrect.

You can still do that and more. Assume you build a procedure that checks for incomplete entries. That is a simple query of looking through the results table for any student,Exam combinations where the filled questions are null and not equal the amount of possible questions. This could be in an event that pops up and says "Student John Smith has incomplete record for Test ABC, Do you want to fix". If yes it sends them to those records on the form. If no then "Do you want to delete the existing scored for John Smith on Test ABC". If yes clear the scores. You can make this appear every time the close, open, or move on this form. Could even be when they close the db, telling them to fix incomplete entries.

On the same form you could have a button for Clear Scores/reassign scores. If you determine you entering for the wrong student, then I would have a prompt "Do you want to reassign to another student for this exam". If yes clear from current student, add to another student (just change the student in the results table'. And move the combo to new student. If no "do you want to simply clear scores from current student". If yes clear.

 

[MajP]

FYI,
Although this sounds like a lot, it really is a lot of small things working together. There should not be a whole lot of code and overly complicated queries. The issue is getting all these little things to work together in synch.

 

[SurreyNick]

I really like those suggestions. I think I'll have a go at incorporating all of that

 

[MajP]

Also on your first subform you will want a function to show completed entries student entries. Again this is a function that you pass studentID, and testid to and it counts to see if all questions answered for that student for that exame. You can use conditional formatting to show green and red for who is complete.

 

[SurreyNick]

Yes, that's an extremely sensible idea.

This is getting a lot more detailed now. I think it's going to take me a lot longer, but well worth the effort.