Question Creating progress reports for students (1 Viewer)

mr_Ben

New member
Local time
Today, 16:57
Joined
Oct 3, 2013
Messages
4
Hi there! I've been using Excel at a low-mid level for a few years now and I think Access is the key to tying my data together and letting me see changes that occur over time. I'd like to describe what I'm trying to do and see if my concept is good and learn the right terms to search for so I can come back later with some work to show. I've done a Udemy course, which was great but it's hard to interpolate between the example use case and my own.

What I want to do:

I'm an ESL teacher in a Business College in Switzerland. We give our students an entrance exam, they get marks for 4 modules/semesters of English, then they take an external exam at the end. At the basic level, I'd like to make a report showing a student's overall marks at these 6 points plus breakdowns of reading, listening, writing, speaking, etc. marks where relevant. I'd like to add other details to this like attendance, homework completion percentage and some other stuff. Eventually, I'd also like to be able to group students by class and see grade averages for groups in relation to others based on certain variables (day of week, time of day, which is their mother tongue, are they full-time or part-time students, etc.).

I do a lot of my marking with Google forms which gets copied to Excel or in Excel directly. All of the grade calculations and transmission of marks to the school admin are also done in Excel. I also use mail merge in Office to send the marks to students. This means I have my data already (and I think I'm going to keep collecting it this way), so I'd like to figure out how I can use it without needing to change much.

What I have so far:

My first table is 'Student_List'. This is populated with my mail merge info for the students: title, first, last, email, a couple extra fields that I added as yes/no (whether they do a semester abroad and whether they take the super-advanced external exam at the end (C2 level)), and a field for notes. The students' email addresses are the Primary Key, because the school's IT admin won't repeat email addresses and the email already appears in the grade calculation Excel sheets I have.

The next 4 tables are the results from each of the last 4 modules. 'M1_2016_Marks', 'M2_2017_Marks', 'M3_2017_Marks', and 'M4_2018_Marks'. Modules 1 and 3 run in the autumn, 2 and 4 in the spring. All of the classes are in each list, with email as Primary Key again and then marks for writing, speaking, overall, etc.

Then I've got 'Progress_Report' that takes the student's name from 'Student_List' and pulls the scores from the Marks tables. I just manually created a relationship between the email fields in the 5 tables. These are one-to-one joins and join type 1: Only show if the records are equal. I think I did something else to make the report show students who haven't completed all 4 semesters (sometimes they take a year off, they also change classes a lot too, more on that later), but I can't remember what that step was or find where I edited it :/ Maybe it was something with 'or is Null'?

And, success! I can see the results for all of the students taking classes in these specific 4 semesters.

What I would like to get:

I'm glad to have a kind of proof of concept, but I'd really like to have something more robust set up so I can just append csv data to 'Student_List', import the grades as new tables at the end of each semester and see the results (and maybe export that data to Excel or somewhere else to make pretty visualisations with it).

The main issue seems to be tracking the students' classes. Students change classes pretty regularly and sometimes whole classes get merged/dropped. Plus students can be required to repeat years so I may not see them in 4 consecutive terms. The school uses a naming convention that's repeated for each incoming group (referring to which year they are in, their mother tongue, whether they're full- or part-time and whether they're a mono- bi- or trilingual group), so class 1A in 2017 will probably be 2A in 2018 or it could be 2B or 2C because it merged with a different group, but either way now there's a new class 1A in 2018.

I have attendance records organised by class, and the exams are organised by class, but I think adding a field(s) to those tables isn't the best way. I have a table 'Class_List' that holds information about each class (year, spring/autumn semester, module, day of the week, time, mono-/multilingual, etc.) and I'd like to now make class member tables that just list the students for each semester in each group. Then I can somehow use a query to find the student in the class member tables and then search the appropriate marks table to pull the results for 'Progress_Report'. Then I could also show results for 4 semesters for everyone who is in a particular class, even if they weren't always together.

So students who are in class '2019_Spring_2A' might have been in different classes but I could still see their results compared.

Student1: 2017_Autumn_1A, 2018_Spring_1A, 2018_Autumn_2A, 2019_Spring_2A
Student2: 2017_Autumn_1B, 2018_Spring_1B, 2018_Autumn_2A, 2019_Spring_2A
Student3: 2016_Autumn_1A, 2017_Spring_1A, 2018_Autumn_2A, 2019_Spring_2A

Another reason it would be useful to do this, rather than just by importing the marks one class at a time, is because the entry and exit exams are not sorted by class, so I've also got to match that data somehow.



I know this is a giant wall of text and should really be broken down into discrete parts in order to really address it. I'm mostly interested in people's opinion of my idea. Like, do you think it will work the way I've described it? Where do you think it will go wrong? What other ways of doing it are there? And if you know of other projects like this in the forums, or key concepts I can search for, I'd be glad to do some more reading on my own. Thanks!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:57
Joined
Feb 28, 2001
Messages
26,999
First and foremost, Access can help here.

Second, you have starting what I consider one of the best things to do when starting a project such is this: You are laying out what you have and what you want. But you can't stop here. Your design overall is feasible and any comments I make aren't going to be about that. I will offer some general advice on this stage of your project.

Old Programmer's Rule #1: If you can't do it on paper, you can't do it in Access.

Meaning: Access is like a power tool of the mind. But tools don't build things - people do. So switching to Access is like switching from a brace and bit to a power drill. Neither one does any good unless you know where (and why) to drill the hole. So my advice for this stage of your project is to lay out in some detail, perhaps even with sketches of what you want things to look like, so that you know WHAT you want to build. You have to identify things that we sometimes call data entities. For example a student is an entity in your model. A course is another entity. An exam can be an entity. A mark (related to a course or exam) might be a separate entity but might not.

The idea is that you build tables of entities, and all entities of the same type go into the same table. Look up and read about normalization if you haven't already done so. Hint: If doing a web search, look up "database normalization" since by itself, normalization can refer to political, mathematical, and chemical normalization conditions or processes.

Once you have your entities in table, start thinking of what needs to come together for each result. I often suggest using a bunch of sticky notes to represent data entities and a dry-erase board to draw lines of action or relationships between entities. When you have the process drawn out on paper, you can then do something about it in Access.

Old Programmer's Rule #2: Access can't tell you anything you didn't explain or provide first.

This is derived from the simple fact that Access is the subject matter expert in making forms, reports, tables, queries, etc. But it knows DRECK about anything else. There, you have to be the subject matter expert. If you want X in a report, you have to have a data source for X. If you want XYZ in a report, you EITHER need a source for XYZ, or you need sources for X, Y, and Z plus a formula to compute the XYZ result.

Therefore, when you have that detailed design, you check every element you wanted to see to verify that you have data sources. This will sometimes mean working backwards from conclusion to premise to verify that it is possible to get the desired output.

Now, let me make a comment about a specific element you mentioned.

These are one-to-one joins and join type 1: Only show if the records are equal.

In a dynamic join based on 1:1 relationships, you have to be careful. They are actually quite rare because in the real world, that uniqueness of relationship is rare. USUALLY things are 1:many or many:1, so using the "only where they are equal" option is good - but the idea of expecting only one match might not be correct. You might miss cases where a many:1 mapping is correct. And PLEASE understand that I'm not saying don't do it. I'm saying "check for uniqueness, don't ASSUME uniqueness." Because if you make the wrong assumption, you might get the wrong result by omitting legitimate matches.

When using a 1:many match where it is possible to have no matches for some entries can be handled several ways. The "IS NULL" option is possible, but more often than not, we recommend use of the NZ function to convert null responses to something more benign (and tractable, since Access formulas don't like nulls).
 

Ranman256

Well-known member
Local time
Today, 11:57
Joined
Apr 9, 2015
Messages
4,339
I have a sample classroom schema below. take what you need:

each course has many classes
each Class has a list of students.
the class then has assignments (tWork) ,tests, etc.
the student is graded on the assign and graded (tStudentWork)

tCourses
-----------
CourseID (autonum)
subject [Basic math, science, soc.study,etc]
CourseType [math,sci,humantiy,music]

tStudents
--------------
StudentID (autonumber)
FirstN
LastN
Addr
City
St
Zip
phoneH

tClasses (classes offered, Sci101, Tri202)
----------
classID (auto)
courseID [from tCourses]
TeacherID
semester
Subject
Room#
ClassTime
meetOn (m,w,f)

tClassRoster (students in 1 classroom)
--------------
RosterID (auto)
ClassID (long)
StudentID (long)
FinalGrade

tClassDays (days the class meets)
--------------
classID
ClassDate
Status (in session, canceled, holiday)
Memo

tWork (all tests,quizes given to class)
----------
WorkID (auto)
RosterID
WorkType (test,lab,quiz)
GradePts

tStudentWork (grade student got on test)
-------------
StudentWorkID
StudentID (long)
WorkID
Grade

tAttend (attendance per class)
---------
AttendID
StudentID
ClassDate
Status (late,attend,absent)
Memo
 

bastanu

AWF VIP
Local time
Today, 08:57
Joined
Apr 13, 2010
Messages
1,401
Not to scare you from the start, the project you describe is very doable but it will involve A LOT of work to get to a useful product. I have worked on a database (Access-based) for international education for many years and while the product was great it did take a lot of effort. As doc-man hinted you will need to learn about database relationships and design then plan your intended structure carefully (for example you do not want one table for each session/module but one table with the following fields: UniqueID(primary key for the table)/SessionID(M1_2017)/StudentID(12345)/Mark_Reading(A+)/Mark_Writing(B-)/etc.

I am attaching some pics here to show you some of the details of what can be achieved with Access, you might not need all of those.



Good luck with your project!

Vlad
 

Attachments

  • ESL_Switchboard.jpg
    ESL_Switchboard.jpg
    75.8 KB · Views: 429
  • ESL_Student.jpg
    ESL_Student.jpg
    98.7 KB · Views: 548
  • ESL_Class.jpg
    ESL_Class.jpg
    111.5 KB · Views: 574
  • ESL_Teacher.jpg
    ESL_Teacher.jpg
    54 KB · Views: 572

isladogs

MVP / VIP
Local time
Today, 15:57
Joined
Jan 14, 2017
Messages
18,186
Agree with the previous comments.
I spent almost 10 years developing applications for various schools in the UK. Access is ideally suited to this type of multi-user application especially if you have SQL Server backend datafiles.

If you want to see what is possible and get some ideas, you could look at the DEMO versions of my schools apps on my website http://www.mendipdatasystems.co.uk
There are three demo versions available with a full set of data for a fictitious school. Most features are available in the demos but you won't be able to view code or enter your own student/staff data

HTH
 

mr_Ben

New member
Local time
Today, 16:57
Joined
Oct 3, 2013
Messages
4
Thank you all! I really appreciate the feedback.

@The_Doc_Man, Thanks for the tips, I like the idea of actually building a flow chart for it. I've been mulling things over in my head for a long time already and just connecting the pieces is difficult so I see what you mean about needing to know where to drill the holes. This will also make it easier to communicate what I'm trying to accomplish with people who are generous enough to offer their help!

@Ranman256, Thank you for these structures, it's great to see it laid out like this.

@bastanu, Wow! That does look like a lot of work! I know it's a big project but I feel like it's something that's worth my time for a few different reasons. It'll come in fits and starts as my real job allows but I'm trying to document what I'm doing and just keep at it so I can make some progress eventually.

BTW, it looks like there was some personal information not edited out in the ESL_Teacher.jpg--all the others are scrubbed so maybe you meant to edit it out?

I think I see what you mean with the table, it doesn't make sense to try and write a query to check different tables when I can just put all the entries in one table if I just add a couple fields. Each record will have an autonum Primary Key, I can match the email address (we don't have student numbers) with a one-to-many relationship and then I just need to point the data from the different semesters to the right places.

@isladogs, WOOOWWWW! Thank you so much for sharing this! SDA is very inspirational (and intimidating)! I'm having trouble opening the demo though, it says it was created with the 32-bit version of Microsoft Access and to open it with the 32-bit version. I'm running Windows 10, Office Professional Plus 2016. Your site says I should be able to run it, so I'll try installing it again. Thank you, though! I hope I can get it to work. I won't be doing anything so comprehensive, I'm only going to track my own classes with my own data. Our college is quite fragmented, there are a lot of professors who only teach one or two classes. I'm fortunate in ESL that I generate a good bit of data each term and can get new data each semester. I love seeing what can be done though.

Thanks again, all. I'm off to do some flowcharting!
 

isladogs

MVP / VIP
Local time
Today, 15:57
Joined
Jan 14, 2017
Messages
18,186
Hi Mr Ben
Unfortunately its a 32-bit ACCDE file so you won't be able to open it in 64-bit Access. Should be fine in Access 2016 32-bit
Its a VERY LARGE download - 890MB zipped!
The data is for last academic year as I've neglected to update it recently

I've never bothered to do a 64-bit version of the DEMO files as its not been an issue previously
The full version is distributed as an ACCDB file so will run in both 32-bit & 64-bit versions. I realise that doesn't help you at the moment. :rolleyes:

However, there are various PDF files you can look at with screenshots and explanations
e.g. see http://www.mendipdatasystems.co.uk/application-help-files/4584623363

HTH
 

mr_Ben

New member
Local time
Today, 16:57
Joined
Oct 3, 2013
Messages
4
No problem, @isladogs I'm already making some rather pervy noises just looking at the images in the pdfs! Love it!
 

bastanu

AWF VIP
Local time
Today, 08:57
Joined
Apr 13, 2010
Messages
1,401
mr_Ben, all data in those pics comes from a demo db with dummy data, thanks for pointing that out! Glad to see you're already making progress on your project. One minor point - I would suggest you do not attempt to use email addresses as unique identifiers - a student could change ten on you in a matter of months and you are left with orphan records/ batch updates - not pleasant. I suggest you create a StudentID (or StudentNumber) in your db (the Access database autonumbers can be great for that if you don't have a specific format in mind) and use that to identify individual students throughout your application. So the StudentList table in your case that stores the bio demographic info for the students would have an autonumber field called StudentID set as the primary key and all the other tables (tblAddresses, tblSessions, tblClasses, tblEmails , etc. will have StudentID as a long integer field acting as a foreign key.

Cheers,
Vlad
 

isladogs

MVP / VIP
Local time
Today, 15:57
Joined
Jan 14, 2017
Messages
18,186
I also agree about not using email as a PK field. Instead I would use the student admission 'number' as your PK which will already be on the school system and will definitely be unique. I put 'number' in quotes as it may be a text field.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:57
Joined
Feb 28, 2001
Messages
26,999
I'll add a voice to the selection of PKs. There are some rules if you want to get picky. To see what the web has to say, search for "candidate keys" to understand why you do - and don't - select certain fields as PKs.

For the eMail example as a PK... it is a bad candidate for a couple of reasons.

1. People can change eMail carriers, so you run into "ripple effects" if someone changes their eMail address and you had used that as a PK. It still would not be wrong to declare a unique index on an eMail field (as other than the PK) but you would have a serious issue the first time you found someone with no eMail address.

2. The eMail address tends to be long. Ideally, you want shorter keys because the shorter the key, the more you can fit into one index block, which would happen because you ALWAYS have an index on the PK of a table. Access doesn't offer you a choice.

3. Longer keys (see item #2) are harder to type and easier to cause a typo.

There are other reasons as to why you would choose one field over another. Search for "candidate key" and see a ton on the subject.
 

Cronk

Registered User.
Local time
Tomorrow, 02:57
Joined
Jul 4, 2013
Messages
2,770
I can't see any reason for not using an Autonumber field as the PK, and this should never be revealed to the user.
 

isladogs

MVP / VIP
Local time
Today, 15:57
Joined
Jan 14, 2017
Messages
18,186
I don't have a problem with using an autonumber PK field.
However, my point is that the student data already exists on the main school management system database and the admission number will be used as the PK field in that.
Rather than retyping all the student data, import it (or link it) and use the existing structure
 

Users who are viewing this thread

Top Bottom