Chapter 1

Access Basics

IN THIS CHAPTER

Deciding when to use Access

Discovering what's new in Access

Unlocking the basics of working with Access

Figuring out how to get started

Access, the one, true database application within Microsoft Office, has always been a powerful program. As a result, you're probably reading this book because all that power makes Access an application that's not so easy to learn on your own. You're not alone in that feeling, because Access is also unique within Microsoft Office in that most people can't just fire it up and start using it the way you might have done with your first Word document. So having this book by your side is a good choice.

That said, with the foundation components of Access, and the key functionality that you'll discover in this book, you'll be able to put Access through many of its most important paces. You'll be working with wizards and other onscreen tools that keep you at a comfortable arm's distance from the software's inner workings, the things that programmers and serious developers play with, but you'll be harnessing real power. Hope you're feeling better now!

Please don't panic after reading that reference to "real power." You don't have to use every feature and tool and push the edges of the Access envelope to build a really solid database. In fact, you can use very little of everything Access has to offer and still create quite a significant solution to your needs for storing and accessing data - all because Access can really "do it all" - enabling you to set up a database quickly, build records into that database, and then use that data in several useful ways. Later on, who knows? You may become an Access guru, if that's your desire. And this book can be a great start in that process, too.

In this chapter, you'll discover what Access does best (and when you might want to use another tool instead), and you'll see how it does what it does, and hopefully you'll begin to understand and absorb some basic terminology.

Of course, nobody's expecting you to memorize tons of complex vocabulary or anything scary like that. We would never do that to you. Rather, the goal here (and in the next two chapters) with regard to terms is to introduce you to some basic words and general concepts intended to help you make better use of Access - as well as better understand later chapters in this book, if you choose to follow us all the way to its stunning, life-altering conclusion.

What Is Access Good For, Anyway?

What is Access good for? That's a good question. And, happily, the list of what you can do with it is a lot longer than the list of what you can't do with it. When it comes to data organization, storage, and retrieval, Access is at the head of the class, no matter what Excel aficionados will tell you - and even if you've been using Excel to sort and filter your lists and thought that was all you need.

Building big databases

Okay, what do I mean by big database? Any database with a lot of records - and by a lot, I mean hundreds. At least. And certainly if you have thousands of records, you need a tool like Access to manage them. Although you can use Microsoft Excel to store lists of records, it limits how many you can store (no more than the number of rows in a single worksheet, which is just over 1 million, but Excel will run poorly if you get anywhere near that number), and it wasn't designed to create a true database. Overall, Excel wasn't designed to create the kind of tools - things like forms and reports - that Access can build. So anything with a lot of records and complex data is best done in Access.

Below are some reasons why Access handles big databases so well.

• Typically, a big database has big data-entry needs. Access doesn't just offer forms, but form-building features that enable you to create a quick form through which someone can enter many records, quickly and easily. This creates efficiency and ensures accuracy. (Check out Chapter 8 for more about building forms.)
• When you have lots and lots of records, you also have lots of opportunities for errors to creep in. This includes duplicate records, records with misspellings, and records with missing information - and that's just for openers. So, you need an application like Access to ferret out those errors and fix them. (Chapter 10 lays out how you can use Access to find and replace errors and search for duplicate entries.)
• Big databases mean big needs for accurate, insightful reporting. Access has powerful reporting tools you can use to create printed and onscreen reports - and those can include as few or as many pieces of your data as you need, drawn from more than one table if need be. You can tailor your reports to your audience, from what's shown on the reports' pages to the colors and fonts used.
• Big databases are hard to wade through when you want to find something. Access provides several tools for sorting, searching, and creating your own specialized tools (known as queries) for quickly finding the elusive single record or group of records you need.
• Access saves time by making it easy to import and recycle data. You may have used certain tools to import data from other sources - such as Excel worksheets (if you started in Excel and maxed out its usefulness as a data-storage device) and Word tables. Access saves you from reentering all your data and allows you to keep multiple data sources consistent.

Creating databases with multiple tables

Whether your database holds 100 records or 100,000 records (or more), if you need to keep separate tables and relate them for maximum use of the information, you need a relational database - and that's Access. How do you know whether your data needs to be in separate tables? Think about your data - is it very compartmentalized? Does it go off on tangents? Consider the following example and apply the concepts to your data and see if you need multiple tables for your database. Hint: You probably do!

The Big Organization database

Imagine you work for a very large company, and the company has data pertaining to their customers and their orders, the products the company sells, its suppliers, and its employees. For a complex database like this one, you need multiple tables, as follows:

• One table houses the customer data - names, addresses, phone numbers, and email addresses.
• A second table contains those customers' orders, including the name of the customer who placed the order, the products they ordered, the salesperson who handled the sale, shipping information, and the date of the order.
• A third table contains information on the products the company sells, including product numbers, supplier names, prices, and the number of items in stock.
• A fourth table contains supplier data - about the companies from which the main organization obtains its inventory of products to resell to customers. The table contains the company names, their contact person, and the address, email, and phone-number information to reach them.
• A fifth table contains employees' data - from the date they were hired to their contact information to their job title - and also contains notes about them, sort of a summary of their resumes for reference.

Hopefully, as we listed those tables, you could see the way they'd be conceptually connected to each other - customers, orders, products, suppliers, and employees - and can therefore see how relationships between those tables would help the tables literally work together.

Other tables exist, too - to keep track of shipping companies and their contact information (for shipping customer orders), expenses (for the expenses incurred in running the business), and other tables that are used with the main five tables. The need for and ways to use the main tables and these additional tables are covered later in this book, as you find out how to set up tools for data entry, relate your tables, look up records within your tables, and create reports that provide varying levels of detail on all the data you've stored.

Because you don't have to fill in every field for each record - in any table in the database - if you don't have a phone number or don't know an email address, for example, it's okay to leave those fields blank until you've obtained that information.

Fail to plan? Plan to fail

Like just about any undertaking, at least considering the steps involved and the desired outcome is a good idea before you get started. Your database is no different, and has even greater needs for effective planning. If you think carefully about your database, how you use your data, and what you need to know about your employees, customers, volunteers, donors, products, or projects - whatever you're storing information about - you can plan

• How many tables you'll need
• Which data will go into which table
• How you'll use the tables together to get the reports you need

Of course, everyone forgets...