I want to store my students' information such as marks, etc. And I need a way to make calculations on this information. I also want the application to make it easy to fill students' information (in the beginning of the term) and to publish their marks online later on.
Should I use MS Excel or MS Access? When should I use one of them over the other?
I would go for MS Access because if a user has to fill in the sheet, it becomes prone to errors. MS Access is a database and can calculate fields and protect fields based on earlier entries. It is more advanced to build the sheet, but if done properly, it will be far easier to fill in. Additionally, it is easier to generate a proper report showing just the information you want to display, not everything. This is also possible with Excel, but a lot harder to do.
If there isn't much calculation to be done, and the application does not have to be that extensive, Excel may just do it. But Excel is meant as a sheet that you work in, not as a database, where Access is meant as a database with different methods to fill in the information and extract other information.
If you are the only person to work in the sheet all the time, Excel is probably a better choice if the data and calculations and reports are limited.
Storing students' information and performance would be easy in either Excel or Access. In Access, OP would just need one table. However, calculations are much harder in Access, because the function interface is highly nonintuitive. Also, using calculation in Access is overall far less intuitive, because functions operate on arrays, rather than on individual cells. Excel is probably the best choice.
I have been in similiar situation in the past. For the simple management of one course, I was able to do everything with Excel and also use this data easily to generate word documents.
Some years before I programmed a student management tool in Access. For this purpose (many courses, different tasks, different degree paths etc.) and the fact that the output and forms where used by other people and potentially less tech-savy staff Access was great. Also the data became much cleaner, as input validation got rid of some "best practices" used before.
So if it is just for your course, and next term you are only going to copy the file and use it as a template - go with Excel. If you are working together with other staff on the same data and want to manage data across terms etc. Access will be far better in the long run, but will require more work upfront.
One year, as a science teacher of a small private school, I had to do report cards for 300+ students because the grading computer system in place was no longer accessible (a mini-crisis of sorts). With no budget for more actual grading software, I was left to use Excel and/or Access to make report cards. I decided to use both Excel and Access. Here is why:
1) Excel was already being used to store grades, and overall is much simpler to create tables with. Access is a real pain to work with in terms of table development.
2) We needed to be able to combine multiple records and create reports. Access is designed to create reports from multiple tables with linked variables.
3) Access can do a dynamic link to multiple Excel spreadsheets, which updates the database whenever the Excel files change. This meant we didn't have to load things more than once.
All in all it went pretty well, though I will say that the system was definitely on the fritz and crashed a few times with so many tables being linked (this was with Office 2003). The nice part of working with database software though is that the data is locked and even a software crash does not kill your database file. I think Excel now has a lot more capabilities... so you might not need Access at all, though I've never created reports with Excel.
I think this is primarily opinion-based, but here we go anyway.
If you are not a computer professional, you should never use MS Access. I think it was very unfortunate that MS produced Access, providing the illusion that non-computer professionals can mess around with relational databases. If you try to do it, you will get it seriously wrong.
If you are a computer professional, then the question becomes seriously opinion-based. If the problem I'm trying to solve has a low budget, I usually prefer simpler solutions that anyone can understand, rather than something that will become unmaintainable the moment I leave or the moment the money flow is hindered or the moment that I don't have enough time to maintain it. If I have a higher budget, I usually prefer other database systems.
