Creating a database table from scratch

Creating a table from scratch entails creating the table and then entering the fields one by one. After you open a database file, follow these steps to create a database table from scratch:

1. Go to the Create tab.

2. Click the Table Design button.

   The Design window appears. From here, you enter fields for your database table. I hate to be like a City Hall bureaucrat who gives everybody the runaround, but I can’t help myself. Turn to “Entering and Altering Table Fields,” later in this chapter, to find out how to enter fields in a database table.

3. Click the Save button on the Quick Access toolbar.

   The Save As dialog box appears.

4. Enter a descriptive name for your table and click OK.

   Return to the Navigation pane and you see the name of the table you created. If you don’t believe me, click the Tables group to see the names of tables in your database.

Creating a database table from a template

If you know your way around Access and know how to modify database tables, you can do worse than create a database table with a template. Access offers four template types: Contacts (for storing contact addresses and phone numbers); Issues (for prioritizing issues); Tasks (for tracking projects, their status, and when they are due); and Users (for storing email addresses). As well as creating a table, Access creates ready-made queries, forms, and reports to go along with the table. After you create a table with a template, you can remove fields that you don’t want. Deleting fields is always easier than adding new ones.

Follow these steps to use a template to create a table (and accompanying queries, forms, and reports):

1. Close all objects if any objects are open.

   To close an object, click its Close button or right-click its tab and choose Close on the shortcut menu.

2. On the Create tab, click the Application Parts button.

   A drop-down list with options for creating forms and tables appears. (The tables are listed under “Quick Start.”)

3. Choose Contacts, Issues, Tasks, or Users.

   If you have other tables in your database, a dialog box asks whether you want to create a relationship between the table you’re creating and another table.

4. Select the There Is No Relationship option button and click Create.

   Later in this chapter, “Establishing Relationships among Database Tables” explains how to create relationships on your own. If you want to create these relationships now and you have the wherewithal to do it, select an option besides There Is No Relationship, choose a table on the drop-down list, and click the Next button to choose which field to forge the relationship with.

5. On the Navigation pane, right-click the name of the table you created and choose Design View (or click the Design View button in the lower-right corner of the screen).

   In Design view, you can see the names of the fields in the table. If the table contains fields you don't want or you want to change the names of the fields, turn to “Entering and Altering Table Fields,” later in this chapter.

Importing a table from another database

Few things are more tedious than entering records in a database table. If the records you need were already entered elsewhere, more power to you. Follow these steps to get a database table from another Access database:

1. Go to the External Data tab.

2. Click the New Data Source button, choose From Database on the drop-down list, and choose Access on the sublist.

   The Get External Data – Access Database dialog box opens.

3. Click the Browse button, and in the File Open dialog box, select the Access database with the table you need and click Open.

   You return to the Get External Data – Access Database dialog box.

4. Select the first option button (Import Tables, Queries, Forms, Reports, Macros, and Modules into the Current Database) and click OK.

   You see the Import Objects dialog box, as shown in Figure 2-1.

5. On the Tables tab, select the database table you want.

   You can import more than one database table by clicking several table names or clicking the Select All button.

   You can import a table structure — its field names and formats — without importing the data in the table. To do so, click the Options button in the Import Objects dialog box, and under Import Tables, select the Definition Only option button (refer to Figure 2-1).

6. Click OK.

If the table you want to import includes lookup fields, import the tables or queries that the lookup fields refer to as well as the table itself. Without those tables or queries, the lookup fields won’t be able to obtain any values. Later in this chapter, “Creating a lookup data-entry list” explains what lookup fields are.

Creating a field

Create a field on your own or get Access’s help and create a ready-made field. Both techniques are described here. Ready-made fields include fields designed especially for storing currency data, hyperlinks, and date information.

Creating a field on your own

To create a field on your own, open the table that needs a new field and follow these steps on the (Table Tools) Design tab:

1. Switch to Design view if you aren’t already there.

   To switch to Design view, click the Design View button on the status bar.

2. If necessary, insert a new row for the field.

   To do so, click in the field that is to go after the new field, and then click the Insert Rows button on the (Table Tools) Design tab.

3. Enter a name in the Field Name column.

   Names can’t include periods or be longer than 64 letters, but you don’t want to enter a long name anyway because it won’t fit very well along the top of the table.

   Some database programs don’t permit spaces in field names. If you intend to export Access data to other database programs, don’t include spaces in field names. Instead, run the words together or separate words with an underscore character, like this: underscore_character.

4. Press the Tab key or click in the Data Type column, and choose a data type from the drop-down list, as shown in Figure 2-3.

   Data types classify what kind of information is kept in the field. The next topic in this chapter, “All about data types,” explains data types.

5. If you want, enter a description in the Description column.

   These descriptions can be very helpful when you need to reacquaint yourself with a field and find out what it’s meant to do.

