LIS 505 - Microsoft Access introduction

Microsoft Access is a widely used relational database management system. The following deals with some very basic features. Access is covered in somewhat more detail in LIS 558, Database Management Systems.

Access stores an entire database - including tables, indexes, queries, reports, forms, and scripts - in a single file with the extension .mdb.

Wizards are provided to help in creating certain types of database, or you can choose to start with a blank database and then define your own tables, reports, and so on.

There are also wizards for individual parts of the database (tables, queries, forms, reports, and Web pages), or, again, you can design these from scratch yourself.

Tables

For an object like a table, there are two main views available, one of which is Design View, which allows you to make changes to the structure of the object, and the other of which (Datasheet View, in the case of tables) allows you to use the object (for example, enter new data in a table, edit old data, view existing data). Several types of objects, including tables, also have PivotTable and PivotChart views, which provide ways of crosstabulating and charting field values.

If you look at a table in Design View, you see a list of its fields and their types. Common field types include Text, Number, Date/Time, Currency, Yes/No, OLE Object, and Autonumber. OLE Object fields can contain a wide variety of embedded formats, but may not integrate very well with other aspects of the database. The values in an Autonumber field are set automatically but otherwise behave like Number values.

When you select one of the fields in Design View, you will see that it has a number of other properties, displayed in the lower part of the window. Some field types have more properties than others; for example, an OLE Object field has only two properties, while a Text field has 13 General properties and one Lookup property. Field Size is the maximum size of a field value. Format defines how the field's contents will be displayed; for example, the Format of a Date/Time field might be Long Date, meaning that the month would be spelled out in full, but that the time of day would be omitted. The Decimal Places property applies to Number and Currency fields. Input Mask is similar to Format, but deals with what patterns of characters are permitted in input; the wizards are inclined to set too restrictive an input mask for a date, and you will then have to delate or modify the mask. Caption allows you to change text that will be used by default to label the field in reports and forms to something different from the field's name. Default Value is what is automatically entered for the field in a new record (for example, Date()), meaning today's date); the person entering the record can then change the value if needed. Validation Rule gives a rule that must be followed for the field value to be accepted (for example, >=Date(), meaning today's date or later); since validation rules cannot be overridden when entering data, you need to be careful when defining them. Validation Text is a message to show anyone who attempts to leave the field with a value that does not satisfy the validation rule. Required says whether the field must have something in it to be valid. Allow Zero Length applies only to Text, Memo, and Hyperlink Fields; it seems almost like the converse of Required, but there is actually a subtle difference, explained in the help file. Indexed means whether the field has a secondary index to speed up searching; for a short table, this property should be set to No. According to the help file, Unicode Compression is supposed to default to Yes, which could save considerable space when storing text data; but, in fact, it seems to default to No. The IME properties are not relevant unless you are entering Chinese, Japanese, or Korean text.

The Lookup properties, which you can access by clicking on the "Lookup" tab, can be used to provide users with a list to choose from instead of typing in a value for the field.

Datasheet View shows the table with each record in a different row and each field in a different column. You have some control over the appearance of the table. You can change the font, the border style, the height of rows (but not of an individual row), the width of columns, the order of columns, and the column headings (also changing the names of the corresponding fields). You can hide columns and freeze columns. You can also sort the rows (moving individual rows is messy and is not recommended). Using the "Records" menu, you can show only those records that satisfy certain criteria: "Filter by Form" provides simple selection lists from the values of fields; "Filter by Selection" gives a quicker method of selecting all the records that match a single field value in the current record; "Filter Excluding Selection" supplies the converse; "Advanced Filter/Sort" provides access to other kinds of selection, such as all those records in which a field's value falls in a particular range.

Queries

An Access table can have only one filter at a time, and the filter is either on or off. For more flexibility, you may want to use queries; more specifically, "Select" queries, since there are several other types of query that serve different purposes. A query is based on one or more tables or other queries and in some respects behaves like a table itself, but it does not actually contain its own data. You can create a query by saving a filter (by selecting "Advanced Filter/Sort" in Datasheet mode and then selecting "Save As Query..."). You can also create a query with the query wizard or in Design view.

