Do You Need Access If You Have Excel?
When you’re working with simple lists, you have no reason to use Access, because Excel offers basic tools for such tasks as sorting, filtering, and computing values. When you’re working with complex data, however, Access is the program to use. Although you could store even complex data in list form in Excel, doing so typically results in a lot of data duplication and the risk of data-entry errors. What’s more, storing data in list form requires you to use special Excel tools, such as pivot tables, so that you can analyze and view the data in a meaningful way. That’s not the case with Access.
Here’s a real-world situation involving complex data that is a good candidate for being stored and managed in Access rather than in Excel: Let’s say your business needs to maintain records as to which employees have been assigned company assets, such as smartphones, computers, tablets, or video projectors. Each employee may have any number of these assets in their possession, and you need to store a description and an ID number for each electronic device an employee has (you needn’t limit your data to electronic devices, of course).
The other way to arrange the data in Excel is to allocate one row in the worksheet to each electronic device, but this setup is just as cumbersome, albeit for different reasons. In this case, you would need to repeat each employee’s name and employee number for each device in their possession. That means you would be repetitively entering the employee data, and you would end up with a lot of duplicate data stored in the Excel file. And because the spreadsheet would be cumbersome to work with, there’s a chance that over time you or a colleague would enter some employee data incorrectly. You might end up with what looks like two different employees, for instance, simply because someone entered a record for “Bill Smith” and someone else created a second record for the same person as “Billy Smith.”
For such scenarios, Access is a vastly superior tool to Excel. In Access, you would create one table (a list) of employees, with each record containing the employee’s name and ID number. In a second table (another list), you’d enter the description and ID number for each device, plus the ID number for whichever employee is in possession of that item. The only duplicate data in this arrangement is the employee ID number, which serves to link the two lists. This is what’s known as a relational database, and Access makes such a database very easy to create.
How to Create a Database in Access
First, launch Access and choose File, New. Since you’ll be building this database from scratch, choose Blank database.
The table has no column headers at this point, and you need to alter the design so that you can use employee ID numbers. To make these changes, click the View drop-down menu on the Home tab of the Ribbon toolbar, and select Design View. When the Save As dialog box appears, type Employees as the table name (in the tab beneath the menu bar) and click OK.
Once the table is complete, save it by right-clicking the Employees tab and clicking Save.
Next page: Creating a table for item information
Create a Table to Contain Item Information
To create a second table in which to store information about the electronic devices your employees are using, select the Create tab on the Ribbon toolbar, and click Table. Click on Click to Add, type T23, and press Enter. Type iPad, and press Enter twice. (“T23” and “iPad” are just for the purposes of illustration, of course; use whatever number scheme makes sense for your business.)
Each item must be allocated to the employee who is in possession of it, so to change the table design to accommodate this, click the View drop-down menu on the Ribbon toolbar, and click Design View. Type the table name Electronics, and click OK.
In the Design View, type EmployeeNumber in place of the field name ‘ID’. Press the Tab key; in the Data Type drop-down menu, choose Number. Type IDcode in place of ‘Field1’ and Description in place of ‘Field2’.
Currently the EmployeeNumber field is set to be a “primary key” field, which prevents you from entering duplicate data in that field. You’ll need to change that setting so that you can enter the same employee number multiple times if that employee has several devices checked out.
Return to Datasheet View by clicking View, Datasheet View on the Ribbon toolbar. When prompted, click Yes to save the table. Type 2011 as the employee number for the first electronics item in the list.
Continue and type this information into the table:
EmployeeNumber | IDcode | Description |
2011 | T22 | iPad |
2045 | T23 | iPad |
2011 | T102 | Laptop computer |
2045 | T220 | iPhone |
2045 | P44 | Projector |
2045 | H55 | 32GB flash drive |
Establish a Relationship Between the Tables
When the Show Table dialog box appears, click on each table name in turn, click Add, and then click Close. Drag the EmployeeNumber field from the Employees box, and drop it on top of the EmployeeNumber in the Electronics box. The Edit Relationships dialog box will open when you do this. Select the Enforce Referential Integrity checkbox, and click Create.
Create a Form to Enter and View Data
Now that the two tables are linked, you can create a form that will make it easy to add employees and devices. This is the point at which you will see the true power of Access compared with Excel.
On the Ribbon toolbar, click Create, Form Wizard. When the Form Wizard dialog box appears, select Table: Employee from the Tables/Queries menu, and click the double-arrow button to add all the fields to the Selected Fields list. Next, in the Table/Queries menu, click the Table: Electronics entry, click IDcode, and click the single arrow. Finally, click Description, click the single arrow, and then click Next.
Your form will open on the screen, showing details for the first employee in the Employees table as well as all the electronic devices that have been assigned to that person. You can move from one employee to the next using the navigation tools at the foot of the screen. Here too is a ‘New (Blank) Record’ button that you can click to add a new employee. You’ll find similar navigation and new-record buttons at the foot of the subform, which shows details of the electronic devices associated with each employee. When you add a new electronics item, Access will associate it with the current employee automatically.
Harness the Power of Relational Databases
This simple example gives you an indication of the power of relational databases in general, and Access in particular. If one employee has 50 electronic devices checked out, and another staffer has just one item, the database you’ve created will show detailed information about each person’s devices in an easy-to-understand table view. You can easily navigate from one employee to the next, instantly see the electronic devices they’ve been assigned, and even add and delete data using the form.
Of course, there’s a lot more to Access than what I’ve exposed here; but as you become familiar with the basics, you’ll recognize many opportunities where databases can be a boon to your business. That said, some small businesses need databases that are so complex that they’ll have to ask a specialist to build them. A professional database designer can build interactive query screens, assemble complex reports, and incorporate features that will protect the integrity of your valuable data.
When you purchase through links in our articles, we may earn a small commission. This doesn't affect our editorial independence.