Help on how to handle this complex data entry task

[MajP]

I played with this a little. Here are some functions I found very reuseable

Public Function GetTotalQuestions(ExamPaperID As Long) As Long
  GetTotalQuestions = DCount("*", "tblQuestions", "ExamPaperID = " & ExamPaperID)
End Function
Public Function GetTotalQuestions_2(ExamPaperRef As String) As Long
  Dim ExamPaperID As Long
  ExamPaperID = DLookup("examPaperID", "tblExamPapers", "ExamPaperRef = '" & ExamPaperRef & "'")
  GetTotalQuestions_2 = DCount("*", "tblQuestions", "ExamPaperId = " & ExamPaperID)
End Function
Public Function GetCompletedQuestions(TestID As Long) As Long
  'A test id uniquely describes a student taking the test.
  GetCompletedQuestions = DCount("*", "tblResults", "TestID = " & TestID & " AND NOT Score is Null")
End Function
Public Function AllResultsCompleted(TestID As Long) As Boolean
  'Used to determine if you have
  Dim ExamPaperID As Long
  Dim completed As Long
  Dim total As Long
  ExamPaperID = GetExamFromTest(TestID)
  total = GetTotalQuestions(ExamPaperID)
  completed = GetCompletedQuestions(TestID)
  If total = completed And total <> 0 Then AllResultsCompleted = True
End Function
Public Function GetExamFromTest(TestID As Long) As Long
  GetExamFromTest = DLookup("examPaperID", "tblTests", "TestID = " & TestID)
End Function
Public Function QuestionsInserted(TestID As Long) As Boolean
  'Determine if you have inserted dummy records
  Dim existing As Long
  Dim required As Long
  Dim ExamPaperID As Long
  existing = DCount("*", "tblResults", "TestID = " & TestID)
  If existing > 0 Then QuestionsInserted = True
End Function
Public Function GetStudentName(StudentID As Long) As String
  GetStudentName = DLookup("Fullname", "tblStudents", "StudentID = " & StudentID)
End Function

These are useful for doing all the checks I talked about. How many questions inserted, answered, required, AllQuestionsCompleted.

It will turn green once all questions are filled in.
It will prompt you to do the insert query when you click on a person that does not have question filled in

If your results are like 1 to 5. Then I would have the marks textbox trap up arrow and down arrow. Up it adds 1 down it subtracts 1. That way you do all entries with 3 fingers on the keyboard. Tab, up arrow, up arrow,... Tab to next question. But if not it is tab, keypad, tab, keypad because the other fields are disabled.

 

[SurreyNick]

You're a true gentleman MajP. Thank you so much. I'm just about to sit down for our evening meal and shut down the PC for the night, so I'll have a proper look at this tomorrow, but the very least you deserve is an immediate thank you tonight.

Nick

 

[MajP]

Thanks. Like I said it is not a lot to it, just a bunch of little things. As far as I can tell are you table structure is correct. As far as circular relationships, I do not think that is an issue. They all go PK to FK so no issue. People think relationships (only have to do with referential integrity) are the same as joins in a query. They are not. So it will use those joins as a default in a query and those closed loops will cause the query to be not updateable. In the query just break the loop so you have a single path. (if you can show the field in one path do not need the other). So you may have to play with joins when creating queries if they are to be updateable. The only thing that threw me for a loop was tblTest (it was just the name that confused me). It is really Student_TestPeriod. It is a record that identities the date a student took a test and which Exam they took.

One thing. Get rid of the lookup values in the tables. This makes things really confusing, and can lead to problems. You can Google "The Evils of Table Lookups". Make all your lookups at the form level. When I look at the table I want to see studentID and the value 1. It is confusing when it has a studentref in there.

 

[MajP]

Added a couple more bells and whistles. This alerts you if there are incomplete scores and provides a list of those with incomplete scores. Added some progress textboxes. Added the feature to clear scores or delete inserted records. Only thing left is the Reassignment. I would put another button on the subform by the clear and delete. When you select reassign I would present a pop up with the other students that have taken the test. Then do an inset or update and then a clear. You will have to do some checks. If the other student has records you would have to do an update. If the other student does not have records you will do an insert.

 

[SurreyNick]

As far as circular relationships, I do not think that is an issue. They all go PK to FK so no issue. [text cut] .....it will use those joins as a default in a query and those closed loops will cause the query to be not updateable. In the query just break the loop so you have a single path. [text cut] The only thing that threw me for a loop was tblTest (it was just the name that confused me). It is really Student_TestPeriod. It is a record that identities the date a student took a test and which Exam they took.

