Should I use MS Excel or MS Access?

Question

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?

Solution 1

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.

Solution 2

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.

Solution 3

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.

Solution 4

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.

View more solutions

Comments

[Doktoro Reichard]

Access and Excel are tools. The fact Excel is more intuitive to use than Access does help justify the dislike some people have against Access but they remain tools: one can learn to use them effectively given enough work.

[James_pic]

Excel ceases to be simpler than Access when it doesn't fit with the structure of your data. I've seen too many examples of people reinventing Access within Excel, using VLOOKUP and complicated VBA

[Antonis Christofides]

@DoktoroReichard A Boeing 777 is also a tool, isn't it? You can certainly learn to use it given enough work. However, you will not do that amount of work unless you intend to become a professional pilot.

[Mike Honey]

+1 and also most applications quickly get into requirements for separate related tables, each with a unique granularity. Access shines at this whereas Excel is very weak. In this example you might well need separate tables for Students, Courses, Students in Courses, Marks for Students in Courses ...