Access Database Lingo

Now, if the section heading (“Database Lingo”) is making you panic because you think I want you to memorize a bunch of database jargon, don’t worry. Just relax. Breathe normally. The next section, and many throughout this chapter, simply uses some terms you need to know so you can figure out what Access is referring to in its various dialog boxes as well on the various tabs it uses to give you access to commands in the Access workspace. Knowing these terms will, therefore, help you get around and get things done in Access.

Unfortunately, you simply must know technical terms — there are no two ways about it. I’m talking about only a handful of words, though — some of which you probably already know and maybe even use in reference to information in general — words like record and database. See? Nothing high tech, just some basic words and concepts you really need to absorb so you can move on and use Access effectively.

The terms in this section appear in size order, starting with the smallest piece of a database — the data — and advancing to the largest — the entire database itself. I’ve done it this way so you get the big picture, a little bit at a time, and see that the big picture is made up of smaller items. Seeing how they all fit together (and what to call each piece) is what this chapter’s all about.

Data, no matter how you pronounce it

Data is the stuff that Access stores. Information you may store in your head one way will be stored in a different way in a database program like Access. For example, you may think of someone’s name as John Smith, or you may only ever think of the guy as John — either because you don’t know his last name or because you never use his last name. A database, however, stores his name as either John Smith, in a field called Name, or as two pieces — Last Name (Smith) and First Name (John). The latter approach is best because it gives you more freedom to use the data in more ways. You can sort the data by Last Name, for example, which is hard to do if you’ve just stored the entire name as one chunk.

Get the idea? As mentioned in Chapter 1 (where you plan out your database), it’s a good idea to break down the data as much as possible. No matter how you pronounce it — “day-tah” or “dat-tuh” — it’s your information, and you want to be able to get at it in the simplest, most logical way possible. As you read on in this chapter, and when you review Chapters 1 and 2, you’ll see that Access gives you all the tools you need to do just that — it’s just a matter of using the right tools at the right time!

Fields of dreams (or data)

Because people don’t want their data to wander around homeless, the technical wizards created fields — places for your data to live. Each field holds one kind of data. For example, to track information about a baseball card collection, your fields might include Manufacturer, Player Name, Position, Year, Team, and Average (or ERA, for pitchers). If you have a name and address database, your fields might consist of Last Name, First Name, Middle Initial, Address1, Address2, City, State, Zip, Phone, Cell, and Email. When you think about it, it’s pretty logical. What are all the things you can know about a baseball player? A client? A product? These things become fields.

As with the term data, other database programs (such as FoxPro and FileMaker) all agree on what a field is. In larger database packages, however (such as Oracle and Microsoft SQL Server), you find the term column replacing field. And to make things more exciting, Microsoft Excel stores your fields in columns when you use an Excel spreadsheet to store a list. The tabular structure of a database table is what leads Oracle and SQL to refer to columns rather than fields, but for heaven’s sake — couldn’t they have stuck to a term we all know?

Records

Having fields is a good start, but if you stop there, how do you know which last name works with which first name? Something needs to keep those unruly fields in order — something like a record. All the fields for one baseball card — or one client or one product — are all collectively known as a record. If you have two baseball cards in your collection, you have two records in your database, one for each card. Fifty clients? Fifty records.

For a little more about records, check out the following:

• Each record in a table contains the same fields but (usually) has different data in those fields. And not every record must have data in every field. If someone doesn’t have a cell phone, you can’t very well have any data in the Cell field for that person, right?
• A single record contains all the information you need about a single item (accounting entry, recipe, or whatever) in your table. That’s all there is to it.

Tables

A table is a collection of records that describes similar data. The key phrase to remember in that last sentence is similar data. All the records in a single table contain fields of similar data. The information about that baseball card collection may fit into a single table. So would the client or product data. However, a single table would not handle both baseball cards and clients because they’re unrelated databases. You wouldn’t put the records for your car’s repairs in the folder where you keep your Christmas cookie recipes, right?

Why? Because if anyone else needs to know when you last had the tires rotated, they aren’t going to know to look in the same place one finds the best recipe for Ginger Snaps. You might remember that they’re stored in the same place, but it’s just too confusing for anyone else. And too limiting. Access lets you write reports and queries based on your data, and if the data in your database isn’t all related, it’ll be chaos trying to write a report or generate a query that pulls data from that database. You could end up with a recipe that calls for motor oil or a maintenance schedule that tells you to preheat the car to 350 degrees. Such a report might be amusing, but it’s hardly useful.

The database

An Access database, or database file (the terms are interchangeable), is a collection of everything relating to a particular set of information. The database contains all the tables, queries, reports, and forms that Access helps you create to manage and work with your stuff. Instead of storing all those items individually on the disk drive — where they can become lost, misplaced, or accidentally erased — Access groups them into a single collective file.

Here’s an important point: All those parts — the tables, the reports, queries, and forms — cumulatively make a accesss database. And that’s before you even enter any records into the tables. The database, therefore, is more than the data; it’s the tools that store, manipulate, and allow you to look at the data, too.