Why Not Just Use Excel?

Many people learn to use spreadsheets at the very start of their computer careers. Of course, Microsoft Excel is the premiere spreadsheet today, offering fabulous flexibility and ease of use. So, why not just keep all your data in Excel? The spreadsheet in Figure 1.1 shows just a few of the problems that crop up when you try to use a spreadsheet like a database.

Figure 1.1. Data stored in an Excel spreadsheet.

Even though it looks like this spreadsheet is doing a good job of tracking customers and orders, there are four problems with it:

• In cell A17 (that is, the cell in the first column of row 17), the customer's name is spelled "Haneri Carnes." Everywhere else, the customer's name is spelled "Hanari Carnes."

• In cell C21, July is spelled out. This is inconsistent with the other cells , where it's abbreviated.

• In cell D5, the product name is "Manjimp Dried Applies." In cell D7, the product name is "Manjimup Apples." Which one is right? Or are these two different products?

• To see the fourth problem, put yourself in the shoes of the person maintaining this spreadsheet. Each time an order comes in, you need to type the company name, over and over again, along with the city and any other information (such as a telephone number or address) that you're tracking for customers. No wonder there are typos in the customer name!

The basic problem is that Excel does not have good facilities for checking data integrity. If you type a name incorrectly or enter a date that isn't really a date, Excel won't tell you. The problem is more than just cosmetic. You might have heard the expression "garbage in, garbage out." Suppose your boss asks you to find out how many products have been ordered by Hanari Carnes. If you search the spreadsheet for that customer name, you'll miss the row in which the name is spelled incorrectly.

Without going into any depth, we can tell you right now that Access has solutions for all these problems:

• Access stores each piece of data (such as a customer name) in just one place. You can't type the name the wrong way because you're not constantly retyping it.

• Access can force some pieces of data to match particular patterns. For example, you can make sure that only valid dates are entered as order dates and that the same format is used for every date.

• Access can easily check a set of data to find all the different values it contains. This makes spotting typos easier.

• Access can help you build user -friendly data entry interfaces (called forms in Access). Instead of typing the customer name for every order, you can just select the name from a list onscreen.

Tip

What happens if you want to store your data in Access and work with it in Excel? No problem! When you want to take advantage of one of Excel's features (such as its flexible charting interface), you can export Access data to Excel. You'll learn how to do this in Chapter 16, "Sharing Data."

There's another reason Access is a better choice than Excel for many business uses. If you've ever tried to share an Excel spreadsheet with another user, you know that only one person can edit an Excel spreadsheet at a time. If two people want to use the same spreadsheet, they have to wait and take turns. Not so with Access! Access is designed for multiuser scenarios straight out of the box. If you need to type in inventory information while another user edits product names in the same database, there's no problem. More than one user can even access the same types of data at the same time. If a user happens to request a record that's already in use, he might have to wait a second or two until the first user is done. For the most part, many users can happily work with the data at the same time.

Note

Access is an example of a desktop database designed for one user, a workgroup, or a small department. In theory, an Access database can accommodate 255 users simultaneously . In practice, you'll probably find acceptable performance until you get up to around 50 users.