How to Build Your First Database With Microsoft Access

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

How to Build Your First Database With Microsoft Access
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.

Access offers templates to quick-start a project; if you prefer, however, you can choose 'Blank database' to create one from scratch.
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 you've typed some data, you can fine-tune the design by moving to Design View.
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.

Use Access's Design View to set up your table design with field names and data types to describe the items that will be stored in each field.
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

Subscribe to the Best of TechHive Newsletter