Introduction to Databases with MS Access

There’s a sense in which almost any computer program is a database – almost any computer program operates on a base of data. However, what we call a database program is generally a program that operates on a base of data in which there is one or more collections (in Access, called tables) of identically structured records. The structure of these records: typically, they consist of multiple fields/components/members/properties/attributes – elementary units of data. All records of the table have the same fields, but not necessarily the same data values.

For example: A library’s catalog. A record of this database typically represents a book in the library’s holdings. Fields of the record typically include:

  • Title
  • Author(s)
  • Copyright date
  • Classification (in Library of Congress system, or Dewey Decimal system)
  • A field to inform you whether the book is currently available, or checked out
  • Etc.

You can create a new Access database by clicking Start, All Programs, New Microsoft Office Document, Blank Database. Immediately, you must specify how the database file is to be stored on your disk or network space. When that’s done, you’ll see what seems to be a document-less Access window. Continue the process of creating a database by going to the Create tab of the ribbon and clicking Table Design. This gives us a view of a database that’s initially empty; we want to design it by choosing its fields and properties of the fields, especially data types and related properties.

Data types include:

  • Text – often used for non-numeric data, such as names, titles, etc. In the Field Properties section of the design view, on the General tab, note the Field Size property. For the Text data type, the Field Size is the maximum number of characters permitted (maximum value: 255). You can save memory by using smaller values – but don’t be too zealous about saving memory, because you should use a large enough value to accommodate your data.
  • A variant on Text is rooted in the idea that a field might use a short list of possible values, and it might be desirable to present this list as a menu. Using a menu is usually faster than typing the value, and less likely to produce typographical errors. In the Design view, for the field’s data type, we can set up such a menu by choosing Lookup Wizard.
  • Number – typically used for numbers, in any of several subtypes that may be specified in the Field Size. Among the subtypes:

Byte – used for relatively small integers, typically a range of 0 to 255, or -127 to 127.

Integer – typically for a range of -32,767 to 32,767

Long Integer – typically for a range of (I think) -2,000,000,000 (approximately) to 2,000,000,000

So, why not always use the Long Integer subtype for whole numbers? The price paid is that the larger ranges of values require the use of more memory.

Single –

Double -

  • Currency – not a subtype of Number, but can be used for arithmetic, and is typically used to represent money; typically, is displayed with a currency symbol such as the dollar sign.
  • Yes/No – a data type with two values, each with 3 representations. One data value is represented in text by the word “Yes” or “True”, and in a checkbox by a check; the other data value is represented in text by the word “No” or “False”, and in a checkbox by a blank checkbox. The checkbox will appear in a datasheet; in certain design aspects, this data type is represented in text.

We can change our view of the database on the Home tab between the Design view (to design fields of the database) and the Datasheet view (to view, enter, and edit the data) by clicking the View button.

Consider an address, such as

456 South Maple Drive apt. G8

We might consider this as an example value for a field named, say, StreetAddress; or we might consider such an address as giving data to fields as follows:

Field / Value
StreetNumber / 456
Street / South Maple Drive
Apartment / apt. G8

The second approach has major advantages (we’ll discuss them much later).

The “Command Center” or “Control Center” is often a jumpoff point in your work with a database. Typically located at the left of the Access window; note its drop-down menu, which includes “object types” for types of documents that may be part of the database, including Tables, Forms, Queries, Reports, etc. For example to view a saved table, if not already selected, choose All Tables from this menu; from the Tables menu, click on the desired table to view it.

A Validation Rule is used to specify the set or range of reasonable values that a field may use. Should a user attempt to enter a value outside of this set, an error message is displayed. For example, a street number is likely to be a positive integer of at most 5 digits – that is, in the range 1 to 99999. Thus, if a user enters a nonpositive value or a 6-digit number, it’s desirable to display an error message.

A typical Validation Rule is based on comparisons, in which the name of the field is understood without statement. Thus:

We want1 streetnumber 99999 – recall this is short for1 streetnumber and streetnumber 99999

orstreetNumber 1 and streetnumber 99999

which is stated in Access as

>= 1 and <= 99999

If you have given a Validation Rule for a field and do not wish to use the default error message when this rule is violated, you may enter your own choice of error message in the field’s Validation Text entry.

