technology@msb   MSB Home  

Access 97/2000: Tables and Queries

What is Microsoft Access?

Microsoft Access is a Relational Database Management System (RDBMS) designed to run in Microsoft Windows. As yet, Access does not have a version for Macintosh. Unlike older Database Management Systems for IBM-compatibles, Access takes advantage of Windows' graphical interface to simplify complex database operations and does not require you to learn sophisticated programming languages. Access contains powerful querying and connective capabilities that allow you to easily manipulate the data in your database. Access’ graphical interface also makes designing sophisticated forms and reports easy, which facilitates effective data presentation both on-screen and in publication-quality documents.

In using Access, there are four main types of objects that you need to know: Tables, Queries, Forms, and Reports. The first two will be discussed in this handout and the final two will be covered in the next one. Each object type performs a very specific function in Access. Tables are used to hold your data. Queries allow you to organize and extract your data. Forms offer on-screen interactivity with your data, and Reports print your data in presentation-quality documents. Every database that you use will take advantage of these four types of objects to make the database easier to navigate.

Access 97 Vs. Access 2000

The biggest difference between these programs is not readily visible. Microsoft designed Access 2000 to be international, building the database in a character-set called UNICODE. Access 97 uses ASCII, a character subset of UNICODE that contains characters mostly useful for American English words. (ASCII doesn't have much accentuation, etc.) This fundamental change in the underlying database construction has two results: 1) your database is bigger because UNICODE takes up more space; and 2) Access 97 cannot read full-fledged Access 2000 databased because Access 97 doesn't understand UNICODE. Solution? Either migrate entirely to Access 2000, keep a copy of Access 97 on your machine for shared databases, or use Access 2000 to open your database "read-only," meaning that you can edit data in the tables of the shared database, but you cannot change the structure of the database (add/edit tables, fields, queries, relationships, etc.).

Navigating in Access

Access can feel a bit strange when you first start to use it. You still have the usual toolbars and menus that you get in the other Microsoft Office programs, but you also get the added complexity of different object types and multiple objects of the same type. Access uses the Database Explorer to facilitate getting around inside the database. To use this tool, click on the appropriate tab to select your object type. Then, double-click on the object that you want to open. To create a new table, query, form, or report, use the New button in the database explorer. Do not use the New button on the toolbar because that will create a new database and close the one you are currently working in. To edit an object’s design, single-click on the object’s name, then click on the Design button.

Some databases that you use will not have the database explorer visible. One option that you can use to see it is to choose Unhide from the Window menu. In the dialog box that appears, double click on the name of the database you are working in. This method will not always work, but it frequently does.

Viewing Options for Tables

Since your viewing options differ depending on where you are in the program and what kind of object you wish to view, look at the View menu to see what your options are. In the Database window, for example, your viewing options are limited to choosing the kind of object you wish to examine. When you are working with Tables, however, you can select either Datasheet View or Design View . Inside of each object, you will have different views of that object. The most useful ones are listed below and be found either through the View menu or on the toolbars inside of each object.

Design View allows you to see how an object has been put together and to modify its structure or layout. This view can be accessed for any Form, Report, Table, or Query. Datasheet View allows you to look at the data in a table or query in a spreadsheet-like format. It is available for any Form, Table, or Query. Other helpful views include Print Preview and Sample Preview . These views let you see how a Report will look before you print it. Sample Preview shows only a few sample records, while Print Preview shows all of the records in the database.

What are Tables?

Data are always stored in tables. All of the other object types are built on top of your tables. They display the data inside your tables and write data changes back into your tables. Each table contains information about one subject, such as employees, members of the class of 1998, or addresses. Most relational databases have many tables, each storing different information about a related subject. One table might store customer names and addresses while another stores customer orders. Tables are made up of records, which contain all of the information about a single item, such as an employee or address. Each record is then subdivided into fields, which are the smallest increments of useful and discrete data in your database. It is always a good idea to make the fields as precise as you possibly can in the beginning. If you are designing an address book, then it would be better to include three fields for First Name, Middle Name, and Last Name, rather than to include only one field for a person’s name.

Creating New Tables

When you create a table, you should think carefully about the kind of data it will contain and how you will want to use that data later on. Access makes designing certain kinds of tables easier for you by providing Wizards that automatically create tables for uses as diverse as personal exercise logs, medical records, contact lists, student/class rolls, and cataloging record and book collections. The Wizards work fine for many tables, but you will find that creating your own tables offers much more flexibility for your database.

Creating Tables with the Table Wizard

  1. In the Database window, click the Table button (or choose Tables from the View menu)
  2. Choose the New button.
  3. Choose the Table Wizard button.
  4. Follow the directions in the Wizard dialog boxes for adding fields to your table.

Creating tables without the Table Wizard

  1. In the Database window, click the Table button.
  2. Choose the New button.
  3. Choose the Design View button or the Datasheet View button.
  4. The table’s Design window or Datasheet will appear.

Design View: Changing Table Properties

While you can choose to create a table in either Datasheet or Design View, the Design Window offers much more flexibility in creating a database. The first noticeable difference between the design window and the database window is that the standard toolbar has been replaced by the Table Design toolbar. This toolbar offers several shortcuts to make designing tables easier. The Table Design window itself is divided into two principal parts: the area for creating field names and descriptions, and the Field Properties area below it.

Field Names

The Field Name is a descriptive identifier for a field that can be up to 64 characters (letters or numbers) including spaces. The names should be descriptive enough that anyone can easily identify them when viewing or editing records. For example, LastName, FirstName, StreetAddress, or HomePhone. To create a new field, simply position your cursor in a blank cell and enter a new name.

Field Descriptions

A Field Description, an even more descriptive identifier for the field, helps you make your tables easier to understand and update. For example, if you are making a database of all the records you own and have a Name field, you might clarify that entry by describing it as “The title of the album, CD, or tape.” This somewhat optional field is actually much more important than you think. As you or your users traverse the fields in your tables (or later in your forms), this description will appear in the bottom left-hand corner of the screen, giving those who may not understand your field names a better idea of what information should be entered into the field. Accordingly, you might want to flesh out your field descriptions, even giving them an example to work from (“The year of release (e.g., 1999).”).

Data Types

After you name a field, you choose a data type for the data to be contained in the field. When you choose a field’s data type , you are deciding:
  • What kind of values to allow in the field. You cannot store text in field with the Number data type.
  • How much storage space Access is to set aside for the data in that field.
  • What types of operations can be performed on the values in that field. Access can find the sum of Number or Currency values but not of Text values.
  • Whether Access can index or sort data in the field. Access cannot sort or create an index for Memo or OLE Object fields.
The following table summarizes each data type.
Data Type
Stores
Size
Text
Alphanumeric characters
Up to 255 characters (255 bytes)
Memo
Alphanumeric characters that are usually several sentences in length
Up to 64,000 bytes (large text fields). NOTE: Memo field is not searchable!
Number
Numeric values (integer or fractional)
1, 2, 4, or 8 bytes
Date/Time
Dates and Times (various formats)
8 bytes
Currency
Monetary values
8 bytes
AutoNumber
A numeric value that is automatically incremented for each record added
4 bytes
Yes/No
Simple yes or no, true or false
1 bit (8 bits = 1 byte)
OLE object
OLE objects, graphics, or other binary data
Up to 1 gigabyte
Hyperlink
Addresses for documents that you want to link to that are stored outside your database
Up to 2048 characters
Lookup Column
Creates a drop-down list of values to choose from in Datasheet View
Up to 255 characters

Datasheet View: Adding and Saving Records

The other way to look at your table is in the Datasheet view. Generally, you will want to use the Design view to work on layout and other formatting options. The Datasheet view is most helpful for entering new records and editing old records in a table. To switch to Datasheet view, choose Datasheet from the View menu or click the Datasheet View button on the toolbar.

Adding a Record

  1. Make sure that your cursor is situated in the first blank cell on the table, then enter the desired data. If you are using an existing database, the "first blank cell" is at the bottom of the table. Watch the graphics symbol in the record selectors box just to the left of your record. It will change from the arrowhead, which indicates that it is the current record, to a pencil, which indicates that you are editing this record. Note: It will still be a pencil even if it is the first time that you have entered the record. If the locked record symbol appears, then the record has been locked by another user, and you do not have access to it.
  2. To get to the next field of the record, press <Tab> or use the mouse and click on the next cell. If you want to return to an earlier field, then press <Shift> + <Tab>.
  3. To save a record, you do not need to do anything more than move to the next record after entering data in the record’s last field. You can also save the record by closing the table. You do not need to save the table to save any changes you have made to the records inside it. Saving the table saves only design changes to the table.
Warning: If you reboot your machine or exit Access while editing a record, you will lose the record.

Editing a Record

  1. Select the record that you wish to edit by clicking in the specific field you want to change.
  2. Type in the new data for that field.
  3. When you move to another record, the new data will overwrite the previous data.
Note: When editing data, Access will not prompt or remind you that any old information will be lost.

Adding/Deleting Fields

You can also add and delete fields in Datasheet view, but you do not have the same control over specifying parameters that you do in Design view. To create a new field in Datasheet view, either go to the Insert menu and choose Insert Column , or right-click where you want to add the new column and choose Insert Column from the menu displayed. To delete a field in Datasheet view, select the field by clicking in the field heading, go to the Edit menu and select Delete Column . You can also right-click on the field heading and choose Delete Column from the displayed menu.

What is a Query?

A query is a way of asking questions about data stored in your tables. Your query design tells Access exactly which data to retrieve. Queries allow you to look at selected data from one or more tables without viewing all of the fields in each table and without having to save all of your data over again. Queries make it easy for you to combine two or more table fields into a single field, to summarize data, to use criteria to select and sort data, and to add, update or delete large groups of records. The only parts of a query that Access stores are its design parameters. The data displayed in a query are called a dynaset (dynamic dataset) or recordset. Unless you design the query to save the dynaset as a new table or part of an existing table, Access will not store it.

Views in the Query Window

The query window has three main views: Design View , Datasheet View , and SQL View . In Design View you can create and modify the design of a new or existing query, using graphical tools. In Datasheet View you see the data retrieved by your query in a spreadsheet-like format. If you know Structured Query Language (SQL ), SQL View allows you to examine and modify the statements that underlie your query.

Two ways to design your own queries

Query Wizards

Wizards simplify the process of creating your own query, but they are very limited. With Query Wizards you can design the following types of queries:
  • Simple Query. Creates a simple, straightforward select query.
  • Crosstab Query. Organizes data for a summary appearance.

To Create a Query Using Query Wizards:

  1. In the Database window, click the Query button.
  2. Choose the New button.
  3. Choose the Query Wizard that you desire.
  4. Follow the directions as they are presented to you on the Cue Cards to create the type of query you want.

Do-It-Yourself

For many queries you want to design, Query Wizards will only be a starting point or will not help at all. To be flexible, you need to know how to create queries without resorting to Wizards.

To Create a Query Without Query Wizards:

  1. In the Database window, click the Query button.
  2. Choose the New button.
  3. Choose Design View . Access will then open a Select Query window (you can tell the type of query by looking at the top of the query window) and display the Add Table dialog box, which lists the tables and queries in your database.
  4. Select the table that contains the data you want to your query by clicking on it and then choosing the Add button. Or double-click on the table. If you want to select more than one table, hold down the <Ctrl> key and click on each table you want to add. Then choose the Add button. Access will display a field list for each table you select in the query window. Note: the field list may be obscured by the Add dialog box.
  5. Choose the Close button. If you have added tables that use the same field name and data type as earlier tables, then you will see lines in your query design window linking the tables.

Using the Query Design Window

Matching Fields and Linking Tables

Access automatically creates joins if two tables in your query have the same field name and data type, or you can create these joins yourself. Lines linking the tables from the matching fields will appear in your Query Design Window. Access uses the symbol “1” for the primary or source table and the “∞” symbol for the secondary side. This “one” to “many” relationship can be established between as few as two tables or as many as you want.

Query By Example Grid

After you add the tables to your query, a field list for each table appears in the upper half of the query’s Design window. The lower half of the window is known as the Query By Example (QBE) Grid. This grid is where the actual fields of your query appear. You can adjust the size of the QBE grid by clicking the mouse pointer on the line that separates the two portions and dragging.

The QBE grid shows the fields and properties that make up this query’s dynaset. The Field cell indicates the field that is being queried. The Table cell shows from which table the field is taken. The Sort cell tells you how the information in that field will be ordered in the query. The Show cell indicates whether the field will be shown in the query’s datasheet. For example, you sort an address book by last name, but because you have already combined first and last names as an expression in a separate QBE row, you might choose not to show the last name field. Criteria cells indicate how you will limit the search for the field in question. You can place criteria on each field being queried. You should remember that the selection process will follow the criteria from left to right on the QBE grid and that this may change the results of your query

Adding Fields to the QBE grid

The field list in the upper portion of the query design window displays the fields available for your query. To add a field to the query, select the field in the appropriate field list and drag it to a cell in the Field row of the QBE grid. To add more than one field, hold down the <Ctrl> key and select all the fields you want to add, and then drag them to a Field cell. Repeat this procedure until all the fields you want are added to the QBE grid. If you would like to add all the fields from a table or query to the grid, then simply click on the “ *" in the table’s field list and drag it to the QBE grid. This action means that all fields in the table will be added to the query.

Rearranging, Inserting, and Deleting Fields

Editing field selections is almost as easy as adding fields. You can move existing fields, insert new fields, or delete current fields by dragging and dropping.

To move a field or fields:

  1. Select the field by clicking the column selector above the field name.
  2. Click and drag the column selector to its new location. If you watch the lines separating the columns, the line where the field will be repositioned becomes darker and thicker.

To insert a field:

  1. From the field list, select the field you want to insert.
  2. Drag the field from the field list to a column in the QBE grid. If you drop the field on top of a pre-existing field, the new field will be added to the left of the old one.
  3. If you added the new field to a blank column at the end of the QBE grid, move it by selecting and dragging its column selector to its desired location.

To delete a field:

  1. Select the field to be deleted by clicking the column selector.
  2. From the Edit menu, select Delete (or press the <Delete> key). You can delete all of the fields in the grid by choosing Clear Grid from the Edit menu.

Renaming a field

To rename a field in your query, click to the left of the first letter in the field name in the QBE grid. Then, enter the new name followed by a colon. The new name is displayed as a datasheet column heading and will appear on any forms based on the query.

Datasheet View: Looking at a Dynaset

After you run your query, you will want to look at the dynaset it generates. The easiest way to do this is in Datasheet View . Select Datasheet from the View menu or click the Datasheet View button on the toolbar. To look at the dynaset in another way, run the query by clicking the Run button or selecting Run from the Query menu. With this method you should be careful when using a query other than Select Query. Access will actually run your query and manipulate your data as it has been told. Only Select Queries will end up in the Datasheet view.

Establishing a Sort Order

Possibly the most useful task that a database program can do is sort your data. Access sorts data alpha-numerically. Creating a query that sorts data can be useful when you want to display data in a form or report, or even if you want that data to appear in order in a combo box or list box on a form. You create a query that sorts the data and then use this query as the data source in a form, report, or box. To specify a sort order, click the Sort cell for the field you want to choose, then click the arrow in the cell. You then need to select from the list Ascending (0-9 and A-Z) or Descending (9-0 and Z-A). You can also specify a sort order for more than one field, like asking Access to sort by both First Name and Last Name fields in a mailing list. The sort order will start sorting from the left to the right of the QBE grid from left, so make sure that the Last Name field in the above example occurs on the grid prior to the First Name field. You cannot sort on Memo or OLE Object fields.

Boolean Operators

Access uses Boolean operators to carry out its queries. You will usually base every select query on these And, Or, and Not operators. The And function allows you to specify two or more criteria for selection. If you search a database for people living in Chapel Hill And people who own dogs, the only matches found will be those that fit both criteria. The Or operator lets you specify two or more criteria, and your data must meet at least one of the criteria. In an address book database, you might use this operator to find people living in Chapel Hill Or Durham Or Raleigh. The Not operator simply eliminates certain criteria from consideration. If you wanted to search your address book for everyone who does not live in the 27514 Zip Code, then you would type “ Not 27514” as your criteria. Commonly, you will use some combination of these operators. To specify several criteria for just one field, the And and Or operators are your best bets.

Using “Or”

If you were using a card catalog and wanted to see which books have been written by authors with the last names Faulkner and Joyce, in the Criteria cell for Last Name you would enter: “Faulkner” Or “Joyce”. Access provides a helping hand for designing “Or” queries. Instead of entering the full statement above, you could have entered “Faulkner” in the Criteria cell, then moved down one cell and entered “Joyce.” Access assumes that each extra Criteria row under the first one uses an Or operator.

Using “And”

If you were concerned about limiting this search to only William Faulkner, then you would need to use the And operator over two fields: First Name and Last Name . If you enter the criteria in each field’s Criteria cell, Access assumes that you want it to use the And operator as it reads the fields in QBE grid from left to right.

There will be times when you need to use the And and Or operators at the same time. You can combine the above operations. Access reads across each row of criteria from left to right, then starts moves down row by row. In order to combine your searches, you would need to know which items should go on the same row. To add anyone with the middle name of “Scott” to the previous search, move down one row in the Criteria cells and enter “Scott” in the Middle Name field.

Using Wildcards

If you want to select records that contain certain patterns of characters or cannot remember an exact data entry, use the wildcards (* and ?) in your search. The question mark (?) stands for any single character, while the asterisk (*) stands for any number of characters in the same position. For example, if you enter “*th” in a Criteria cell, both “125th” and “Perth” will be selected. The format for entering wildcards differs slightly from entering complete data. If you enter “*th,” Access inserts the Like command before the entered expression.

 
    Georgetown University