Access or Excel

Yesterday in FORT 120 we started looking at Microsoft Access, a relational database management system. It has similarities with Excel, the spreadsheet solution. This raises the question, why learn both programs and when should one be used over another.

At first glance there are similarities. Both store data in tables of rows and columns. Both perform calculations and have form and report-writing utilities. However, there is a big difference.

In an interesting article on the Microsoft web site Emma Nelson pinpoints the big difference. Excel is best when all the data fits into a single table (known as a flat file). When the data should be divided into multiple tables that relate to each other through common fields, Access can best handle the job. In a relational database the data is divided into multiple tables to make storage more efficient and search quicker. Access has all the tools needed to manage a relational database.

Borrowing from the Nelson article, use Access when you:

  • Require a relational database (multiple tables) to store your data.
  • May need to add more tables in the future to an originally flat or non-relational data set.
  • Have a very large amount of data (thousands of entries).
  • Have data that is mostly of the long text string type (not numbers or defined as numbers).
  • Rely on multiple external databases to derive and analyze the data you need.
  • Need to maintain constant connectivity to a large external database such as one built with Microsoft SQL Server.
  • Want to run complex queries.
  • Have many people working in the database and want robust options to expose that data for updating.

And use Excel when you:

  • Require a flat or non-relational view of your data (you do not need a relational database with multiple tables). This is especially true if that data is mostly numeric–for example, if you want to maintain a financial budget for a given year.
  • Want to run primarily calculations and statistical comparisons on your data — for example, if you want to show a cost/benefit analysis in your company’s budget.
  • Know your dataset is manageable in size (no more than 15,000 rows).

In Geographic Information Systems (GIS) we combine geographic data with attribute data. This requires multiple complex data tables that use a relational database tools. In fact the geodatabases used in ArcGIS desktop use Access databases by default. When you make a map with data in ArcGIS you are, in effect, using a relational database.

This entry was posted in FORT 120 and tagged , . Bookmark the permalink.

Leave a Reply