[Figures are not included in this sample chapter]

Sams Teach Yourself Microsoft® Access 2000 in 21 Days
- 3 -
Exploring the Data Foundations--The Table

The past two days have mired you down in theory. Today starts the real hands-onuse of Access, which, for most people, is a lot easier than abstract discussion.I think you'll also find that using Access is just plain fun. So let's get started.Today, you will learn the following:

• The nature of a table
• Creating a table
• Table properties
• Editing table properties

The Nature of a Table

Yesterday you saw the way a table looks in Data and Design views. Today you'lllearn how to create a table. You'll also gain an understanding of the theory behindvarious table elements and how to apply that theory to your projects.

Remember that a table is the fundamental element in a relational database. Itis the place where the system stores all data. The other objects of a database systemmanipulate or present the data, all of which is stored in these tables. The ironyof a table's position in a database system is that although it's the simplest objectto create or modify, it's also the most important in the sense that if the tablestructure is wrong, the database system can't be right.

The most important element in table design is making sure that the table's fieldsare suitable for the intended data. For example, if you define an address field asbeing able to hold only 20 characters, you will be in deep trouble when people startentering real addresses in that field. You can change much of a table's design afterthe fact, but some changes will cause ripple problems (downstream trouble) if thetable is linked to other tables, or if you have other database objects expectinga certain data structure.

The moral of the story is to plan three times and do once.

Examining a Table

Locate and open the Tables.mdb database on your CD-ROM. Click the Tables entryin the object bar, and then open in Datasheet view (double-click on) the tblAllTypesobject. Your screen should resemble Figure 3.1.

FIGURE 3.1 A table is a simple structure, but it holds the key to the success of your database.

This table has various field types (data types) and is a trivial example, butshould give you a feel for the nature of a table and the types of data it holds.

Click in the Social Security Number field. Note that Access creates a templatefor a Social Security number. That template is called an input mask. Enter a SocialSecurity number. Now click on the Last Name field and try entering the last nameGonzalez. You can't because the field size for this field has been defined too smallto be useful for a Last Name field. This is a rather obvious error, but one of thetypes you as a developer must watch out for.

