How to Build Your First Database With Microsoft Access

If you own a version of Microsoft Office that includes Access (Office Professional 2010 is the most current version), but you’ve never used it, you’re overlooking a powerful tool for organizing and analyzing business data. I’ll show you how to make the most of this relational database program.

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).

If you were to create this record-keeping system as a list in Excel, you would do so in one of two ways. First, you could allocate one row in a worksheet for each employee and dedicate pairs of columns across that row to contain the description and ID for each asset the employee has been assigned. If one person had 15 devices, for example, you’d make 30 such columns. If one person had only one piece, then you’d need just two columns. Because the amount of data stored for each employee would vary, looking for a particular item in the worksheet wouldn’t be easy. It also wouldn’t be easy to view the data if someone had a lot of equipment, as you would have to set up more columns than would fit comfortably in the Excel window.

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.

Table1 will appear on the screen. Click on Click to Add, and enter details for the first two fictional employees: Type James, press Enter to move to the next column, and type Smith. Press Enter twice to move to the second column of the second row, and type Peta, press Enter, and type Harrison.

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 in Design View mode, type EmployeeNumber in place of the ‘ID’ field name. Press the Tab key, and in the Data Type drop-down list, choose Number. Change ‘Field1’ to read FirstName and ‘Field2’ to read LastName.

Return to Datasheet View by clicking View, Datasheet View. Click Yes when prompted to save the table, and type the numbers 2011 and 2045 in the first column of the table.

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.

Click anywhere in the EmployeeNumber field in the table, and click the Primary Key button on the Ribbon toolbar to remove the Primary Key setting from this field.

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:

Establish a Relationship Between the Tables

So far, the database contains two tables of related data. Now it’s time to link the tables together. First, save and close each table by right-clicking each table’s tab and choosing Close (click Yes if prompted). Next, select the Database Tools tab on the Ribbon toolbar, and click the Relationships button.

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.

You should now see a line between the two tables, with a 1 on the Employees side and the infinity symbol on the Electronics side. This line describes a one-to-many link: One employee can have many devices, but each device can be allocated to only one employee.

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.

When you see the ‘How do you want to view your data?’ prompt, click by Employees, choose Form with subform(s), and then click Next. Now, click Datasheet, Next, and Finish to name the forms with the default names and to open the form to view information.

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.