When should I use Excel Spreadsheets and Access Databases for my different types of data?
Microsoft Access is a database application.
Microsoft Excel is a spreadsheet application.
They possess many similarities, which can make it difficult to decide which program you should use. For example, both programs can store large amounts of data, run powerful queries and analysis tools to slice and dice that data, and perform sophisticated calculations that return the data that you need. Excel gains added data manipulation functionality through Power Query and PowerPivot.
Each application has distinct attributes providing their own advantages, depending on the type of data that you are managing and what you want to do with that data. For example, if it is your goal to maintain data integrity in a format that can be accessed by multiple users, Access is your best choice, whereas Excel is better suited for complex numerical data that you want to analyse in depth.
Excel Spreadsheets and Access Databases
In very general terms, Microsoft Access may be the best choice when you have to track and record data regularly, and then display, export, or print subsets of that data. Access forms provide a more convenient interface than an Excel worksheet for working with your data. You can use Access to automate frequently performed actions, and Access reports let you summarise data in printed or electronic form. Access provides more structure for your data; for example, you can control what types of data can be entered, what values can be entered, and you can specify how data in one table is related to data in other tables. This structure helps you ensure that only the correct types of data are entered.
Access stores data in tables that look much the same as worksheets — but Access tables are designed for complex querying in relation to data stored in other tables.
As a spreadsheet application, Microsoft Excel can store large amounts of data in workbooks that contain one or more worksheets. However, instead of serving as a database management system, such as Access, Excel is optimised for data analysis and calculation. You can use Excel to build models for analysing data, write simple and complex formulas to perform calculation on that data, pivot the data any way that you want, and present data in a variety of professional looking charts.
As sophisticated as spreadsheets have become, they still have some serious drawbacks. Spreadsheets are not ideal for long-term data storage. They only offer simple query options, do not guard data integrity, and offer little to no protection from data corruption.
Database Concepts
A database is a collection of information organised and presented in a structured way. In simple terms:
| Data = | Pieces of information. |
| Database = | Information Source. |
The reasons for using a database are:
| · Storing Data | Computerised data takes up less space than its paper equivalent. |
| · Finding Data | Locating data by computer is very fast. |
| · Sorting Data | Sorting and resorting data indifferent ways is achieved quickly and with little fuss. |
| · Updating Data | As it is stored centrally, data need only be dealt with once. |
Your Data will be organised, easy to maintain, easy to store and easy to retrieve
Types of Database Structures:
A Flat File Database – A simple table, consisting of categories (or fields) in columnar format and records entered in rows. “Databases” created in spreadsheet applications (like Microsoft Excel) are flat file databases. An old dfashioned example of a flat file or two-dimensional database is the old printed telephone directory.
Relational – A relational database involves several individual tables which are linked by common key values. Data records can be extracted from several tables at the same time. Because these more advanced databases can involve multiple data tables they are sometimes referred to as three-dimensional databases.
Flat file Database Vs Relational Database
Where a Flat File system deals with all the information on a single table a Relational Database System splits a broad range of information into several tables. This means that data entry is quicker with less wasted effort. Also ‘queries’ or methods of searching for specific data are much faster. The power of the relational database system lies in its ability to relate two or more tables together to retrieve and collate information.
Other differences are:
- Because of data duplication for each record entered in a Flat File System.
There is much more wasted space.
There is more time spent on data entry.
- Queries will run slightly quicker in a Flat File Database System.
Microsoft Access is an RDBMS, a Relational Database Management System.
Database Layout
In an Access database, data is stored in the form of a table. These consist of fields and records:
| Field | Field | Field | |
| First Name | Last Name | Date of Birth | |
| Record | John | Doe | 03 Nov 58 |
| Record | Patrick | Deer | 12 Jan 66 |
| Record | Anthony | Elk | 31 Jul 55 |
| A Field is a piece of data of a specific type: | e.g. First name or last name. |
| A Record is a collection of fields: | e.g. information about a person. |
| A Database is a collection of records: | e.g. your business card file. |
The power of the relational database lies in its ability to relate two or more tables together to retrieve and collate information.
NOTE: An Access database is a container for lots of different items. Tables are such items. You can have many tables in one Access database (i.e. one Access database file).
Microsoft Access Objects
An Access Database uses the following objects which knit together to form the database:
Tables Hold the information.
Queries Manipulate the information and are used to extract data (find records)
Forms For user input. These are easier on the eye than table grids.
Reports To display the information in a presentable way and with totals etc.
Macros To automate basic tasks.
Modules For full Visual Basic programming of procedures & functions.
All of these can be designed by the user either from scratch or with the help of the sample templates.