What are MS Access advantages over MS Excel in analyzing large data files?

Answer

If you work on a huge number of sets of data, which you have to sort, filter, group, and create subgroups on which to calculate or extract values such as averages, medians, or/and maximums, you are better off with Access. The process of creating groups within groups and then performing calculations on those is way more cumbersome in Excel.

Excel on the other hand will be better for analyzing up to a few hundred records of data. The number of records, however, cannot exceed 65536 records (Excel 2003) or 1,048,576 (Excel 2007) . This is the maximum number of records Excel can handle.

It also depends on the way you want to analyse the data. Databases are good for finding relationships in data. Spreadsheets are good for crunching numbers and doing "what if" analysis (e.g. scenario models).

Because of a spreadsheets two dimensional design (rows and columns), the way users use spreadsheets as combined input/storage/output systems, and a spreadsheets inability to easily establish enforceable relationship rules between rows (or columns) on the same or different spreadsheets, the true multi dimensional nature of data can be hidden from the user and/or violated by incorrect data edits. This can make analysis of data difficult if the data is stored in a spreadsheet. It is easy to extract data from a database into a spreadsheet to take advantage of a spreadsheets unique data manipulation features (e.g. pivot tables, charts etc).

The real questions should be "Where do I store the data I want to analyse?" The answer to that is definitely "In a database". You can make it safe there.

Then choose the appropriate tool for the analysis you want to do.

First answer by ID3589904995. Last edit by Wichita. Contributor trust: 2 [recommend contributor]. Question popularity: 31 [recommend question]

Can you answer other questions about databases and data management systems?