Crabby Office Lady

Access, our database program, has a language all its own. It's not Spanish, it's not French, it's not even Vietnamese (no, it's trickier). But just as I lectured you in all my "demystifying Office terms" columns, the sooner you get on board and learn your vocabulary, the better off you'll be when it comes time to actually use the program.

---

Subscribe to Crabby's columns
Read all the Crabby Office Lady columns
Get the Crabby Office Lady's book

---

In March of this year, I wrote a column about Microsoft Office Access 2007. In this column, I admitted that I had been more reticent than relieved to be writing about this program because I found databases a bit, well, daunting. (Frankly, I'd just as soon install a drip irrigation system for my flower beds.) However, while writing that column, I learned a lot about Access and have used it more and more in my daily work. It's not that scary at all, once you get the hang of it and figure out some of the terminology.

This week's column in my summer "demystifying" series is a great way to introduce you to what some of these basic terms are in Access. And after you have a better idea of what's what, you and I can waltz hand in hand down the path to database nirvana. Everybody say "Om."

Ready, set ... define those terms!

Once you understand a language of a country, a program, or even your 2-year-old son, you can interact a whole lot easier with those who speak the language. Access is no different. Getting a few of its most common terms under your belt makes it a whole lot easier for you to get started working on your databases.

Data

Data is the plural of datum, the building block of information. Your data is a collection of facts, records, or even pieces of knowledge all organized in a way that makes sense and is easy to access. If you don't have data, you don't need a database (which makes sense, really).

Database

Now that you have your data, you have to organize it in ways that make sense to you. Hence, you create a database, a collection of all the data that is relevant to the work you're going to do. Then you can ask it questions by using a program (in our case, Access).

For example, if you have a huge list of all of your customers, their orders, the date they placed orders, their costs, your costs, and so on, you can organize it all to make sense. If you want to see how Customer X's orders this year compared with those of last year, you can look at just that aspect of the information by using your database. And if you want to see how your costs compare with his, you can get the information for that, too. It's a lot easier to do this using a database than to try to find all that information by hand. That's the point of a database: It does the work for you.

Some examples of common databases are the following:

• Telephone books
• Library catalogs
• International Genealogical Index (the largest collection of free family history, family tree, and genealogy records in the world)
• Club membership lists
• Your list of contacts in Outlook

Database object

In Access we talk a lot about objects. An object can be a table, a macro, a query, a form, a report, or a page. Without objects, your data would be useless. I like to think of database objects as the pots, pans, plates, cups, and utensils. If you don't have those things, the food in your pantry and refrigerator — unless all you have is raw snack food — is useless. You have to have a way to work with the things you have — make sense of them, turn them into something useful (or in this case, edible). And, just like cooking, different scenarios need different objects. For example, you may need to create a report object so that you can get an idea of how many of your customers are ordering a specific product during a specific time period. Or perhaps you want to create an AutoCorrect object so that certain names or spellings that you constantly misspell are corrected for you automatically.

Access Objects

Table

In almost every Office program are tables. In general, tables allow you see your information in an organized way, and in Access that is exactly how a table works, too. In Access a table is a type of object that stores your data in records (rows) and fields (columns). Your table contains data about a particular subject, such as orders and products, or employees and performance ratings. Each record in a table contains information about one item, such as an employee, and a record is made up of fields, such as name, address, and phone number.

Some examples of tables that you can create are:

• A client table that lists the names of your clients and the type of products they order.
• A catalog of products you sell, which may include the prices and photos of the products.
• A list of inventory, where it's stored, and how much is on hand.

Datasheet

A datasheet is your data from a table, a form query, or stored procedure that's displayed in a row-to-column format. When you open a table or take a look at the results of a query, it's a datasheet that you see (in Datasheet view). It's the visual representation of the information you want to see. This is an example of an employee table in Datasheet view:

Create a simple report by formatting a datasheet

Query

A query is either a request or a question that you "ask" Access about the data stored in your tables. It brings together your data so that you can create a form or a report. Using the table examples above, you can do the following:

• Run a select query about which products fall into a specific price range, or run a query to see which customers order the same products.
• Perform an action query that allows you to add, change, or delete data from your table.

Introduction to queries

Forms and reports

A form is a database object that helps you control the access to your data, such as which fields or rows of data are displayed. It's a way for people to be able to see "into" your database. You set up a form so that people — including you — can find what they're looking for quickly. And if you or others are going to be adding more information to the database, setting up a form can ensure that no incorrect data will be entered.

A report is similar to a form — it shows you just the type of information you want shown. It's also what you get after you run a query. It can be printed and contains information that's organized and formatted as you want it. A phone list is a report, and so is a collection of mailing labels.

Create a form by using the Form tool

Create a simple report

Expression

You know that look on your face that you get when your computer has crashed and you didn't back up your files? Yes, that is a type of expression. And you know the phrase you shout out to the person on the road who just cut you off? That's an expression, too (as is the gesture she makes with her fingers out her car window as you honk loudly at her).

However, these are not the expressions we're talking about today. In Access (and frankly, any other database) an expression is a combination of mathematical or logical operators, constants, functions, table fields, controls, and properties that evaluates to a single value. You can use expressions to calculate values, validate data, and set a default value for a field or control.

Got that? No? (Me neither.) Okay, let's put it differently so that you and I can actually understand.

An expression is simply a formula. If you're familiar with Excel, you know what a formula is. You use formulas and expressions to do a number of things, for example, perform a calculation (the value of the cost of a product plus tax) or combine or manipulate text (combine the FirstName field, a space character, and the LastName field).

So, as you can hopefully understand now, an expression in Access is merely like a formula — you write the expression (or formula), and Access brings you the answer you want.

Create an expression

Filter

If you have a gigantic database with a million (literally) records contained in it, finding a particular one — or a few particular ones — can be a daunting thought. Good thing you can use the filtering capabilities to reduce this huge amount of information to just what you actually need.

A filter will limit — or sort — a view so that you can home in on the information you need. You don't have to alter the design of your query, form, or report; you don't have to create a smaller one; and you don't have to make your eyes bleed by trying to scan hundreds of pages. You just need to apply a filter to the data to show only the types of records you want.

For example, if you want to take a look at all of the 72,456 employees who have a specific area code, you can filter the records in the Employees table where the Phone Number field starts with 425. It's that simple.

Limit the number of records in a view with filtering

Default control style and default value

Anything that has a default, as we already know (because we've all read the several Crabby columns that explain what default means), can save us time.

In Access, controls (such as text boxes, labels, or check boxes) have properties and styles (size, for example). To make these controls useful, you have to enter a value into them (yes; no; maybe; not till the hot place freezes over).

The default property is something you can set for a control so that each time you create a new control of that type, the property will have the same value.

The default value is related: When you add a new record that contains a field or control, the default value is the value that is entered into that field or control automatically.

 Note   If you read this fast, it's hard to tell the difference between these two, but this is what I came up with: A control's properties have to do with its size, font color, and so on, while the value (which can relate to either a field OR a control) has to do with the actual value of the content within the control or field itself.

This makes things easier on you if you're adding a lot of records that all have similar controls or fields. And if you have a default setting for the value, you know that there will always be something in that field or control — it will never be a blank space, even if you forgot to put the real value in.

Final words

Hopefully, you now have a firmer grasp (or at least a grasp of some sort) of what to expect when you begin to delve into Access. There is a LOT more information on Office Online, so please head on over to the Access area of the site and read some articles, take a training course or two, view a few demos, and go on your way.

Next week I'll cover Publisher terms, and it will be my last column in this "demystifying" series of the summer. I hope that you enjoyed reading these as much as I enjoyed writing them. From the comments I received from you, I guess these columns did some of you some good.

"I was brought up to believe that the only thing worth doing was to add to the sum of accurate information in the world." — Margaret Mead

About the author

Annik Stahl, the Crabby Office Lady columnist, takes all of your complaints, compliments, and knee-jerk reactions to heart. Therefore, she graciously asks that you let her know whether this column was useful to you — or not — by entering your feedback using the Was this information helpful? tool below. And remember: If you don't vote, you can't complain.

Subscribe to Crabby's columns
Read all the Crabby Office Lady columns
Get the Crabby Office Lady's book