Introduction
One common cause of confusion in Microsoft Office is when to use Excel and when to use Access for storing information. There is a good reason for the confusion. Both can be used for storing and manipulating information and there are benefits and drawbacks to each. In this guide I will explain simply each application, when they should be used and the benefits and drawbacks of using each.
What Is Microsoft Excel?
Excel Orders
Microsoft Excel is a spreadsheet. This is an application made up of individual rows and columns of cells and its primary function is to perform financial calculations. It is also possible to store data in a spreadsheet. For example you can store simple customer details with one item per cell. When you have items stored you can perform tasks such as sorting, calculating totals, creating graphs etc.
Customer Table
| Name | Address | Town | Phone |
| Joe Bloggs | 1 High Street | Dublin | 435 5556778 |
| Ann Green | New Street | New York | 435 5555673 |
| Bob Builder | Long Road | London | 233 4454545 |
What Is Microsoft Access?
Microsoft Access Orders Table
Microsoft Access is a database. A database is a tool that allows the storage of data in tables. Tables look similar to sheets in excel however they are designed for more extensive use of data e.g. If you want to store Customer Details, Orders, Staff Details, Inventory etc.
With Access you can also create graphs, perform calculations, create reports and much more.
When Should I use Excel and when should I use Access?
The clearest way to draw a line between Excel and Access is as follows: If your data involves one set of data, for example Customer details then this can be stored in a spreadsheet or in one table in an Access database. If your data involves more than one table e.g. You need to store customer orders as well, then you will need to use Access.
Orders Table
|
Name |
Item | Amount |
| Joe Bloggs | Hammer | 2 |
| Joe Bloggs | Screwdriver | 1 |
| Bob Builder | Saw | 4 |
In this example we have what is called a one to many relationship i.e. each one customer will have many orders.
What is the Simple Rule of Thumb?
Use Microsoft Excel if you have no experience of using Access and you only have one simple set of data to use.
Use Microsoft Access if you have some technical knowledge and wish to perform more extensive tasks with your data such as creating reports or queries.
If you wish to create a database with multiple sets of data and more complex reports, queries etc. then you would be better off consulting a professional developer to see what is involved.