One thing. Get rid of the lookup values in the tables. This makes things really confusing, and can lead to problems. [text cut] When I look at the table I want to see studentID and the value 1. It is confusing when it has a studentref in there.

Interesting. I wasn't aware relationship loops might prevent a query being updatable. I haven't encountered that yet in my db but I will certainly watch out for the possibility.

Yes, Tests isn't the best description for my tblTests. It hasn't anything to do with physical test papers, that's my ExamPapers table. The Tests table is as you recognise an instance of an exam being taken by a student, so it's a junction table between Students and ExamPapers. I have fallen foul of using descriptions that make sense to me, assuming incorrectly that I will be the only person to ever look at the structure. My apologies for adding unnecessary confusion.

The same apology goes for using lookup values in my tables. When I was developing the db I thought this was a really good idea, and to start with it was quite helpful particularly when I was creating queries, but it even confuses me now and it's my db! I will remove them all

N.

 

[SurreyNick]

Added a couple more bells and whistles.

MajP, I don't know how to express my gratitude properly. You have saved me an absolute ton of work. With my lack of knowledge it would have taken me several days to do this and even then I doubt it would be to the same level you have raised this form to. But perhaps even better than this you have given me solutions in the context of my database that I can study and learn from and incorporate into other parts of my db. Thank you ever so much.

I won't be the only person to be grateful either.

This database is something I'm attempting to create for my son, who is a biology teacher at a large school, to replace a set of tasks he has been trying to do with spreadsheets. My son is attempting to help students achieve better results in their GCSE biology qualification by tracking and reporting on the students’ knowledge and understanding of each of the 119 biology topics that make up the qualification. Currently students only get a basic overall achievement mark for each test, so they don't necessarily know where they need to improve their knowledge.

What my son wants to do is possible because every question of every test relates to one of these 119 topics and each question has a mark value. What he started doing in the last year is drilling deeper into the results of a test and telling his students how well they have done in various topics too. The students have found this immensely useful and it has being paying dividends because their grades have been improving and their enjoyment of and commitment to the subject has also improved. Better still, because it appears it has been making a difference to my son's students, his fellow teachers have also started doing the same for their classes. But there's a problem.

Producing the figures requires detailed analysis of the various tests the students undertake and even doing a few tests on spreadsheets is a challenge. Going forward my son wants to be able to aggregate data for every test each student takes throughout the three years of their study so students get even better data about how well they are progressing overall and in each topic. From the data he also wants to do trend and comparative analysis i.e. how one student compares to the average, how one class compares to another, how one year group does compared to a previous year group, how boys compare to girls etc. etc. etc.

None of the analysis is particularly complicated but he and his fellow teachers now want to do it for nearly 1500 students tracking their progress over three years of study and that makes it a mammoth task. Handling and manipulating this much data on spreadsheets is not really practical. A dozen or so years ago I made a couple of very simple databases and so I offered to try and help by developing something to replace the numerous spreadsheets the teachers use. As is no doubt very apparent, I’m an Access novice, but I’ve retired so the one thing I have in my favour is the time to do it and the willingness to learn. I am against the clock however, because the teachers really would like a functional database by September in time for the start of the next academic year.

My son isn't expecting an all singing all dancing database, he’s just hoping he’ll get something that’s functional. I have no doubt that given enough time I can give him something quite nice, but I’m coming from a low skill base, so the help you have provided is truly appreciated and by helping me you might just help students achieve a better outcome too. Thank you

N.

 

[MajP]

MajP, I don't know how to express my gratitude properly. You have saved me an absolute ton of work

Thanks, but like I said it was not a lot of work. It really was several small pieces to work together. I done this so much that I do not have to think about how to do it because I have done it already. My niche on this site is using Access for engineering and mathematical modeling so definitely can further help with any analysis. There are a couple people on this site especially @isladogs who build educational databases (some professionally) and can give a lot of guidance. Here is his site with educational examples/tools

Mendip Data Systems

A wide range of commercial & free example apps, code samples and extended articles together with news and events for Microsoft Access developers.

www.mendipdatasystems.co.uk

You have saved me an absolute ton of work. With my lack of knowledge it would have taken me several days to do this and even then I doubt it would be to the same level you have raised this form to.

