In This Chapter

Getting to know some basic terms and concepts

Outstanding in your field(s)

Deciding to go flat-file or relational with your database

Getting a table started

This may be the single most important chapter in this book. That probably sounds like a strong statement, but it’s really true. When you want to figure out how to actually do something, it’s essential to understand not just how it works, but why it works that way, and what’s going on behind the scenes. So after you’ve read about why Access is the right tool for you (in Chapter 1) and how to get around in the Access interface (Chapter 2), it’s time to really nail down how Access works and how to start building your database.

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 use some terms you need to know so you can figure out what Access is referring to in its various dialog boxes as well as 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.

Field Types and Uses

A field, you remember, is where your data lives. Each field holds one piece of data, such as Last Name or Batting Average.

Because there are so many different kinds of information in the world, Access offers a variety of field types for storing it. In fact, Access puts the following field types at your disposal:

• Short Text
• Long Text
• Number
• Currency
• AutoNumber (this data type is applied, by default, to the starting ID field in any new table)
• Date & Time
• Yes/No
• Lookup & Relationship
• Rich Text
• Attachment
• Hyperlink
• OLE Object
• Calculated
• Lookup Wizard

The types just listed are those available for fields you create in addition to that first field — the ones that will contain your data.

For now, suffice it to say that the aforementioned Autonumber field is a field that contains an automatically generated number so that each record is unique in that it has a unique autonumber, or ID. You get the word about the need for (and ways to create) unique fields later on, in Chapter 4.

As for the Lookup Wizard, we also get to that one much later in the book, in terms of its actual use. But you’ll find out more about when and how this data type is used later on in this very chapter!

For now, don’t worry about figuring out what each field type is or what it does based on its name — I go over each one shortly. As you can see, though, the list covers just about any type of data you can imagine. And remember, each one can be customized extensively, resulting in fields that meet your needs exactly. If you absolutely cannot wait to find out about modifying all the specs for your fields, Chapter 4 should be your next stop.

The upcoming bulleted list introduces the available field types and how they’re used. You’ll also find out a little bit about how you can tweak them to meet your specific needs:

• Short Text: Stores up to 255 characters of text — letters, numbers, punctuation, and any combination thereof.

• Long Text: This replaces the Memo field type found in versions 2010 and previous. A Long Text field holds up to 64,000 characters of information — that’s almost 18 pages of text. This is a really big text field. It’s great for general notes, detailed descriptions, and anything else that requires a lot of space.

  Numbers in a text field aren’t numbers to calculate with; they’re just a bunch of digits hanging out together in a field. Be careful of this fact when you design the tables in your database — you don’t want to enter, say, a value that you intend to use in a Calculated field or to extract some other kind of information from a report and have that value stored as text, rendering it inoperable as a number. If the data is numeric, store it that way.

  Text fields have one setting you need to know about: size. When you create a text field, Access wants to know how many characters the field holds. That’s the field size. If you create a field called First Name and make its size 6, Joseph fits into the field, but not Jennifer. This restriction can be a problem. A good general rule is to make the field a little larger than you think you need. It’s easy to make the field even larger at some later point if you need to, but it’s potentially dangerous to make it smaller. Surgery on fields is covered in Chapter 4.

• Number: Holds real, for-sure numbers. You can add, subtract, and calculate your way to fame and fortune with number fields. But if you’re working with dollars and cents (or pounds and pence), use a currency field instead.
• Currency: Tracks money, prices, invoice amounts, and so on. In an Access database, the buck stops here. For that matter, so do the lira, the mark, and the yen. If you’re in the mood for some other kind of number, check out the Number field.
• Date/Time: Stores time, date, or a combination of the two, depending on which format you use. Use a date/time field to track the whens of life. Pretty versatile, eh?
• Yes/No: Holds Yes/No, True/False, and On/Off, depending on the format you choose. When you need a simple yes or no, this is the field to use.
• Lookup & Relationship: If you want a field within one table to actually display content from a field in another table, choose this as the field type. A simple Lookup Wizard opens as soon as this field type is chosen, through which you select the table and field to look up through this new field in your table.
• Rich Text: Need the content of a particular field to be formatted just so? Choose this field type, and the formatting applied to the data in the field (using the Text Formatting tools on the Home tab) will be how it appears onscreen and in reports.
• OLE Object: You can use the OLE Object data type to link or embed an object — such as an Excel worksheet or Word document — to an Access Table.
• Attachment: Use this field type to attach files — Word documents, Excel worksheets, PowerPoint presentations, or any other kind of file, including graphics (a photo of the volunteer, product, or location, perhaps?) — to the record.
• Hyperlink: Thanks to this field type, Access understands and stores the special link language that makes the Internet such a powerful place. If you use Access on your company’s network or use the Internet extensively, this field type is for you. You’ll find out more about hyperlinks and other neat ways Access and the Internet play well together in Chapter 11.
• Calculated Field: Use this field type when you want to fill the field in question with the result of a formula that uses one or more other fields in the same table. For example, in a table that contains a list of your products, other fields might include Price and Discount. If you want to also have a field that calculates the new price (the Price, less the Discount), you’d make that a Calculated field. When you choose this as the field type, you use a submenu to choose what kind of data will house the result, and then an Expression Builder dialog box appears, through which you set up the formula.

