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!
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!