Do not hesitate to come here, this is what people here like to do. Some people do crossword puzzles, soduko, play video games, etc.; the people on this site like to answer questions. I usually pick to harder questions just to see if I can figure it out. I learn a ton that way. So if it is taking you a while to figure something out, someone here will solve it quick. A lot of us have very extensive libraries of databases and code so often it is not writing something new because it has been done before.

I’m an Access novice, but I’ve retired so the one thing I have in my favor is the time to do it and the willingness to learn. I am against the clock however, because the teachers really would like a functional database by September in time for the start of the next academic year.

My wife works in the school system and we are canceled for the rest of the year. so maybe you have some time. If you are a novice I would have not guessed that. Actually thought the opposite. Your table structure is pretty complicated with several many to many, but it is extremely well designed IMO and properly normalized. Most people could not have done this properly normalized. The table and field naming convention is excellent, and because of that it is easy to follow. You keep it simple with primary keys using autonumbers. No bad names (spaces, special characters, reserved words). The only issue was to get rid of the table lookups, which surprised me because everything else was so well designed. A lot of time people come asking for a fancy form, but you have to spend lots of time redesigning their whole db. If the skeleton is good, you can easily add lots of features to the db.

I am guessing the amount of data for this will get very large. Is there any way in the future to move the backend to SQL Server or some other enterprise db? Every test, every question for every student could get pretty large. If there is a plan to do that, then need to keep that in mind now in the design.

There is something in your forms that is making the db abnormally large. There is not that much data or other objects. Sometimes this is an embedded image. I will try to chase that down. But I could not zip this with my forms and the old forms.

If this is a long term project then do not be afraid to come back with more questions. If it makes sense to add to this thread then add, if not start a new thead and just put a link back to this. I have threads that took hundreds of questions and replies. Most recent

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

Would monitor size or resolution have anything to do with it and/or having a modal form? I'll try again when I get back home.

www.access-programmers.co.uk

Good luck, we are here to help.

On more thing. The form has ideas of how to do things for demonstration purposes, not necessarily the way to do it. For example the button to see the incomplete records and the pop up query. In real life I would have a form with the table of incomplete records. You would click on a person and it would send you to update their scores. If you had more real estate that list could be at the bottom of the form, or you could change the source object of the first subform to be the list of records to complete ( wow that is a good idea, I think I will demo).

 

[MajP]

Here is an update with several usable features.

Records now are either complete (all scores entered), Incomplete (dummy records inserted not all answered), or not started (no dummy records). Added text boxes to show which person and exam on left side. Added check if you exceed the allowable score. Let you override in case you have extra credit points. The fix incomplete button just filters to the incomplete records. Only feature left is to reassign scores.

Another potential features
1. Perfect Test button (assign all scores the max). Assuming you have pretty good students this will aid in filling out. Assume they miss one or two. Edit only those scores, then hit the button to fill in the rest.
2. Add better conditional formatting in the scores subform. Red not filled in, green perfect score, yellow less than perfect score. This will help to verify results.

 

[MajP]

Potential features added. That should definitely speed up data entry and reduce a lot of mistakes. Only need to focus on questions missed.

 

[SurreyNick]

LOL. This is the Rolls Royce of all forms. Love it!

 

[SurreyNick]

My niche on this site is using Access for engineering and mathematical modeling so definitely can further help with any analysis.

Your expertise could prove most helpful MajP. The analysis is pretty straightforward and I can do it all on a spreadsheet very easily, but not so easy to do it in Access and display it in forms. I will have to use VBA with lots of functions to generate the data and I want to display a lot of it graphically with bar charts, pie charts and perhaps scatter charts too. I already know Access 2010 has very poor charting capabilities so I may need some assistance to push the envelope a bit. I have a few more data management forms to finish first and then I will begin all these results forms. If I can, I also want to create a few dynamic forms where the user can select certain parameters and see the results displayed in the form.

There are a couple people on this site especially @isladogs who build educational databases (some professionally) and can give a lot of guidance. Here is his site with educational examples/tools

Mendip Data Systems

A wide range of commercial & free example apps, code samples and extended articles together with news and events for Microsoft Access developers.

www.mendipdatasystems.co.uk

Yes, I have come across his name while searching on this forum for ideas and solutions to some of the problems I have encountered along the way and I have visted his website several times for additional ideas..

My wife works in the school system and we are canceled for the rest of the year. so maybe you have some time.