To help you start thinking about your database and your data and to begin imagining the fields you could use for some common types of data, Table 3-1 presents a breakdown of field types and ways you might use them.

All the field types listed as samples in Table 3-1 are really text fields, even the ones for phone numbers. This is because Access sees their content as text rather than as a number that could be used in a calculation. (Check out Table 3-2 for field-naming no-nos.)

Of course, another field type (listed in the Type column) is neither a Short Text nor Long Text field — you also see the Hyperlink field. This data type is also considered text, but the Hyperlink data type stores URLs, as URLs — not just as a string of text and punctuation.

If all this text versus numbers stuff is confusing you, remember that computers think there’s a difference between a number (that you’d use in a calculation) and a string of digits, such as the digits that make up a phone number. When it comes to different kinds of text fields, it’s a matter of how much text will be stored in the field, and if it needs any special formatting in order to work properly in the database.

Choosing Between Flat and Relational Databases

Unlike ice cream, databases come in just two flavors: flat-file and relational. Also unlike ice cream, it’s not really a matter of preference as to which one you choose. Some databases require a relational approach; others would be overwhelmed by it. Read on to figure out how to tell the difference.

Building a Database

So you’ve read a few chapters here at the beginning of the book, maybe you’ve leafed ahead where I’ve referred to other chapters, and now you feel ready. You want to dive in and start building a database. Keeping in mind my previous advice to take it slowly, you can take a whack at it here.

In the following procedure, you set up a new database and then use the Table Wizard to build the first table in the database. Ready? Here we go …

1. If Access is not already running, take a moment to start it.

   Chapter 1 shows you how to do this.

   In the Access workspace, a series of large template icons appears, below a Search for Online Templates box, accompanied by links to likely searches for templates that store Assets, Business, Contacts, Employee, and so on.

2. Click the Blank Desktop Database icon.

   A Blank Desktop Database dialog box appears, as shown in Figure 3-1.

3. Type a name to replace the generic DatabaseX (where X is the number assigned chronologically to the database).

   You don’t need to type a file extension (.accdb); Windows 7 and 8/8.1display your extensions automatically. Also, if you accidentally delete the file extension while changing the filename, don’t worry — Access adds it to the filename you type.

4. If you don’t like the folder that Access picked out for you, click the little folder icon and choose where to store the new database.

   As shown in Figure 3-2, when you click that little folder icon, the File New Database dialog box opens. From here, you can navigate to anywhere on your local system or on a network to which you’re connected and select the drive and folder on which to store your new database. When you’ve finished selecting a spot for your new database, click OK to return to the workspace.

5. Click the Create button.

   A blank table, called Table1, appears in the central section of the workspace, and on the left, a panel lists the parts of your database (there’s just one part so far). Figure 3-3 shows your new table and the left-hand panel.

   When you click Create, if a dialog box pops up and asks whether you want to replace an existing file, Access is saying that a database with the name you entered is already on the disk.

   • If this is news to you, click No and then come up with a different name for your new database.
   • If you intended to replace that old database with a new one, click Yes and proceed.
6. Create and name your fields in the table by double-clicking where it says Click to Add at the top of the second column in the table.

7. Click the arrow to the right of the words Click to Add and choose the type of field you want to add.

   The many choices are discussed earlier in this chapter. For most fields, Text will be the type, but your data and its nature (and your desired uses for it) will dictate what’s best to choose here.

   What’s that ID field in the first column? It’s there by default and will contain a unique number for each record you create (when you start entering records, later). This provides the unique field that each table requires, especially if you’re going to relate your tables. You can change its name by double-clicking the name “ID” and changing it to, for example, Customer Number.

   Later on, after you’ve set up your tables and established relationships between them, you can reassign what’s known as the primary key (another name for a unique field in a table), and at that point, if you want, the ID field can be removed.