Continue to enter data, observing from entries that have come before what typeof data yo u suppose the field should hold. The final field, Sound, contains a soundfile (.wav) that you can play if you have a sound-enabled computer, but don't worryabout entering new sounds in this field. Try entering apparently wrong data suchas 3/4 in the Contact Date field. The table won't accept data that's inappropriate.You won't need to (or can't) enter a number in the Friend Registration Number becausethat will auto-increment when you're finished with the record.

When you're finished experimenting, switch to Design view by clicking the viewbutton at the far left of the toolbar. Your screen should resemble Figure 3.2.

FIGURE 3.2 Here is a table that contains a sample of various data types.

When designing your tables you must focus on the following main issues:

• What will be the key field for the table?
• What data type will you use for each field?
• What properties for each field do you need to set?
• How does this table fit into your entire data structure scheme?

Use the tblAllFields object in Design view to explore the various elements ofthis elementary table's design. Pull down combo boxes; examine how the Propertiessection changes as you click different fields (having different data types). Clickthe Last Name field entry, and examine the Field Size property, noting that it'stoo small to accommodate most of the world's last names (remember the problem youhad when you tried to enter Gonzalez?).

Return to Datasheet view for this table, discarding any changes you might havemade in Design view. Place your cursor between any two columns until the cursor changesto a bar with opposite-facing arrows. Cli ck and drag, noting how you can change thevisible size of a field.

---

NOTE: Changing the visible size of a field will not alter its Field Size property.

---

Click the column head. This will highlight the column. Pressing Delete at thispoint will allow you to delete this field from your database. Click to the rightof the Social Security Number field at the record level to highlight an entire record.Your screen should resemble Figure 3.3.

---

TIP: Using the usual Explorer keys Ctrl and Shift, you can make multiple selections of fields or records.

---

Again, pressing Delete will delete any highlighted records. Keep in mind thatalthough this works for a few records, an action query is a better way to deleterecords from a live database, as discussed on Day 15, "Examining Special QueryUses."

Click any column head, keeping the mouse button pressed, and then drag the columnto the right or left. This will change the column order display, but it won't affectthe order of fields in Design view.

FIGURE 3.3 Using a record selector, you can highlight a record or a series of records.

Finally, click between two rows on the far left of the table--in the record selectorarea. Your cursor will change to a double arrow with a bar. Drag up or down to changethe width of all records.

---

NOTE: All rows (records) in a table must be the same width. You can't resize rows individually.

---

Finally, click the Format entry in the main menu bar. Note the entries that areavailable to you for this table. Microsoft has mad e some changes to Access to makeit work somewhat like Excel for tables and queries (when feasible), so you get theExcel-like entries for hiding and freezing columns (fields).

Keep in mind that it should be a very rare or even non-existent event that userseven see a table, much less make data entries to it or edit its layout (change columnor row order or widths). Microsoft added these table facilities apparently to makeExcel experts feel more at home. An adept Access developer will have his or her dataentry done all through forms or automated by macros or VBA.

---

TIP: Using the same technique as record selection, you can alter (drag) the field order in Design view. This will alter the order of fields in Table (Data) view. Conversely, altering the order of fields in Table view will not alter their order in the design grid.

---

Creating Your First Table

By now you probably have enough experience in tables and their views to have agood idea of how to create the critters, but follow along with this section becauseyou'll likely pick up a few interesting details.

There are three ways to make a table. They are

• Use the design view grid
• Use data entry to create a table by example
• Use a wizard

Table Design Basics

The design grid is the way almost all (maybe all) experienced Access developerscreate their tables. This grid is the only way to have the fine control of your fields'properties that you must have in any but the most elementary Access applications.

Somewhat perversely, Microsoft tries to get you to use the sample entry (shownfollowing) for tables by automat ically launching that method when you define a newblank database. This likely stems from some user focus groups, but it's truly bafflingfor most Access users.

Here are the steps to creating a table using the design grid:

1. From the Database view, click New and then choose Design View from the resulting dialog box, and click OK.

2. Enter a field name in the first row of the Field Name column.

3. Press Enter or Tab to move to the Data Type field.

4. Enter a data type for this field. Access will scroll using the first letter of the data type. You can also pull down the combo box, and choose the data type from a list.

5. Alter the Field Properties section of the table design grid as needed.

6. Add a comment in the Description column if desired.

The following are the various data types and their uses:

• Text--Alphanumeric data up to 255 characters. Access uses dynamic storage, so specifying a field fewer than 255 characters will not affect disk storage needs, but it can have an effect on auto-generated objects (such as forms) based on the field.
• Number--Various kinds of numbers from byte (0-255) to decimal. Use the Number type only for fields that must have computational operations on them; otherwise, use Text. You specify what kind of Number data types your field will hold by altering the Field Size property. Figure 3.4 shows the combo box for the Number data type with its selections exposed.

FIGURE 3.4 The Number data type uses the Field Size proper ty to specify the type of numbers your field will receive.

---

TIP: Use the least-precise Number data type you can to preserve database size and to optimize Access' performance.

---

• Date/Time--Really a Number data type too because Access stores dates as sequential numbers. Used to store dates or times.
• Currency--Another variant of the Number data type. This is optimized (it's very precise) for currency calculations and it formats using a currency sign.

---

TIP: A Number data type entry can't start with a zero, so use Text for any fields (such as Social Security number) in which there exists the possibility of a leading zero or zeros.

---

• AutoNumber--A field that either sequentially or randomly assigns a number to each record without any user input.
• Yes/No--A very compact data type that is either on or off (yes or no). Good for check box type fields such as Application Received?.
• OLE Object--A linked or embedded OLE object of any type. Normally used for adding items such as music, sound, or pictures to a table.
• Hyperlink--A URL such as a Web site address or an email address.
• Lookup Wizard--Not really a data type, but a way to link a source to a field within a table.

Using the Table Design Grid

Now that you have the preliminaries, it's time to create your first table usingthe design grid (Design view). Open the Tables database if necessary, and open thetblEmployees table in either Table view or Design view. That's the table you'll becreating in this a nd the other task lists. If you get lost, consult this table tosee where you went wrong.

Task: Creating a Table Using Design View

1. Launch Access and either load the Tables database or start a new blank database. If you do the latter, close the blank table that Access will auto-launch.

2. Click the Tables entry in the object bar. Click New to start a new table. Choose Design View from the dialog box, and click OK. That will launch the design grid. You can also choose Create Table in Design View from the Database view.

3. Enter the field name Employee ID in the first row of the Field Name column. Press Enter to enter the Data Type field. Your screen should resemble Figure 3.5.

FIGURE 3.5 The start of a new table in Design view.

4. Click the Key Field icon (the one with the key on it) in the toolbar. The Employee ID field will be the field used to link the tables within this database. Click in the Field Size field in the Field Properties section of the grid. Edit the value to read 6, the size of an employee number in this company.

5. Click in the second row of the Field Name column and enter Last Name. Keep the Data Type as Text, but edit the Field Size property to 20.

6. Enter Field Names for first name and middle name, changing the Field Size to 15 for both, but keeping the Data Type as Text.

7. Enter the Field Name Vested and change the Data Type to Yes/No.

8. Enter a Field Name for vestment date and change the Data Type to Date/Time. Also change the Format property to Medium Date.

9. Choose File, Save or click the Save icon on the toolbar. If you're in a new database, use the name tblEmployees for a table name. If you're using the Tables database, choose another name to prevent overwriting the existing table.

10. Click the View button to switch to Table view. Your screen should resemble Figure 3.6.

FIGURE 3.6 Your new table in Datasheet view is ready to receive data about your company's employees.

---

NOTE: Note that Access automatically knew to use a check box for the field you designated as Yes/No.

---

Although there are more details, such as many other field properties you needto gain familiarity with, that's how to create a table using the Design view.

Using the Datasheet View to Make a Table

Microsoft added this facility against some objections from the database community.It's not that using this method necessarily ends up in a bad way, but it really savesnothing over using the Design view method and can lead to sloppy designs in all butthe most elementary databases. It's your call if you like using this method. To seethis table finished, open the table tblByDatasheet from the Tables database. Hereis the method in all its glory.

Task: Using the Datasheet View to Create a New Table

1. Launch Access and either load the Tables database or start a new blank database. If you do the latter, Access will launch a blank table for your use. Skip to step 3 if this is the case.

2. Click the New button or cho ose Create Table by entering data from the Database view. If you clicked the New button, choose Datasheet view from the dialog box and click OK. Your screen should resemble Figure 3.7.

FIGURE 3.7 You can use the Datasheet view to define certain data types on-the-fly.

3. Enter 000234 for the first field in the first row.

4. Right-click on the first column header (now labeled Field1) and choose Rename from the context menu. That will place the column header in edit mode. Enter the field name Employee ID for this field.

5. Enter some name data of your own choosing (why not use your name?) for each of the next three fields, editing the field names to Last Name, First Name, and Middle Name, respectively.

6. Move to the next column, enter Yes, and edit the column header to Vested.

7. Move to the next column, enter a date such as 2/3/99, and edit the field name to Vestment Date. Your screen should resemble Figure 3.8.

FIGURE 3.8 Creating a table using the data entry method can be quite tedious for any but the smallest tables.

8. Click the Save icon in the toolbar (the one with the diskette icon) and name the table tblByDatasheet if you're in a new database. If you're in Tables, choose a unique name to prevent overwriting the existing table.

9. Click the View button to switch to Design view, refusing the offer to create a primary key. Your screen should resemble Figure 3.9.

FIGURE 3 .9 The datasheet entry method does a respectable job of guessing the correct data types for your fields.

NEW TERM: A primary key is a field holding values that are unique overthe entire table. Each record in a table must have a primary key to absolutely identifyit.

Note that Access did a good job of guessing the right data types for the fieldsbased on the data you entered in the first record. You want the Text type for EmployeeID, and there's no control over the Field Size property except in Design view.

To see a limit of the Datasheet view method, switch back to Table (or Data) viewand note that the leading zeros have disappeared from the Employee ID field. Thisis because a Number data type field can't have leading zeros. From here, you canfine-tune this table to meet the exact criteria you did when you created the sametable in Design view.

It's your call if you think that this method saved time or was simpler than theall-Design view method.

The Table Wizard

The Table Wizard is somewhat misnamed. Other wizards allow you to create objectsmore or less of your own choosing. The Table Wizard only allows you to pick and choosefrom predefined tables and fields, yet it still can be a time saver. Remember, youcan use Design view to edit any fields or field properties created in any table,including those done by a wizard.

That said, take a look at the table from the Tables database called tblServiceRecords. This is the table you'll be creating using the wizard. When you're finishedreviewing the outcome of this experiment, it's time to actually do the work.

Task: Using the Table Wizard to Create a New Table</ H4>

1. Launch Access and either load the Tables database or start a new blank database. If you do the latter, Access will launch a blank table for your use. Close this table.

2. For something new, double-click on the Database view entry Create Table by Using Wizard. Your screen should resemble Figure 3.10.

FIGURE 3.10 The Table Wizard is really a picker of predefined tables and fields within those tables.

3. Click the Personal option button in the left center section of the dialog box.

4. Scroll down the Sample Tables list box until you find the table Service Records. Click on it to highlight it.

5. Click the single right-facing caret to move the current field from the sample table to the table you're creating. Use the double-caret button to move all fields. Similarly, you can remove a field or all fields from your table by clicking the single or double left-facing carets, respectively. For this table, include all the fields from the sample table into your table. Click the Next button to move on.

6. To maintain our naming conventions, rename the table tblService Records. Allow the wizard to set the primary key for you. Click Next.

7. The next screen is rather interesting because Access will try to establish a relationship with existing tables by examining all the primary keys. Because the other tables currently existing in Day 3 don't have anything to do with tblService Records, Access correctly guesses that no relationships should exist. Your screen should resemble Figure 3 .11.

FIGURE 3.11 The Table Wizard is a trouper in that it even tries to establish relationships with existing tables. In this case, no relationship is possible and it recognizes the fact.

8. Click Next, and then Finish to see the table in Datasheet view. There is no need to manually save the table. The wizard does that for you.

Analyzing the Wizard's Table

The wizard didn't do a very good job of making a table. Although the general tableis all right, you should be able to do better and you will be able to before you'vefinished this book. Let's look at some of the design flaws in this table.

Flat Model Instead of Relational

With the table in Data (or Table) view, scroll over to the Parts Replaced field.That's a normal text field. As you likely know, most repair jobs require more thanone part to be replaced, so how would a data entry person handle this field? Let'ssay a person wanted to determine the cost of this job by linking the parts to hisor her cost? There's no way to do this. The following are the two ways:

• Put a bunch of parts in one field as shown in Figure 3.12. That the field is of data type Memo implies that this is the designer's intent.

• Put each part in a duplicate record, as shown in Figure 3.13.

FIGURE 3.12 This table violates one of the primary rules of relational database design. How could a data entry person handle one field for many entries?

FIGURE 3.13 The solution of a new record for each part is worse than the preceding multiple entry solution. What a waste of time and effort, not to mention the confusion about whether these are a single service or more than one incident.

We'll correct this structure in just a moment to show the correct way to structurethis type of data.

Possible Wrong Field Types

Switch to Design view for the Service Record table. The Description field is Textdata type of 255 characters long. There is also a Problem Description field of Memodata type. There are no comments to tell you, the programmer, or the user what thedifference in these fields is, or whether the 255 characters in Problem Descriptionis sufficient.

---

DO use the right data type for each field.
DON'T jump to the obvious because this is often wrong. For example, use Number data type only for fields that need mathematical evaluation. Don't use Number for fields such as phone number even if you're sure nothing but numbers will ever be entered.

---

Equally worrisome is the AutoNumber data type for the ServiceRecordID field. Thisis all right if you want to allow Access to uniquely generate an ID for each job,but what if the service orders are pre-printed or your user wants to use a customcode for each service record?

The AutoNumber data type is useful for generating index numbers when you're surethat you won't need to edit or specify the values for this field other than the startingnumber, but it isn't ideal for all uses.

Linking Fields--The Heart of the Relationship

The really serious problem with tblServiceRecords as it stands is the bunchingof parts within a single field. Open the Tables databa se in the Day3 folder, if necessary;close any open objects to clear the deck, and get ready to work.

Click the Tables entry in the Object bar and locate the two tables, tblServiceIncidentand tblPartsUsed. These are the two tables you'll be linking.

The purpose of this task is to have one record for the service incident, and away to not only record all the parts for this job, but to cost it as well. The solutionis to put the service incident details in one record and the parts in many records.The reasoning will become clearer as you work through this task. You can then furtherlink up the parts-used information to cost information for those parts.

Some of the presentation in the rest of this section will be a bit beyond whereyou've gone, but try not to worry about it. Concentrate on the idea of the link,and how it makes the one-to-many relationship of having many parts to a single servicejob much clearer and more useful than lumping all the parts into one Memo field.

Task: Using the Table Wizard to Create a New Table

1. Open the two tables tblServiceIncident and tblPartsUsed in Design view either at the same time or separately. Note that there is a field called ServiceRecordID in both tables. This will be the link field.

---

NOTE: Linked fields must be of the same data type. AutoNumber by increment--the data type in tblServiceIncident--uses Long Integers for incrementing, so the link field in tblPartsUsed is Number with a field size of Long Integer.

---

2. Choose Tools, Relationships from the main Access menu. Your screen should resemble Figure 3.14.

FIGURE 3.14 The Relationships window is where you link tables using their common fields.

3. Locate the two tables tblServiceIncident and tblPartsUsed. Click on each and click the Add button to move them from the Show Table dialog box to the Relationship window. If you want to, adjust the size of the field list boxes to show more fields or to show all the fields. Your screen should resemble Figure 3.15.

FIGURE 3.15 These tables are ready to have a link defined for their one-to-many relationship.

4. This link will be one-to-many, meaning that there might be many parts for any single service incident. Also, you should enforce relational integrity. That means that you can't assign a part to a job that doesn't exist. In other words, a matching record must exist in tblServiceIncident before you can attribute a part to it in tblPartsUsed.

5. Click on the ServiceRecordID field in tblServiceIncident and drag your cursor to the ServiceRecordID in tblPartsUsed. Release the mouse button. When you're finished, your screen should resemble Figure 3.16.

FIGURE 3.16 The simple drag method establishes the link.

6. You might have noticed that Access guessed and created a cursor to the effect that this is a one-to-many relationship. Check the Enforce Referential Integrity box and the two other check boxes in the dialog box that become enabled. Your screen should resemble Figure 3.17.

FIGURE 3.17 The link is establish ed with integrity enforced. Don't worry about the join types at this point. You'll learn about this in Day 11.

7. Click Create New to close the dialog box, and notice that Access graphically shows you the new link (and that it's one-to-many) in the Relationships window. Close the Relationships window by choosing File, Close from the main menu.

The idea of the link field is to make sure that the items in tblPartsUsed willmatch up correctly to the right job listed in tblServiceIncident.

Currently there are two records in the tblServiceIncident table. There are severalparts in tblPartsUsed for each of these jobs. Open both tables to examine the entries.Figure 3.18 shows these two tables with their entries as of this point.

FIGURE 3.18 Each part used is linked to the job it's been used for by the common link field ServiceRecordID.

Shortcut to Data Entry

Access does include a way to add linked records to the many table quite easily,but remember it's not really a good idea to do so. Still, because it's there, it'sworth knowing about. Open the tblServiceIncident table. Note the new feature--a plussign at the extreme left of each record. Click on the plus sign. That will open upa new table-like window in which you can add parts to any job. Figure 3.19 showsthe parts window opened for job number 2.

FIGURE 3.19 Access has a table-level entry facility to enter items to the many side of a one-to-many relationship.

This is one of those things that appears somewhat confusing in a screen shot.By all m eans, open tblStudentIncident and click on the plus sign yourself to seehow this facility works. Most experts believe that the best way to do data entryin a one-to-many relationship is through a form with a subform. You can get a previewof that topic by looking ahead to Day 16, "Advanced Form Concepts."

The Outcome of a Relationship

At this point, you might think that this is a lot of work compared to includingall the parts in one memo field like the original table did. Here's where you mustjump ahead a bit so you can see the reason for all this foundation work.

There is another table in the database Tables called tblPartsData. This tablecontains cost and supplier information about each part this auto repair facilitystocks or orders. If you examine that table's data or structure and tblPartsUsed,you'll note a slight violation of proper normalization because the part descriptionis repeated in each table. I left things that way to make some earlier steps clearerfor you, but in an actual application, I wouldn't have had this data duplicated.

Figure 3.20 shows a query in which the information in the two tables, tblServiceIncidentand tblPartsUsed, is linked up again. You can see that due to the link between thefields, the query is able to match up the parts used with the job where the partswere used.

FIGURE 3.20 The link field lets Access match the parts used with the jobs they were used in. Note this query includes data from both tables.

The query shown in Figure 3.20 is part of the Tables database and has the nameqryBasicMatchUp. If you want to run this query, click on the Queries entry in theobject bar and double-click on its entry. Open this query in Design view to see howit is constructed.

The Best Is Yet to Come

That's fine, but you might still be wondering, "Why bother?" If youlisted all the parts in one huge Memo field, they would also be matched up with theright job.

Now it's time for the relationship trump--the matchup you can't do using the Memofield method of including many items in a single field in a table. Figure 3.21 showsanother query, this time including cost information from the table tblPartsData.This query is called qryCosting and is also part of your sample data. Open it inDesign view to see how it works if you want a peek ahead on query construction.

FIGURE 3.21 At last, all the extra work pays off. The cost information is yours without any additional work.

Yes, you could have manually entered all the cost information in the originaltable, but why bother? This way, after you've created the relational structure, Accesswill automatically look up linked information for you.

---

NOTE: One of the main purposes of a relational database is having the database look up related data rather than requiring that data entry personnel enter it redundantly.

---

Not Convinced?

If you're not convinced yet, open the query qryTotalCost in Query view by double-clickingon it. Your screen should resemble Figure 3.22.

FIGURE 3.22 Computer programs love to add things up. Here Access gathers up all cost data for each job and sums that data, matching the sum to the job.

By all means, look at the magic behind this query by switching to Design viewfor it. Although the workings of this query might not be superficially obvious, youshould have a good idea of how it's operating.

There is no reasonable way to duplicate the costing and summation data you'veseen in these past two queries by piling data into Memo fields. More importantly,having data normalized like this allows for easy lookups, faster data entry, andassured data integrity. The latter simply means that you can't enter a part for ajob that doesn't yet exist. You must create a job first, and then you can add partsto it. Otherwise, you can end up with "data orphans" in your system.

More importantly, referential integrity means you must assign a part to a job.Therefore, you're assured that you'll bill all the parts when you bill the jobs.That doesn't necessarily mean the right part is attached to the right job, but that'sanother topic.

Some of what you saw in this section is a bit too complex to go into right now(such as how that query did its totaling magic) because the information so far islikely quite a bit of a load for you at this point. Day 11 builds on the foundationlaid today.

If you remain skeptical that the referential model isn't the best for the vastmajority of data tasks, how about just suspending your disbelief for a few days?Give it a while and you'll see why it's worth the bother to create a correct relationalstructure for your database projects. After a short while, you'll see that usinga flat model would be less than satisfactory for most of what you will need to dousing Access.

Seeing It Yourself

Grasping the concept of linking and relationships between tables is v ital to thesuccessful use of Access and similar products. The following task will use the objectsyou're now familiar with to show how this concept works in daily use.

Task: Adding Data to a Linked Table and Then Seeing the Result

1. Open the Tables database, if necessary. Open the tblServiceIncident table in Datasheet view (double-click on it in the Database view).

2. Click the plus sign to the far left of the first record in the table. This will open a window containing the linked table tblPartsUsed. Your screen should resemble Figure 3.23.

FIGURE 3.23 Clicking on the plus sign (expand view) will open a window to the linked table.

3. Enter a few part numbers and descriptions of your own choosing to simulate entering actual parts for this service order. Keep in mind what you've entered.

4. Click in the same area, to the left of the first record, on the sign that's now a minus. The window will close.

5. Click on the plus sign to the left of the second record to open a window for that record. Again, enter some data in the window distinct from the data you entered in step 3. Close the table.

6. Click on the Queries entry in the Database view.

7. Run the query qryBasicMatchUp by double-clicking on it. Note that the parts you entered for record 1 are now linked to that record, and those you entered for record 2 are now linked to that one. Those are the essentials of a relational database. Your screen should resemble Figure 3.24.

FIGURE 3. 24 You can see how Access links the records correctly by running this simple query.

Table and Field Properties

You've seen demonstrated only a few table properties as of now. This section willstart your tour of a few additional table properties, plus some overall table settings.

Task: A Beginner's Guide to Field Properties

1. If necessary, open the Tables database and the tblEmployees table in Design view. Your screen should resemble Figure 3.25.

FIGURE 3.25 The tblEmployees table object will act as a test bed to learn about some additional properties available to the developer in Access tables.

---

NOTE: This task uses the tblEmployees, but any table will suffice for these demonstrations.

---

2. Click on the Last Name field to bring up the context-sensitive Field Properties list box.

3. Edit (or enter) Surname for the Caption property. The Caption property controls the label a field has, not only for the table, but for any objects (such as forms) derived or bound to the table. If the Caption property is blank, the field will use the field name for a Caption property. Switch to Datasheet view, saving changes when prompted. Figure 3.26 shows the results of the Caption property.

FIGURE 3.26 The caption or header for this field now overrides the default field name for a caption.

4. Note that the field name is now Surname, reflecting your entry in the Caption property.

5. Use of the Format p roperty is a huge topic in detail, but the concept is simple. The idea is to alter the format or appearance of data. For example, you might want a data field to display in capital letters. To do this, enter a > for the Format property. Try that with the Last Name field. Change to Datasheet view, enter some lowercase letters in the field captioned Surname, and then leave the field. The entry will appear in capital letters. Figure 3.27 shows the use of the Format property to display a field in all caps.

FIGURE 3.27 A simple change to the Format property will make a large difference in the display of a table's data.

---

NOTE: The data stored in the table with a Format property of > is in its original form--upper- or lowercase. The Format property changed only the way the data is displayed in the table.

---

The help system is a good reference for other format properties. Table 3.1 givesa few examples to get you going.

TABLE 3.1  A FEW SAMPLE FORMATS

Indexing and More on Key Fields

The key field (primary key) is the unique identifier for a record. All tables,without exception, should have a key field identified. For example, you can't generallyuse a Last Name field as a key field because most databases will have, or at leastcan have, more than one entry for any last name. The authors of this book, Palmerand Cassel, have fairly common names. If you used last names as a key field, youcould only have one Palmer and one Cassel in the entire table--surely not a gooddesign concept. Last Name won't work for a primary key, but the authors' Social Securitynumbers are unique to them, so that would work.

If possible, use natural keys--keys that are part of the data anyway. A good exampleof this is the previously mentioned Social Security number. Everybody's is uniqueand you might have that as part of the data anyway. If you're creating an auto serviceapplication, license plate (tag) numbers should work as a primary field.

If nothing works naturally, you can resort to using Access' AutoNumber facilityto generate artificial (not part of the data) primary keys. A primary key not onlyuniquely identifies a record (you can have only one example in any table), but alsospeeds many Access operations. Don't leave the console without one.

Indexes (indices, really) order da ta within a database. An index with entriesthat can't be duplicated in that field within a table is a primary key, but you canalso have secondary indexes. Figure 3.28 shows the tblEmployees in Design view. Examinethe Field Properties for the Indexed property. The entry reads Yes (No Duplicates)for the definition of a primary key.

FIGURE 3.28 The EmployeeID field is the primary key for this table.

Click the pull-down tab for the Index property, and you'll note you can set thisproperty to No, for no index, or Yes (Duplicates OK). The latter will order the tableor at least keep a series of pointers to data for any field with that property.

Index any field you expect to search on, but except in the case of the primarykey for a table, don't make the index exclude duplicates. Doing so might end up problematicif you need two identical entries in a field. For example, if you will know thatyou'll be searching employees under last name, index the field, but don't excludeduplicates or you'll only be able to include one last name per table.

You can sort (reorder) and filter data in tables although most developers preferdoing so within queries. The next section shows how you can do these feats whilestaying within tables.

Sorting and Filtering in Tables

Database design purists cringe when hearing that people manipulate the views ofa table. They complain that other database objects, especially forms and queries,are for that duty. Well, cringe causing or not, people seem to like playing aroundin Table or Datasheet view rather than creating additional objects for that purpose.There's really no reason not to use tables for viewing your da ta, other than thatthe data in tables should be normalized (fragmented) into less-than-useful chunks.A query will usually reassemble data into a more useful form, but if your needs canbe met by viewing, ordering, or filtering data directly in tables, there's no reasonnot to do so.

---

NOTE: Edits made in a query will be reflected in the underlying tables. For example, edits made in the query shown in Figure 3.24 will be reflected in the respective tables underlying this two-table query.

---

Sorting

To sort a table or other datasheet is database talk to order it in a determinedfashion. Ordering records alphabetically is an "alpha sort" in databaselingo. Sorting a table in Datasheet view doesn't really alter its indexes, merelythe display of the table. Here's how it's done.

Task: Ordering the View of Table Data

1. Close the Tables database, if it is open. Open the Northwind database. Northwind is part of the sample data. You might need to run Office setup again to install Northwind if it's not on your disk.

2. Click the Tables entry in the object bar. Locate the Customers table and open it in Datasheet view by double-clicking on it. Your screen should resemble Figure 3.29.

FIGURE 3.29 When opened, a table will order itself according to its primary key, which is the Customer ID for this table.

3. Click anywhere in the Contact Name column (field). This will cause your next action to act on that field.

4. Click the Sort Descending toolbar button. Figure 3.30 shows this button (the cursor is on it). Note the change in the table's order.

FIGURE 3.30 Changing the sort order changes the display of data, but not the order of the data within the table.)

5. Click in the Contact Title field. Click on the Sort Ascending button to re-sort the table. This will bring all the accounting managers to the fore.

---

NOTE: The skills you gained today in filtering and sorting in tables are fully transferable to other objects such as queries and forms.

---

That's all there is to changing the sort order. To make sure you return the tableor tables to their pre-sort condition, close them and discard layout changes, andthen re-open. In this simple table, you can also sort ascending on the primary key,Customer ID, to achieve the same result.

Filtering

Filtering means to filter out all but the data you let pass your criteria or criterion.Access makes this operation as simple as sorting.

Task: Filtering

1. Open the Northwind table Customers again if you closed it after the last task. Locate a record within the Contact Title column with the entry Owner.

2. Click the Filter by Selection button on the toolbar. Figure 3.31 shows this button and how your screen should look after clicking.

FIGURE 3.31 Filter by selection takes a look at the current selection and filters according to it.

3. Click the Remove Filter button (two to the right of the Filter by Selection button) to remove the filter. Cl ick the same button again to reapply the filter.

4. Click the Remove Filter button to remove any filter by selection. Click the Filter by Form button. This button is between the Filter by Selection and Apply/Remove Filter buttons. Your screen should resemble Figure 3.32.

FIGURE 3.32 Filtering by form is really a query.

5. Note the pull-down buttons. Pull down the combo box for Contact Title and choose Accounting Manager. Click the Apply Filter button (the one with the funnel as an icon). This will apply the filter as before. You can filter on more than one field using this method. After applying the filter your screen should resemble Figure 3.33.

FIGURE 3.33 Filtering by form allows more than one field filter.

You can use Filter by Form to save and recall filters. Here's the trick in a task.

Task: Saving and Recalling Filters

1. Open the Customers table in Datasheet view.

2. Click the Filter by Form button. Choose the Contact Title field by clicking in it, and choose "Owner" from the drop-down list. Click the Save as Query button in the toolbar. Enter qryFilterDemo as a save name. Your screen should resemble Figure 3.34.

FIGURE 3.34 You can save a Filter by Form as a query.

3. Click OK to exit this dialog box.

4. Click the Close button on the toolbar to close the Filter by Form screen. Close the table by choosing File, Close; discard layout changes, if so offered.

5. Open the table again. Click the Filter by Form button. Click the Load from Query button (far left). Your screen should resemble Figure 3.35.

FIGURE 3.35 You can recall saved filters using this dialog box.

6. Click OK to load the filter. Click the Apply Filter button and you'll have your table filtered as before.

Finding Data

There's one more trick with tables to cover today, but it's an easy one to grasp--findingdata within tables. Like the other skills, filtering and sorting, this skill is transferableto other database objects such as forms and queries.

Task: Saving and Recalling Filters

1. Open the Northwind database if necessary. Open the Customers table in Datasheet view.

2. Click in the Company Name field. Click the Find button on the toolbar (the button with the binoculars icon). Click the More button to expose all of the dialog box. Your screen should resemble Figure 3.36.

FIGURE 3.36 The Find dialog box is a quick route to locate data.

3. Enter Li in the Find What text box. Pull down the Match combo box and choose Start of Field. Click Find Next. Access goes out and finds the first entry in the Company Name field that starts with the two letters Li.

4. Pull down the other combo boxes in this dialog box to gain familiarity with other capabilities of Access' find facility. When you're finished, close everything.

Find Cautions

Many users love the Find dialog box and use it regularly, but a few cautions areworth mentioning, as well as some tips. Here they are.

---

DO perform search and replace operations with caution. This can be helpful, but very dangerous. For example, if you learn that LINO-Delicateses is now LONO Delicateses and decide to search on LI and replace it with LO, allowing a Replace All will change LINO-Delicateses to LONO-Delocateses unless you tighten the search criteria. Use with care!
DO carefully check all options on the Find dialog box to make sure you know where you're looking for your hits.
DON'T apply a find to an entire table (as opposed to a single field) because this can take a long time.
DON'T apply a find to an unindexed field unless you have plenty of time; it will take longer than on an indexed field. If you plan to search regularly within a field, index it in table design view.

---

---

NOTE: You can use an action query to change table data. You'll encounter these specialized advanced queries on Day 15.

---

Summary

Tables store all data within a relational database such as Access. Access offersthree ways to make a table: Design view, by entry example, and by a wizard. Eachfield within a table must have a specific data type conforming to the type of datait's to hold. Each field also has an array of developer-set options called fieldproperties. The properties for each field depend upon the data type.

Primary fields act as unique identifiers for a record within a table. Althoughyou should try to use actual data as a key, using artificial data such as an incrementingAutoNumber field is much better than no key (which should never occur). By linkingfields between tables, which is the essence of a relational database, you can laterconstruct various views of data from more than one table.

Although other database objects such as forms or queries might do the job better(to some people's view), you can filter, sort, and find data within tables.

Q&A

Q If I set a primary key field in my one table, should I also use that field as a primary key in my many table?
A No. This will make your link useless because you will potentially need many entries using the data from the one side in the many side. For example, say you have a list of employees with the Social Security number (SSN) as a primary key in a table called tblEmployees. You also have a table called tblFixedAssets in which each asset is assigned to an employee. The link is the SSN. Because any employee can have many assets, you can't have SSN as the primary key in tblFixedAssets. Instead, include SSN as a field in tblFixedAssets to link on, but use another unique field (such as asset number) for tblFixedAssets' primary key.

Q Why do I need a primary key for every table?
A Access enforces uniqueness for primary fields. Telling Access that a field is the primary key is the only way to be completely sure you have a unique identifier for every record in that table.

Q I want to have more than one sort for a table, but every time I change a table's field order I lose my old sort. How can I have more than one sort for a table?
A You can order or sort tables using a simple query. Each query can have its own sort order, so the answer is to use a series of queries, not sorts within tables. You can copy tables and sort them all, but that's a real waste of disk space.

Q Will the * wildcard work in Access' Find dialog box?
A Yes. For example, *c* will find any field with the letter c in it. But you really don't need this because you can also search in Any Part of Field, which covers the same territory.

Workshop

The Workshop helps you solidify the skills you learned in this lesson. Answersto the quiz questions appear in Appendix A, "Answers."

Quiz

1. Can a primary key field have two records with identical values for the primary key field?

2. Can the value of a primary key appear elsewhere (another field) in a record?

3. How often should identical data appear in a properly designed database?

4. If you create two tables you intend to have a one-to-many relationship, how do you create that relationship?

5. Will the letter Z appear first or last after applying a Sort (Descending) on a field containing all the letters of the alphabet?

Exercises

1. Open the Customers table in the Northwind database. Click in the Contact Title field.

2. Click the Filter by Form button on the toolbar. Enter a filter criterion to show only those records that begin with the letter A. Hint, use the criterion A*.

3. Apply the filter. Remove the filter.

4. If you saved the filter in the "Filtering," task click on Queries in the object bar.

5. Locate the query qr yFilterDemo. Note that the object you saved as a filter is now a query (your first query!).

6. Double-click on this object to launch the query.

7. Did the result meet your expectations?