Yes, my son’s school has also closed until further notice. They are hoping to reopen in September so I have several months still, but I want to do extensive testing before I give it to him. There is nothing worse or more frustrating than being given something that doesn’t work as you want it to. At least my son will have time to help beta test it too.

I see you are 4 hours behind UK time. Where are you located?

If you are a novice I would have not guessed that. Actually thought the opposite.

You are very kind, thank you. The first database attempt was poor and I couldn’t generate the queries and reports I wanted so I scrapped it and started again. The second database was better, but the structure still didn’t deliver everything and it would sometimes take ages to run crosstab queries even on just 120,000 records. It took three attempts, three new Access books and more than two months to come up with the current design. I think it will now work but you are right about the volume of data.

I am guessing the amount of data for this will get very large. Is there any way in the future to move the backend to SQL Server or some other enterprise db? Every test, every question for every student could get pretty large. If there is a plan to do that, then need to keep that in mind now in the design.

I am a bit concerned about this too. I calculate the Results table will have a minimum of 120,000 new records each year and will need to hold 9 years of data so a little over 1 million records. The other tables have more fields but have few records. The Students table is the next largest and nine years of records will only be 4,500. The big issue is if the teachers want to adopt the db for all subjects. At the moment it is just for biology. If they do this then I think they will have to get a professional company to migrate it to another platform. It is far beyond my ability. I will try and design in such a way as it is easy to understand that is all.

There is something in your forms that is making the db abnormally large. There is not that much data or other objects. Sometimes this is an embedded image. I will try to chase that down. But I could not zip this with my forms and the old forms.

Yes, I was lazy when I began the design and just added images and objects without first resizing or checking them. You are almost certainly right, it will most likely be an image. I need to properly scrutinise everything I have included.

Good luck, we are here to help.

Thank you.

N.

 

[MajP]

Added some updated leather upholstery. Put in the user scores. not just scored questions. Take a look at all those helper functions, You can reuse them in lots of places such as queries and calculated controls. Also see how they are additive. You can wrap one inside another. Pass in the test id, find the examID using a function that is inside another function.
However as the db get big some of these functions may need to get done by subqueries or aggregate queries. In other words instead of counting or summing in a dsum or dcount you have a aggregate queries that do this then do a dlookup of that query or use that query directly.

One thought. If in general students only miss a small fraction of the questions. Say 10-20%, then you could just store those questions in the results table where they missed or exceeded the marksvalue. TblResults become TblResultDifferences. This would reduce the amount of records by ~80%. I think you could still make it look like this with some slight modification. Now the results table is very compact, it is nothing but keys (good design) so not sure of the practical difference between 1million records or 200K. However what gets iffy is trying to figure out what you have not filled. Now you would have to likely use a temp table to do this. I would have to get someone's opinion on what is the better approach. Also I have to think at some point you can offload some of the data. At that point you could just export the missed/exceeded scores and you will always be able to determine the total scores.

 

[MajP]

Interesting. I wasn't aware relationship loops might prevent a query being updatable. I haven't encountered that yet in my db but I will certainly watch out for the possibility.

It is not actually the relationship, but the default joins created by the relationship. When you build a relationship, it assumes you will always want to join a query in the same way which may or may not be true.

Yes, Tests isn't the best description for my tblTests. It hasn't anything to do with physical test papers, that's my ExamPapers table. The Tests table is as you recognise an instance of an exam being taken by a student, so it's a junction table between Students and ExamPapers. I have fallen foul of using descriptions that make sense to me, assuming incorrectly that I will be the only person to ever look at the structure. My apologies for adding unnecessary confusion

This was the only place the naming was not very clear so not a big deal.

The analysis is pretty straightforward and I can do it all on a spreadsheet very easily, but not so easy to do it in Access and display it in forms. I will have to use VBA with lots of functions to generate the data and I want to display a lot of it graphically with bar charts, pie charts and perhaps scatter charts too. I already know Access 2010 has very poor charting capabilities so I may need some assistance to push the envelope a bit. I have a few more data management forms to finish first and then I will begin all these results forms. If I can, I also want to create a few dynamic forms where the user can select certain parameters and see the results displayed in the form.

I am not expert in charting, so hopefully someone else chimes in. I am pretty good at vba automation (manipulating another office product from a different office product). So I tend to export to excel to do the heavy lifting and manipulate the Excel file from Access. I have lots of code for doing that manipulation. However a lot of times you can do the charting in Access if you do some crosstabs or advanced queries to pre-process the data. Sometimes a temp table is needed.