8. Type a new field name (to replace the highlighted placeholder name), and press Enter to save the new field name.

   As soon as you press Enter, a new field appears, with a blank at the top, awaiting a name.

   Repeat Steps 7 and 8 until you have all the fields you think you’ll need in this table. You can always rename them later (by double-clicking the current names), so don’t worry about perfection at this point. Just start setting up fields so you can start entering data. Figure 3-4 shows a new field name in place and a new one awaiting the Enter key to confirm it.

9. To save your new table and the entire database, press Ctrl+S or click the Save button on the Quick Access Toolbar.

   It’s a good idea to save each time you’ve done something important — building a table, updating some fields, adding records, and so on — essentially after anything you’d hate to have to do over again.

Rarely is “Table1” a really useful name for a table. Before or after saving your database, renaming a table is easy. Just follow these steps:

1. Right-click the Table tab.
2. Choose Save from the pop-up menu that appears.
3. Type a name for the table in the resulting Save As dialog box.
4. Click OK to keep the name.
5. Resave your database to include this change.

Adding and Removing Tables

Nobody’s expecting perfection at this stage of the game. Certainly not in your first foray into database creation, and not even on your second or third attempt. Even seasoned experts forget things now and then, realizing after they’ve built a table that they didn’t need it, or after they’ve started setting up reports and queries that they’ve forgotten a table that they needed. It can happen to anyone.

What to do? Use Access’s simple interface to add the tables you want and delete the tables you don’t.

One more, please

If, after you start building your database, you decide that your database warrants more than one table — in other words, if you realize you need a relational database — then you need to add another table. If you already knew that your database was going to need multiple tables, then — after building the first one — the only thing to do is build the rest, one by one.

To add new tables to an existing database, repeat the following steps for each new table:

1. Click the Create tab on the Ribbon.

   The Create tab’s buttons appear, as shown in Figure 3-5.

2. Click the Table button on the Ribbon.

   A new table, blank and awaiting the name for the first field, appears, as shown in Figure 3-6.

3. Build and name the fields for this new table as shown in the previous procedure.

   Save your database periodically as you work.

4. Continue adding tables, using Steps 1 through 3 for as many tables as you need in the database.

   You don’t have to do this perfectly from the start — you can always go back to rename fields and add or remove tables (more on how to do that in a second). The goal here is to just do it — just get started and get the database going so you can see what you have and start working with it.

Naming tables is important — because you’re going to need to know, at a glance at that left-hand panel, what’s in Table1 or Table2 or Table3, right? Better to name them Customers, Orders, Products, and so on, so you don’t have to remember each one by a generic number. To name a table, you can do so when you first close it and are prompted to save it. As shown in Figure 3-7, the Save As dialog box gives you a Table Name box. Type the name and press Enter. If you decide you don’t like the name later on, simply right-click the name it currently has, as displayed in the left-hand panel, and the current name is highlighted. Type the new name, and press Enter to confirm it. You can also choose Rename from the menu that appears if you right-click the table’s name in the left-hand panel that lists your database components. This also gives you the opportunity to type a replacement name.

Oops, I didn’t mean to do that

So you have a table you didn’t want. Maybe you realize after building Table C that you really only need Tables A and B — or that Table D, which you’ve also created, really makes Table C unnecessary. Whatever the reason, tables, even ones with records in them, are easy to get rid of.

Let me state that again: Tables are easy to get rid of. Perhaps too easy. Before you delete a table, check and recheck your database to make sure you aren’t deleting information that you need to keep. When a table is deleted, all connections to it — including all relationships and references in queries and reports — are deleted, too. A prompt appears when you choose to delete a table, reminding you of this.

Still committed to ditching the table? Here’s how it’s done:

1. With your database open, look at the panel on the left side of the workspace.

   You should see a list of your tables in that panel, as shown in Figure 3-8.

2. Right-click the table name in the panel on the left side of the workspace, and choose Delete from the pop-up menu, as shown in Figure 3-9.

3. Click Yes in response to the resulting prompt if, in fact, you do want to delete the table.

   All gone!

Now, you probably think it’s time to start entering records, but no, I don’t advise doing that yet. Before you start populating your tables with data, it’s a better idea to set up your table relationships, establish the key fields that will connect your relational tables, and define the specs for each of your fields — taking advantage of those field options I mention earlier in this chapter.

Even if your database will be a (relatively simple) flat-file database, you need to iron out the settings for your fields before you start entering data — establishing the rules for entering names, numbers, dates, and so on — so that what you enter is graciously accepted by the fields you’ve set up.

Chapter 4 helps you prepare your database for its relational duties. In Chapter 5 and Chapter 6, you get a handle on customizing your fields to suit your needs. After that’s done, you can enter your data and begin taking advantage of Access’s forms, queries, and reports — all the stuff covered in the rest of the book!