In Design View for a query, you see a window divided into two panes. The top pane is for showing the structure of tables and/or other queries on which the query is based; for a query based on a single table, this is not particularly interesting, and you can generally ignore it. The bottom pane shows the structure of the query itself, in QBE (Query by Example) format. The "Field" row allows you to select fields (or define your own fields) that will play a role in the query: in defining the sort order or in setting criteria for which fields or which records are included. If the query uses more than one table or other query, the "Table" row lets you select which will be the source of the field named in the "Field" row. The "Sort" row allows you to specify whether the field will be used in sorting the query's results and whether the sorting should be ascending or descending. The "Show" row provides checkboxes that allow you to specify whether the field will appear in the query's results if the query's "Output All Fields" property is set to "No". Finally, the "Criteria" rows allow you to specify the criteria that must be met by records to be included in the query's results.

The simplest criterion is just an exact value (number or character string) that must be matched; for example, 505. More complex expressions can also be used in queries. Unfortunately, query Design View lacks the Expression Builder that is available when designing forms and reports, but you can find examples of query criteria expressions in Access' help file by selecting "Examples of Expressions" under "Examples of Expressions" under "Expressions" in the contents. Here are a few simple things you can do in expressions: indicate a range of values with Between and And (for example, Between 1 And 10); indicate upper or lower limits for a field (for example, <10 or >5); specify today's date with Date(); include wildcards in character strings with Like (for example, Like "Sm*th"); specify a particular date with DateSerial (for example, DateSerial(2003,12,31) for December 31, 2003).

Fields that you add in designing a query are called calculated fields. Instead of selecting from the drop-down list in the "Field" row, you type in a new field name, a colon, and a formula for the field's value. The formula should contain at least one reference to another field, enclosed in square brackets. For example, AmountDue: [TotalCharge]-[Deposit].

To access the query's general properties, right-click on the gray background and select "Properties". You can leave most of these properties as they are (if you want to know what one controls, click on it and press the F1 key). The "Output All Fields" property is important because it determines whether the "Show" checkboxes do something useful.

SQL View shows what the query looks like in SQL, a common language for communicating queries to remote databases; since Access actually translates all queries to SQL, it cannot handle certain more complicated QBE queries that are otherwise quite valid.

Forms

In addition to Design View and other views that you would have with a table, a form gives you Form View, the view in which the form is normally used to add new data and review and edit existing data.

If you create a form with the wizard, it will already contain a number of objects, such as labels for fields and text boxes for field values. In Design View, you should also see the Toolbox, from which you can select an object type and then insert an object of that type by clicking on the form.

You can change various properties of an object in a form by clicking on the object in Design View and using the Properties window (to make this appear, you can click on the "Properties" button on the Form Design toolbar). Examples of properties that you may wish to modify include text color, font size, date format, caption (of labels), source (of text boxes and other objects), and whether an object is a tab stop or not (that is, whether the user can move to the object by pressing the Tab key). You can also change the position and dimensions of objects by dragging on their "handles".

In Form View, you will normally see navigation buttons at the bottom that allow the user to move quickly from one record to another or add a new record; these are determined by properties of the form, and you can cause them to be hidden if you wish. For some reason, the only easy way to set a form's height is to drag on its lower border with the mouse; this is especially odd because forms do show a Width property which you can use to control their width exactly.

Reports

Reports are similar to forms except that they are designed for printing data out and are not used for editing or entering data. In Design View, you will see that a typical report has several "sections"; for example, headers for the report and the page and for groups (typically corresponding to headings under each of which which one or more items appear), detail for individual records, and footers for groups, the page, and the report.

You can choose to preview a report in either Print Preview or Layout Preview; the former shows the complete report while the latter shows only a few sample records.

Two object properties to pay special attention to in designing a report are Can Grow and Can Shrink; these allow the report to accommodate a field whose contents may be either long or short depending on the record, without cutting off long values or leaving excessive white space after short values.

You can change the sorting and grouping of fields in a report by selecting "Sorting and Grouping" from the "View" menu and using the Sorting and Grouping window.

Some summary values can be included in a report automatically by choosing the appropriate options in the report wizard; or you can include them in Design View by selecting "Text Box" from the Toolbox, clicking on a section of the report, and then changing the Source property of the text box to = followed by an appropriate expression. You can click on the three dots next to the property in the Properties window to call on the Expression Builder for assistance, though it is easier to type in the value directly if you know what you want. For example, a text box to show the average value of a field called DepositDue might have its Source set to =Avg([DepositDue]).

Note that summary values have a slightly different meaning when they appear in group headers and footers: they apply only to records in the group. Elsewhere, they apply to all the records covered by the report.


Home

Last updated February 11, 2004.
This page maintained by Prof. Tim Craven
E-mail (text/plain only): craven@uwo.ca
Faculty of Information and Media Studies
University of Western Ontario,
London, Ontario
Canada, N6A 5B7