One of the most fundamental database operations is finding data. On the Home tab, we can use the Find button (much as in Word) to search for desired data. There are some important differences between Word’s and Access’ Find operations. Notice the following features:

  • The combobox labeled “Look in” allows you either to restrict your search to the field in which the cursor is located, or to search all fields of the datasheet.
  • The “Match” combobox offers options for Any Part of Field (so, e.g., “ga” matches “Garcia” and also matches “Las Vegas”); Start of Field (so, e.g., “ga” matches “Garcia” and does not match “Las Vegas”); and Whole Field (here, “ga” will not match “Garcia” since “ga” is not the whole name).
  • The “Search” combobox allows you to restrict the direction of the search (Up from the record currently containing the cursor, or Down from the record currently containing the cursor), or search All records (in both directions).
  • The Match Case checkbox – if checked, the search proceeds in a case-sensitive fashion (i.e., capital letters must match capital letters, lower case letters must match lower case letters, in order to realize a match); otherwise, the search is case-insensitive (the same letters match each other, even if one is capitalized and the other is lower case).

The buttons (on the Home tab) labeled A to Z (respectively, Z to A) are used to sort the records in ascending (respectively, descending) order with respect to the field containing the cursor. This is not restricted to Text data – any field can be sorted by these buttons. Note, however, that these buttons don’t always break ties “properly” – for example, if we wish to sort alphabetically by LastName, using the FirstName field as a tiebreaker when two records have the same LastName, these sorting buttons may not break the tie as we desire. We’ll discuss sorting with respect to multiple fields when we discuss queries and reports.

If a datasheet has a large enough collection of fields, the data that identifies a record (e.g., LastName and FirstName) might be off-screen as you edit your data, with the result that it’s easy to enter data into the wrong record. In Excel, we could solve this problem by an appropriate splitting of the screen. Access has a different solution – the use of a Form, which allows you to view just one record at a time.

A form is created by, on the Create tab, clicking Form. Notice the navigation buttons at the bottom of the form. These can be used to “navigate” your way among the records of the source table, changing as desired the record displayed by the form.

  • The “Next Record” button shows a right-pointing arrowhead.
  • The “Previous Record” button shows a left-pointing arrowhead.
  • The “Last Record” button shows a right-pointing arrowhead and a vertical line.
  • The “First Record” button shows a left-pointing arrowhead and a vertical line.
  • The “New Record” button shows a right-pointing arrowhead and a yellow rectangle. This takes us to a new (empty) record, suitable for entering the data of a new record.

You may find it necessary to save the data (click the Save button) and/or reload the table in order to see form-edited changes appear in the table.

An existing form can have its design altered: on the Home tab, click the View button and choose the Design view. The Design tab appears, with many tools that may be used to change the appearance of the form.

Note the sections of the form in the Design view: The Form Header and the Form Footer are analogous to page headers and footers, respectively. The Detail Section of a form typically specifies how a record is to be displayed by the form. Any section of the form can be stretched or shrunk in a fashion similar to stretching or shrinking a row of an Excel worksheet.

Notice that at first glance, many of the fields seem to have duplicate representation, e.g., LastName appears in two controls. The left of these fields is a label, used to show the name of the field (or, more generally, useful explanatory text). The right of these fields is a textbox, used to display the value of the field (the last name of the person represented by the record).

Labels and textboxes are among the controls available at the top of the Design tab, in the Controls section. We typically use these to alter the design of a form, especially if we wish to display more information than is currently displayed on the form. For example, we have done the following:

  • Stretched the Form Footer tall enough to place controls in it.
  • Clicked Label and stretched out a label control in the form footer.
  • Put the cursor inside the label to edit its text, e.g., to show the month and year.

Similarly, we can click the label that appears in the Form Header to edit its data.

Many properties that affect the appearance of the data in a control have buttons on the Design tab. Other properties can be edited by right-clicking a control, left-clicking Properties from the resulting menu, and selecting the desired properties and their settings from the resulting Properties Sheet. Also, all controls on a form are graphic controls like other graphic controls that we have used – they may be moved around and resized.

A textbox, placed on a form, is automatically accompanied by a label. (If you don’t want this label, click it and strike Del.) We have previously used a textbox to display the data value of a field of the current record. A textbox can also be used as a “calculated control” to display the result of a formula. As in Excel, a formula starts with an equal sign; a field of the current record is referred to in a formula by enclosing the name of the field in brackets (the “squared parentheses”). For example, we use the formula

=[ConferenceFee]+[RoomCharge]+[RestaurantCharge]

to total a registrant’s bills. Note you can format this textbox for the Currency style by choosing from the Properties Sheet the Format property, with the selection of Currency as the desired format.