In case the name you choose for your field isn’t descriptive enough, you can give the field a second name. The name appears in Datasheet view, on forms, and on reports. To enter a second, descriptive field name, enter the name in the Caption field on the General tab of the Design view window.

Later in this chapter, “Field Properties for Making Sure That Data Entries Are Accurate” demonstrates how to define field properties in the Design view window to make it easier for data-entry clerks to enter the data.

All about data types

To choose a data type for a field, open the Data Type drop-down list in the Design view window and choose a data type (refer to Figure 2-3). Data types are the first line of defense in making sure that data is entered correctly in a table. Try to enter text in a field assigned the Currency or Number data type, and Access tells you that your entry is invalid. You get the chance to fix your mistake as soon as you make it.

Table 2-1 explains the options on the Data Type drop-down list. Choose data types carefully because how you classify the data that is entered in a field determines how you can query the field for information. Querying for a number range is impossible, for example, if the field you’re querying isn’t classified as a Number or Currency field on the Data Type drop-down list.

Designating the primary key field

As I explain in Chapter 1 of this minibook, no database table is complete without a primary key field. The primary key field identifies which field in the table is unique and contains data that differs from record to record. Duplicate values and null values can’t be entered in the primary key field. (A null value indicates a missing or unknown value.) Choosing a primary key field is so important that Access doesn’t let you close a table unless you choose one.

If no field in your table holds one-of-a-kind data that is different from record to record, get around the problem with one of these techniques:

• The AutoNumber data type: Create a new field, give it a name, choose AutoNumber from the Data Type drop-down list (refer to Figure 2-3), and make your new field the primary key field. This way, when you enter data, Access enters a unique number to identify each record in the field. (To generate random numbers instead of sequential numbers in an AutoNumber field, go the General tab of the Design view window, open the New Values drop-down list, and choose Random instead of Increment.)
• A multiple-field primary key: Combine two or more fields and designate them as the primary key. For example, if you’re absolutely certain that no two people whose names will be entered in your database table have the same name, you can make the First Name and Last Name fields the primary key. The problem with multiple-field primary keys, however, is that it takes Access longer to process them, and you run the risk of entering duplicate records.

Follow these steps on the (Table Tools) Design tab to designate a field in a database table as the primary key field:

1. In Design view, select the field or fields you want to be the primary key.

   To select a field, click its row selector, the small box to its left; Ctrl+click row selectors to select more than one field.

2. Click the Primary Key button.

   A small key symbol appears on the row selector to let you know which field or fields are the primary key fields.

To remove a primary key, click its row selector and then click the Primary Key button all over again.

Moving, renaming, and deleting fields

Suppose that you need to move, rename, or delete a field. To do so, switch to Design view and follow these instructions:

• Moving a field: Select the field’s row selector (the box to its left) and release the mouse button. Then click again and drag the selector up or down to a new location.
• Renaming a field: Click in the Field Name box where the name is, delete the name that’s there, and type a new name.
• Deleting a field: Click in the Field Name box, go to the (Table Tools) Design tab, and click the Delete Rows button. You can also right-click the field and choose Delete Rows on the shortcut menu.

A look at the Field Properties settings

Especially if yours is a large database, you’re encouraged to study the field properties carefully and make liberal use of them. The Field Properties settings safeguard data from being entered incorrectly. Following is a description of the different properties (listed here in the order in which they appear in the Design view window) and instructions for using them wisely. Which properties you can assign to a field depends on which data type the field was assigned.

Input Mask

For Text and Date field types, this feature provides a template with punctuation marks to make entering the data easier. Telephone numbers, social security numbers, and other numbers that typically are entered along with dashes and parentheses are ideal candidates for an input mask (another ridiculous database term!). On the datasheet, blank spaces appear where the numbers go, and the punctuation marks stand at the ready to receive numbers, as shown in Figure 2-5.

In the Input Mask text box, enter a 0 where numbers go and enter the punctuation marks where they go. For example, enter (000) 000-0000 or 000/000-0000 to enter an input mask for a telephone number like the one shown in Figure 2-5. You can also create input masks by clicking the three dots beside the Input Mask text box. Doing so opens the Input Mask Wizard dialog box, where you can fashion a very sophisticated input mask.

Validation Rule

