Microsoft® Access 97 Quick Reference
Before you enter any information in Access, you have to create a table somewhere. A table is the foundation for all queries, forms, and reports. The container for all these objects is the database.
In this part, you find tasks showing how to create your database file and the tables that belong to the database. You follow steps for creating the basic building blocks of a table--fields. In addition, you find reference information for setting the data type, format, default value, size, and other properties of fields. To speed up data retrieval and link to other tables, you can also index the fields, set their primary keys, and create relationships between tables.
When entering information into your database records, you can leave data out of a field if it is not available. However, a blank field can mean more than one thing. It might signify that you don't know what the field's value is or that a value for the field doesn't exist. You can leave a field blank if you don't know what the value is (the value in this field is actually called a Null value). If you set the Zero-length property, you can enter "" (two quotes) in the field to indicate there is no value.
In addition to decimal places (see "Decimal Places"), you can change the format of a number so that all numbers look consistent in your table. Numeric formats include dollar signs, percent signs, and commas. Date formats include spelling the months or using numbers for the month, and how many digits to use for the day and year. Text formats include capitalization.
Queries, forms, and reports also enable you to change the format places. On these objects you can change Format by right-clicking the field or control in Design View and making the change on the Property sheet in the forms and Reports part of this book. (See "Controls: Properties Change " in the Forms and Reports part of this book.)
TIP: Text format types do not appear in a drop-down list. Type > to convert your entry to all uppercase. You can also press F1 while you are in the Format property box for more detailed codes on all data types.
When you create a table, you specify the data type for each field in the Table Design View. Text, Number, Date/Time, Currency, and Yes/No data types are self-explanatory. AutoNumber, formerly called Counter in previous versions of Access, increments each record by one. While Text fields can only contain up to 255 characters, Memo can include over 65,000 characters. OLE Objects enable you to insert graphics, sounds, and other data types. The Lookup data type will enable you to choose from a list of options. (See "Lookup Columns: Create with Wizard.") The Hyperlink data type enables you to launch an Access object (a table, form, query, and so on), another file in another application, or go to a Web site. (See "Hyperlinks: Create Field.")
CAUTION: You can change a field's data type but, depending on the particular conversion, this process can lead to data loss.
When you first create a field in Table Design View, the field is automatically set to text and the default text field size is 50. If most of your fields are not text or are a different size, you can change these defaults.
NOTE: When you import data from another source, you can type which names of fields will automatically be indexed in the AutoIndex on Import/Create text box.
A blank database is a database file that contains no objects or data. It is an empty shell that you will use to add new tables, queries, forms, and reports. If you want Access to create some of your tables, forms, and other objects, see "Database: Create New with a Wizard."
NOTE: You can also press Ctrl+N to start a new database from anywhere in Access. After you give the database a name, Access closes the database that you were working on and opens a blank Database window.
You can create a new database that is blank or let the Database Wizard create one for you that contains the objects you specify for it. The task, "Database: Create Blank" describes the former process. This task describes the use of the Database Wizard. Both procedures begin the same way.
When you use a wizard, Access enables you to choose from sample fields and then create the tables, queries, forms, reports, and switchboards for you. A switchboard is a form with buttons that help a user navigate through the database. You can even have Access populate your database with sample data to help you learn how different features work in the sample database.
Click the check boxes next to include optional fields (in italics) or uncheck any other fields. Click the check box next to the Yes, Include Sample Data if you want that feature, then choose Next.
NOTE: To see which sample database wizards are available, look on the Database tab of the New dialog box. These wizards are installed during the default setup. However, if you do not have the wizards, return to setup (through your Office or Access CD), choose the Chang e Option button while Microsoft Access is selected, and check the Wizards box.
NOTE: On the second to last step of the new Database Wizard, you can include a picture on your reports by clicking the Yes, I'd Like to Include a Picture check box, then choose the Picture button to specify a picture file in the Insert Picture dialog box. Several graphic formats such as bitmaps, icons, the Windows Metafile, TIFF, PCX, PICT, JPEG, GIF, and EPS are supported.
Data documentation can help you get organized, especially for large databases. Database dictionaries describe the database as a whole, each table, and each field. Access has a Database Documentor that describes these and other parts of your database for you.
One of the best ways to learn Access and find ideas for your own databases is to look at the samples that come with the program. These sample databases might have been installed when you set up Microsoft Office. If not, you will need to go through setup. The files are Northwind, Orders, and Solutions. The first database you should look at is Northwind. Orders and Solutions provide examples for application development and programming.
NOTE: The Northwind database includes a Show Me menu that explains features of the application. Orders and Solutions have a Show Me button on their toolbars.
There are numerous ways you can alter the appearance of your datasheet. You must have the datasheet for that table in view; these settings apply universally to the table, and not to individual cells. You can modify a table datasheet, query datasheet, or the Form View of a form.
Additionally, you can change the width of a column or the height of all rows in the datasheet using the Format, Column Width (see "Width of Column" in the Database Essentials part of this book) or Format, Row Height commands. Each column can have its own width, but all rows must be the same size. You can also hide (see "Hide Columns") or freeze (see "Freeze Display of a Table Field" in the Database Essentials part of this book) columns to help see the more of the datasheet.
When you use the Format menu to change the datasheet appearance, only the viewable datasheet changes. (See "Datasheet: Appearance Change.") You can also change the appearance for all datasheets you have not individually changed.
Decimal places are basically the numbers after the decimal point (for example, 25 becomes 25.00 when you add two decimal places). When you work with numbers, having all related numbers with the same number of decimal places adds to the professional appearance of your output. Changing the number of decimal places only adds to the visual appearance of a number, not to its value. Another option is to use the Rnd (Round) function, which will change the value of a number. If you want to add commas or other symbols with numbers, change the format property. (See "Data: Format.")
Forms and reports also enable you to change decimal places. On forms and reports, you can change decimal places by right-clicking the control in Design View and making the change on the Property sheet. (See "Controls: Properties Change" in the Forms and Reports part of this book.)
Captions enable you to uniquely name the column header in Datasheet View (as opposed to calling it by the field name). After you add a caption to a field, any new queries, forms, or reports will use the caption as the default for column headers or labels for the field. Queries also have a caption property for each field.
NOTE: Labels and column headers for existing queries, forms, and reports do not change when you change the field's caption in Table Design View.
You must create a field in a table to use it in a query, form, or report. A field holds one specific piece of information in a record. Examples include a company name or a salary. It is better to condense the information that goes into a field into the smallest unit you can use. For example, instead of having an entire name in a field, use at least two fields--one for first name and one for last name. You will then be able to sort, find, and group information on the last name and use both name fields for mailing labels and letters.
NOTE: The description and other field properties become the defaults for many of the control properties in forms. However, if you change the table's field properties after you create a form, most of the properties do not change on the form.
You can set a default value for a field in the Table Design View on the General tab. When you enter a value or expression in that property, that default value is entered into each new record when you create the record. You are free to overwrite the default value if you have write privileges for that field. The default value does not affect any records you enter before you create the default value. You can have a different default value for the field on a form than the one you create for the table. (See "Forms: Default Value" in the Forms and Reports part of this book.)
A common default value would be a state or country (entered as CO or USA). Another common default would be today's date, which you enter as Date().
Because this default value is applied at the table level, the mechanism for entering the default value operates in a datasheet or form. During an append operation, default values are not added to the new records that are appended to the table.
TIP: Creating default values is a great time saver and speeds up data entry. When you have a field that usually has the same value entered into it, consider setting this property.
If you no longer need a field, you can delete it. For instance, after you import a table from another source you can change the table design to add fields for first name and last name. After you enter these data for these fields for all your records, you then want to delete the original name field that contained both names.
CAUTION: Be sure that you won't be using the field again when you delete. All the information in the field is lost for every record. It will be a large task to find and enter the information for a mistakenly deleted field. You can use Undo if you immediately notice you deleted the wrong field or if you choose, when prompted, to not save changes to the database design. However, this task is so potentially dangerous that you should probably back up your database first. (See "Backup Data" in the File Management part of this book.)
The Description property provides information or notes about fields in tables, and queries. Descriptions can be up to 255 characters in length. Descriptions appear in the status bar while entering data in a field in Datasheet or Form View.
This propertyx2 is set in the Table Design View for tables, and in the Field Properties Property sheet in the Query window for queries. (See "Queries and Filters: Query: Properties" in the Queries and Filters part of this book.)
When you create a control by dragging a field from a Field List, Access copies the Description property to the control's Status Bar Text property. It then displays that description in the status bar whenever the insertion point is entered into that field.
You can add a field at the bottom of the field names section Table Design window (see "Field: Create") or you can insert a field in between existing fields.
You can change the name of a field in your database and your table's data is left unaffected. However, if you have used the field in a query, form, or report created prior to the change, you must manually update that control to reflect the new field name. If you want to see a different name in the column header of Datasheet View, you can also change the caption property. (See "Fields: Caption as Alternate Name.")
NOTE: Field names can be up to 64 characters and include spaces. However, if you are going to upsize your database to a database server such as SQL, it is better not to include spaces because the table's field name spaces will be converted to underscores. Any queries, forms, or reports based on the tables will produce errors and will need to be modified.
To rename the field in a query, change the name of the field in the Query Design grid. That new name provides the column name for the field in Datasheet View, unless the Caption property has been set (in which case, the caption is used). The renamed field also provides the name of the Control Source for any control in a form or report that is based on that query.
You can set many of the field properties in Table Design View. Properties include name, data type, description, field length, validation rules, default values, and whatever you see on the General or Lookup tabs in table design.
When you click in the text box for some of the properties, there is a drop-down arrow representing a list of choices. Click the arrow and then the desired item in the list. Some properties (for example, Input Mask) also have a build button (...) on the right side of the text box. You can click this or the Build button on the toolbar to bring up a dialog box with examples. Another option is to begin typing in the text box. Access will automatically complete the entry with the first available option where the first letter matches your entry. For example, in the data type text box, type n to select number.
TIP: Double-click any property that has multiple choices either in the Data Type or lower section of the design window. This will cycle through the available list of choices. (This is a general feature of Property sheets.)
Field size for text data type fields determines the maximum number of characters you can enter for a field. You can set the field size for text up to 255 characters and the default is 50 characters unless you change the default. (See "Data Types: Setting Defaults.")
For numbers, field size determines the range of numbers you can enter and whether or not the number can include decimal places. Generally, you want to set the smallest possible field size for text or number but still include all possibilities you might enter. With smaller field sizes, your database file will be smaller and quicker.
You want to choose one of the following numeric field sizes that will accommodate your data using the smallest number of bytes possible.
| Option | Description | Byte Size |
| Byte | Numbers 0-255 without decimals | 1 |
| Integer | Numbers from about -32,000 to +32,000 with no decimals | 2 |
| Long Integer | Very large numbers without decimals (+/- 2 billion) | 4 |
| Single | Large numbers with decimals (up to 38 digits before or after the decimal place) | 4 |
| Double | Largest possible numbers with decimals | 8 |
NOTE: When you create relationships between fields from different tables, all data types and field sizes for numbers must match. The exception is an AutoNumber field. Because the foreign key field will not be an AutoNumber field, the related field in the second table should have Long Integer Field Size.
TIP: If you often calculate with a field that has between one and four decimal places, consider using Currency data type instead of Single or Double. Currency uses the faster fixed-point calculation rather than floating point calculations.
Access normally prints and displays vertical and horizontal gridlines in Datasheet View. If you want, you can turn these gridlines off. You can also change the background or font of the cells. (See "Datasheet: Appearance Change.")
In some cases you might not want to see all the columns of the datasheet. Perhaps your display is too wide, you don't need to enter all information, or you only want to see relevant information to your task. Another option to help you navigate with many columns is to freeze columns. (See "Freeze Display of a Table Field" in the Database Essentials part of this book.)
To return a hidden column to view, use the Format, Unhide Columns command and check the box next to the column you want to see.
CAUTION: When hiding columns from view, be careful that you don't inadvertently neglect their data entry.
You can use hyperlinks in forms and datasheets to jump to the location described in that hyperlink. Locations can be other objects in Access databases, documents created by Word, Excel, or PowerPoint, and documents on the Internet or an intranet.
Access contains a new data type called a hyperlink field. A hyperlink field contains the text and numbers that comprise a hyperlink address, which is the path to the object, document, or Web page. A hyperlink address can also be an URL (Uniform Resource Locator) for an Internet or intranet address. Access recognizes a hyperlink address from the entered syntax.
Access enables you to create indexes based on two or more fields in your table, up to a limit of 10 fields. You can specify that a composite index is unique and use it as a primary key, or use that composite index to speed up sorting or searching through your data. Access does not allow you to index on expressions. However, in many cases a single field index will be sufficient. (See "Index: Create Based on a Single Field.") A multiple field index can also make up a primary key. (See "Index: Primary Key.")
To remove an index, click the Indexes button again, select the rows making up the index and press Delete. When searching or sorting on the non-indexed field, Access now takes longer. If you have no index at all in a table, Access orders the records in the order you enter them in the table.
You can index a single field to serve as a method for ensuring unique values, to sort your data, or to speed up search and retrieval operations.
When you index a field, you have two options. Yes (No Duplicates) means that you will not have any entries that match in more than one record. Yes (Duplicates OK) means that entries can match.
A primary key is the index used to uniquely identify records in a table. Every table should have one primary index, although other unique indexes can be defined (as so-called candidate indexes). Often, the primary index is used to establish a relationship with a child table. (See "Relationships Between Tables.") The field that the primary key is related to in the other table is called a foreign key. A primary key can contain one or more fields.
To remove a single field primary key, with that field selected in the Table Design View, click the Primary Key button on the toolbar again, or delete the index from the Indexes window.
Indexes are listings of values or expressions in a field or combination of fields. An index in a database operates just like the index in a book. Indexes point to where something is located.
Indexes are particularly valuable in a number of database operations. They speed up finding and sorting information when you perform those operations later and also change the sorted view of your table. Indexes are necessary to match the data in a field of one table to a field in another, and thus provide the means for relating one table to another.
In the Indexes window you can set index properties: the Index Name (which by default takes the field name but can be changed), the Sort Order, and whether the index is Primary, Unique, or Ignores Nulls. The Ignore Nulls option makes the index smaller and speeds up searching records.
As you enter information in Text and Date data type fields, you might want certain symbols to appear. For example, a phone number has parentheses and a dash. You can manually type these symbols in each text field or you can create an input mask to automatically do the job. An input mask can also verify each character as you type it. To change the display of an entry after you type the entry and move out of the field, you can also format the field. (See "Data: Format.")
TIP: The input mask wizard gives examples of the most common input masks you might want. These include phone number, social security number, long zip codes, passwords, and date and time values.
CAUTION: Make sure your other field properties (such as Format, Default Value, Validation Rule, and Required) do not conflict with your input mask.
TIP: It is generally better not to include symbols with the table because the entries will be shorter (and thus take up less room in your database). However, if you will be exporting this data to a spreadsheet or other database, you might want to include the input mask symbols.
NOTE: You can also type Input Mask characters directly in the property box in Table Design View. For a description of the acceptable characters, click in the Input Mask box and press F1.
There are many instances when you might want to look up information to place in a field. This is especially true when you have codes representing values. Instead of trying to remember the codes, you can create a lookup column that enables you to choose something like the employee name rather than remember their employee identification number.
NOTE: The first step of the Lookup Wizard also asks you if you want to type the values rather than use an existing table. It is generally a better idea to use a table because you can use it for more than one combo or list box.
After you create a lookup column (see "Lookup Column: Create with Wizard"), you might want to change or verify the lookup properties for the field.
The Lookup Column properties identify the source and organization for your drop-down menu. One important lookup property is the Row Source, which can be a query or SQL statement. You can edit the SQL statement by clicking the build button (...) to the right of the Row Source text box and then manipulate the query builder just like a normal query. (See "Query: Create with Design View" in the Queries and Filters part of this book.)
When you define a relationship between two tables, you match the values in one table to values in another table. In order to create a relationship, one or both of the tables requires that the values used in the match be unique. Normally an index (usually the primary key) in the controlling or parent table is used, and a field (called the foreign key) in the child table is matched.
In addition to defining a relationship, you set referential integrity rules in the relationship dialog box. When you enforce referential integrity, you say that you do not want any orphan records in the child table. Orphans occur when no records are matched to the parent table. If you choose Cascade U_pdate Related Records, whenever you change the ID field in the parent table, the field in all corresponding records change in the child table. If you choose Cascade Delete Related Records, you will delete any children records when you delete the parent record. If you choose neither while enforcing referential integrity, you will be unable to update the ID field or delete the record when child records exist.
The relationship is represented by a line between the two tables. You can select a relationship and press the Delete key to remove it. You can also right-click the line to view the shortcut menu, and select Edit Relationships to open the Relationships dialog box. A command button on the Relationships dialog box is join type. You can choose this to set the default join type for queries. (See "Tables: Combine with Join" in the Queries and Filters part of this book.)
The description property of a field appears on the status bar when you are in Datasheet View or Form View. The description becomes the default for the Status Bar property on a form that you can modify. (See "Controls: Properties Change" in the Forms and Reports part of this book.)
NOTE: If the status bar does not appear on your screen, choose Tools, Options, View tab, and check the Status Bar check box in the Show section.
The Table Wizard is a fast way of creating tables. It lets you structure tables based on fields in existing tables, create rudimentary table relationships, and specify a primary key.
If you have existing data, you can also import or link the information to create a table. (See "Import Data" in the File Management part of this book.) You can also create a table by going directly to design window (see "Table: Create in Design View") or by working in Datasheet View (see "Table: Create in Datasheet View").
Access creates the new table and saves it to disk. If you select to modify the table design, you view the Design window (see also "Table: Create in Design View"). For the Enter Data Directly selection in the last step, you see a Datasheet window. For the form selection, a form is created for you.
The datasheet method is a very fast method for creating tables, but is limited in its capabilities. It is best used for small tables where you will add features later to the table design. It does not create table relationships, nor does it provide for data validation or other table properties.
NOTE: Use a consistent style of data within a column for dates, times, numbers, and so on, so that Access can create a data type and display format based on the values it sees you enter.
TIP: If you need more than 20 columns, click a column to the right of your new field, then select the Column command from the Insert menu. Access will automatically add rows after the 30th record.
Access creates the new table and saves it to disk. When you have Access create the primary key, it creates an AutoNumber field that has sequential numbers entered into it.
A convenient place to create the structure of your database tables is Table Design View. This is where you add and remove fields, and it serves as a convenient venue for getting an overview of the properties associated with your fields and table.
Access creates the new table and saves it to disk.
NOTE: You don't have to assign a primary key, but it is recom-mended. Make sure that the order of a compound primary key is correct. You can change the order by clicking the Indexes button on the toolbar and reordering the field names in the index that comprises the Primary Key. However, a compound primary key is used infrequently compared to a single field primary key.
To modify a table, you must select that table in the Database window and open the table in the Table Design View. In this view you can add or remove fields; change field names; change a field's data type; and add, modify, or delete descriptions, field properties, and table rules.
CAUTION: Pay particular attention to modifying the data type of an existing field. When you change data types, there is the potential for data loss due to data type mismatch. Your previous field's data may be truncated or discarded completely. Once it is gone, it is gone forever. Therefore, it is a good idea to make a backup of your database or table before you change the design of a table. (See "Backup Data" and "Database Object: Copy" in the File Management part of this book.)
Access saves your table to disk. If you create a new table, that table's name appears in the Table tab of the Database window.
A number of important table properties can be specified that affect how data is stored and accessed. Two different groups of properties can be accessed. The first set of properties are object properties that are the same for queries, forms, reports, macros, and modules. They include the object description, whether you want to hide the object in the database window, and whether you want to replicate the object. (See "Replication: Create Replica" in the File Management part of this book.)
The second set of properties you access from inside of Table Design View. You can also set the table description here. Other table properties you can set in Table Design View include a sort order, whether you want to filter out records, and a validation rule involving more than one field and the rule's associated error message. (See "Validate Data: Record Validation.")
Of the two different types of Properties sheets, the latter is more useful and used more often. When you open the table from the Database window the Filter and Order By properties are not applied. Click the Apply Filter button to see the results.
You can create rules for a field where the values you enter must fall in a specified range. Otherwise the data is not acceptable. Most likely, the user made a typing error. If you need to use a field name in the expression, you will need to change the Table's Validation Rule property. (See "Validate Data: Table Validation" in the Table and database part of this book.)
Examples of validation rules include >100 and between 0 and 50 for number fields, or >Date() (greater than today's date). See the Criteria sections in the Queries and Filters part of this book and Expression sections in the Calculations part of this book.
You can create a table validation rule to validate the data entered into two or more fields in a record. When you move off the record, Access checks that the table validation rule is not violated. You cannot leave the record without either removing the record's data or fixing a record so that it conforms to record validation.
You enter the record validation rule in the Properties sheet for the table. An example of a table validation rule is:
[ShipDate]>=[OrderDate]. This means that the shipping date has to be greater than or equal to the order date.
Some information in a table is so important that the record would be useless without the information. This is often the case with name fields. You might want to force yourself or another user to enter information in these fields before they can leave the record.
If you are missing data in this field after you change the Required property to Yes, Access will warn you that the existing data violates the rules you just made. After you save the table, go back and add the missing data.
©Copyright, Macmillan Computer Publishing. All rights reserved.