Other measures you might take to enhance the appearance of a form include:

  • You might change the background color by bringing up (regarding the form as a control) the Properties Sheet and using the Back Color property to make your selection (you should have sufficient contrast between background and foreground – text – colors so the data is easy to read).
  • Use the line tool of the Design View (and the Design tab) to stretch out a horizontal line between the last of the column of entries added up and the total (the line tool is not restricted to horizontal lines – vertical lines, and sloped lines, are possible).
  • Surround a related group of controls that are sufficiently separated from other controls by a rectangle.
  • In theory (we weren’t successful in attempting the following) you can choose more complex color schemes and background patterns, as follows: In the Design View, on the Arrange tab, click AutoFormat and choose the desired background.

Earlier, we discussed finding data, in the sense of finding a record with a desired data value. A somewhat more general problem: find the set of records with specified properties. This process is called filtering a database. We consider filtering a table, and a query.

To filter a table: the table should be in view. On the Home tab, note the Sort and Filter section of the ribbon. If you click the Advanced button and choose Filter by Form, a filter design datasheet appears. We might have to clear a previous filter design (one way: use the Del key; another: select, from the Advanced menu, select Clear All Filters). Under the field(s) used to specify the desired properties, specify the desired value or range of values (for a range, much as for a Validation Rule). After the filter is designed, click the Toggle Filter button to apply the filter (that is, to display the records that satisfy the filter specifications). To remove the filter, click the Toggle Filter button.

Some examples:

  • To select all records, and only those records, with the value “Dr.” in the Title field, select “Dr.” under Title.
  • To select all records, and only those records, with a restaurant charge of at least $100, on the filter design, under Restaurant Charge, use

>=100

(as in a Validation Rule, the comparison implicitly uses the field in whose column the specification is given, without naming the field).

  • To select those records with a checked checkbox for Parking, under Parking in the filter design, check the checkbox. To select those records with an unchecked checkbox for Parking, in the filter design, check the checkbox and then click the checkbox again to uncheck it. Thus, we distinguish between not using Parking for the filter and using Parking to select records with an unchecked checkbox (because both of these cases would have an unchecked checkbox).
  • To use the logic of the AND operator: To place restrictions in two different fields, use the same tab of the filter design and create the appropriate restrictions. To place multiple restrictions in the same field, use the word “and” between the conjuncts.
  • To select, say, the records of female doctors – that is, records showing “F” (without quotation marks) under Gender and “Dr.” under title, we can use the Look For tab, specifying “Dr.” under title and “F” under Gender.
  • To select, say, the records of those with last initials H, I, J, K, L, we can use, under LastName in the filter design,

>="H" And <"M"

Note we use <"M" rather than <”L” because, for example,

“Long” <”L”

is false, but

“Long” < “M”

is true.

  • The asterisk is a wildcard for text values, meaning it will match any text pattern. For example, if we want the records of those from New York with last initial “D”, we can place, on the Look For tab,

D*

or

Like "D*"

under LastName, and “NY” under StateOrProvince.

  • The special constant Null is used to describe a text value of 0 characters. The operators Is and Not are used to compare text values with Null. For example, to select all apartment dwellers, under Apartment we can use

Is Not Null

Similarly, to select the records of US residents, we can use, under Country

Is Null

  • To use the OR operator with restrictions in the same field, you can use “or” in the same cell of the filter design. For example, to select the records of those who are from New York or Ontario, we can use, under StateOrProvince,

“NY” or “ONT”

  • To use the OR operator with multiple fields, use different tabs of the query design. For example, to select the records with a high (at least $175) room charge or a high (over $100) restaurant charge, we can design a filter as follows: On the Look For tab, under RoomCharge, use the expression

>=175

and, on the Or tab, under RestaurantCharge, use

>100

Note data from any datasheet may be copied into a Word document. You might use a query and copy its results into a Word document, e.g.,

Here are the registrants from other countries:

LastName / FirstName / City / StateOrProvince / Country
Santana / Diego / Toronto / ONT / Canada

A query allows you to filter; also, to sort more powerfully than with a table; also, to suppress the view of fields you don’t wish to view.

To create a query: on the Create tab, you can use either the Query Wizard button or the Query Design button. I’ll demonstrate the latter.

When you click the Query Design button, the Show Table dialogbox appears. Use it to choose the datasheet that will be the source of records for the query being designed. Click the Add button (to make available the fields of the selected datasheet) and the Close or exit button to close the Show Table dialogbox.