As long as you know your way around operators and Boolean expressions, you can establish a rule for entering data in a field. For example, you can enter an expression that requires dates to be entered in a certain time frame. Or you can require currency figures to be above or below a certain value. To establish a validation rule, enter an expression in the Validation Rule text box. To use dates in an expression, the dates must be enclosed by number signs (#). Here are some examples of validation rules:

>1000	The value you enter must be over 1,000.
<1000	The value you enter must be less than 1,000.
>=10	The value you enter must be greater than or equal to ten.
<>0	The value you enter cannot be zero.
>=#1/1/2019#	The date you enter must be January 1, 2019, or later.
>=#1/1/2019# And <#1/1/2020#	The date you enter must be in the year 2019.

To get help forming expressions, click the three dots beside the Validation Rule text box to open the Expression Builder, as shown in Figure 2-6, and build an expression there. Try clicking the Help button in the Expression Builder dialog box. Doing so opens the Access Help program, where you can get advice about building expressions.

Creating a lookup data-entry list

Perhaps the best way to make sure that data is entered correctly is to create a data-entry drop-down list. That way, anyone entering the data in your database table can do so by choosing an item from the list, not by typing it in, as shown in Figure 2-7. This method saves time and prevents invalid data from being entered. Access offers two ways to create the drop-down list:

• Create the list by entering the items yourself: Go this route when you’re dealing with a finite list of items that never change.
• Get the items from another database table: Go this route to get items from a column in another database table. This way, you can choose from an ever-expanding list of items. When the number of items in the other database table changes, so does the number of items in the drop-down list because the items come from the other database table. This is a great way to get items from a primary key field in another table.

Creating a drop-down list on your own

Follow these steps to create a drop-down, or lookup, list with entries you type:

1. In Design view, click the field that needs a drop-down list.

2. Open the Data Type drop-down list and choose Lookup Wizard, the last option in the list.

   The Lookup Wizard dialog box appears.

3. Select the second option, I Will Type in the Values That I Want, and click the Next button.

4. Under Col1 in the next dialog box, enter each item you want to appear in the drop-down list; then click the Next button.

   You can create a multicolumn list by entering a number in the Number of Columns text box and then entering items for the list.

5. Enter a name for the field, if necessary, and click the Finish button.

   Switch to Datasheet view and open the drop-down list in the field to make sure that it displays properly.

To remove a lookup list from a field, select the field, go to the Lookup tab in the Design view window, open the Display Control drop-down list, and choose Text Box.

To see what’s on a drop-down list, select the field for which you created the list, switch to Design view, and select the Lookup tab in the Field Properties pane. As shown in Figure 2-8, you can edit the list by editing or removing items in the Row Source text box. Be sure that a semi-colon (;) appears between each item.

Indexing a field

To index a field, switch to Design view, select the field you want to index, and on the General tab of the Field Properties part of the Design window, open the Indexed drop-down list and choose one of these options:

• Yes (Duplicates OK): Indexes the field and allows duplicate values to be entered in the field.
• Yes (No Duplicates): Indexes the field and disallows duplicate values. If you choose this option, the field works something like a primary key field in that Access does not permit you to enter the same value in two different records.

Indexing based on more than one field

An index created on more than one field is called a multifield index. Multifield indexes make sorting, querying, and searching the database table go faster. They are especially valuable in sorting operations where records in one field are usually the same but records in a companion field are different. In a large database table that stores names and addresses, for example, many names in the Last Name field are the same, so indexing on the Last Name field isn’t worthwhile, but indexing the First Name and Last Name fields helps Access distinguish records from one another.

Follow these steps to generate a multifield index:

1. Switch to Design view, and on the (Table Tools) Design tab, click the Indexes button.

   You see the Indexes dialog box, as shown in Figure 2-9. The dialog box lists the primary key field already because it’s indexed by default. You also see any fields to which you set the Indexed property to Yes.

2. On a blank line in the dialog box, enter a name for the index in the Index Name column.

3. In the Field Name column, open the drop-down list and choose the first field you want for the multifield index.

   Access sorts the records first on this field and then on the second field you choose.

4. In the next row, leave the Index Name blank and choose another field name from the drop-down list.

   This field is the second field in the index. You can use as many as ten different fields in a multifield index. In Figure 2-9, two fields are in a multifield index: First Name and Last Name.

5. Choose Descending in the Sort Order column if you want the field sorted in descending order.

   Most of the time, you want leave the Sort Order set to Ascending because most people read from A to Z.

6. Click the Close button.

   Click the Indexes button in Design view if you need to return to the Indexes dialog box and change how fields are indexed.

Types of relationships

The vast majority of relationships between tables are one-to-many relationships between the primary key field in one database table and a field in another. Table relationships fall in these categories:

• One-to-many relationship: Each record in one table is linked to many records in another table. The relationship in Figure 2-10 is a one-to-many relationship. Each ID number appears only once in the ID field of the Customers table, but in the Orders table, the same Customer ID number can appear in many records because the same customer can order many different products. When you link tables, Access creates a one-to-many relationship when one of the fields being linked is either a primary key field or an indexed field that has been assigned the No (No Duplicates) setting. (See “Indexing for Faster Sorts, Searches, and Queries,” earlier in this chapter.)
• One-to-one relationship: Two fields are linked. This relationship is rare and is sometimes used for security purposes.
• Many-to-many relationship: This complex relationship actually describes crisscrossing relationships in which the linking field is not the primary key field in either table. To create a many-to-many relationship, an intermediary table called a junction table is needed. This relationship is rare.

Sometimes, fields in separate tables that hold the same data also have the same name, but that isn’t necessary. For example, a field called ZIP Code in one table might be called Postal Code in another. What matters is that fields that are linked have the same data type. For example, you can’t create a relationship between a text field and a number field.

Handling tables in the Relationships window

To display the tables in a database and link tables to one another or see how they’re related to each other, go to the Database Tools tab and click the Relationships button. You see the Relationships window, as shown in Figure 2-11. Notice the field names in each table. The primary key field is shown with a picture of a key next to it. Lines in the window show how relationships have been established between tables.

Apart from linking tables in the Relationships window (a subject I explain shortly), use these techniques on the (Relationship Tools) Design tab to handle tables:

• Repositioning and resizing the tables: Each table appears in its own window. Drag tables from place to place, drag a border to change a window’s size, and scroll to see field names.
• Removing a table from the window: Select the table and click the Hide Table button.
• Removing all tables from the window: Click the Clear Layout button and choose Yes in the confirmation box.
• Placing tables back on the window: Click the Show Table button, and in the Show Table dialog box, select the tables and click the Add button.
• Placing all tables back in the window: To put all the tables with relationships back in the window, click the All Relationships button.
• Studying a table’s relationships: Click the Clear Layout button to remove all tables from the window; then place the table back in the window, select it, and click the Direct Relationships button. All tables that are related to the selected table are added to the layout.

To generate and print an Access report that shows how tables in your database are linked, go to the (Relationship Tools) Design tab and click the Relationship Report button. Then save the report and print it. Chapter 5 of this minibook explains reports.

Forging relationships between tables

On the (Relationship Tools) Design tab (refer to Figure 2-11), make sure that both tables are on display and then follow these steps to forge a relationship between them:

1. Click to select the field in one table; then hold down the mouse button, drag the pointer to the field in the other table where you want to forge the link, and release the mouse button.

   You see the Edit Relationships dialog box, as shown in Figure 2-12. This dragging between table fields is probably the most awkward thing you undertake in Office 365! If you do it right, a bar appears where the pointer is while you move it over the second table, and the names of the two fields appear in the Edit Relationships dialog box.

   Notice the Relationship Type at the bottom of the dialog box. If you accidentally create a link to the wrong field, choose the correct field from the drop-down list in the dialog box.

2. Select the Enforce Referential Integrity check box.

   If you don’t select this box, the relationship between the tables is indeterminate, instead of being a one-to-many relationship. Referential integrity (another hideous database term!) has to do with whether values in the two different fields corroborate each other.

3. Select Cascade options if you so choose.

   One of these options is excellent; the other is dangerous:

   • Cascade Update Related Fields: If you change a value on the “one” side of the relationship, a matching value on the “many” side changes as well to preserve referential integrity. For example, if you create a multifield primary key of First Name and Last Name and then change the name of someone, the related fields in the other table change automatically to preserve referential integrity. This is a great way to make sure that information is up to date.
   • Cascade Delete Related Records: If you delete a record in the “one” table, all records in the “many” table to which the deleted record is linked are also deleted. For example, if you delete an employee from the “one” table, all records in the “many” table that include that employee are deleted! Access warns you before making the deletion, but still! This option is dangerous, and I don’t recommend selecting it.

4. Click the Create button to forge the relationship.

   In the Relationships window (refer to Figure 2-11), a line is drawn between the table fields. The number 1 appears on the “one” side of the relationship and the infinity symbol (∞) appears on the “many” side.

After you create a one-to-many relationship between tables with the Enforce Referential Integrity check box selected, you can’t enter a value in the “many” table unless it’s already in the “one” table. For example, suppose that the “one” table includes a primary key field called Employee Number, and this field is linked to a field in the “many” table that is also called Employee Number. If you enter an Employee Number in the “many” table that isn’t in the “one” table, Access warns you that it can’t be done without violating referential integrity. The best way to solve this problem is to create a lookup data-entry list in the “many” table with values from the primary key field in the “one” table. See “Creating a lookup data-entry list,” later in this chapter.

Editing table relationships

In the Relationships window (refer to Figure 2-11), select the line that represents the relationship between two database tables and follow these instructions to edit or remove the relationship:

• Editing the relationship: Click the Edit Relationships button or right-click and choose Edit Relationship. You see the Edit Relationships dialog box, where you can overhaul the relationship. (The previous topic in this chapter explains how.)
• Deleting the relationship: Press the Delete key or right-click and choose Delete. Then select Yes in the confirmation box.