Web based School
 |  |
Chapter Two |
| 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.
Gridlines: Turning On and Off
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.")
Steps
- 1. Open a table, query, or form in Datasheet View.
- 2. Choose Format, Cells.
- 3. Uncheck one or both of the Horizontal and Vertical check boxes in the Gridline Shown section of the Format Cells dialog box; choose OK.
Hide Column
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 guide.)
Steps
- 1. Open a table, query, or form in Datasheet View.
- 2. Right-click the column header and choose Hide Columns.
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.
Hyperlinks: Create Field
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.
Steps
- 1. Open the table in the Design View by selecting it in the Database window on the Table tab and clicking the Design button.
- 2. In the field list, enter the field name for the new hyperlink field.
- 3. Tab to the Data Type column and select the Hyperlink data type.
4.Click the Close (X) button on the Table Design window and choose Yes to save the changes to the table design.
Index: Create a Composite Index
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.")
Steps
- 1. Click a table name in the Tables tab of the Database window, then choose the Design button.
- 2. Click the Indexes button on the toolbar.
- 3. Type an index name in the Index Name column of the Indexes window.
- 4. In the Field Name column, enter the first field in the index.
- 5. Add additional fields below that line, without naming another index, up to ten fields.
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.
Index: Create Based on a Single Field
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.
Steps
- 1. Click a table name in the Tables tab of the Database window, then choose the Design button.
- 2. Click the field in the table Design View and set the Indexed property in the General tab to one of the Yes options.
Index: Primary Key
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.
Steps
- 1. Click a table name in the Tables tab of the Database window, then choose the Design button.
2. Click the field in the Table Design View. If you want more than one field to make up the primary key, hold down Ctrl and click the field selectors of the other fields.
3. Click the Primary Key button on the toolbar to make the selected field(s) the primary key.
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.
Index: Set Index Properties
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 guide. 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.
Steps
- 1. Click a table name in the Tables tab of the Database window, then choose the Design button.
- 2. Click the Indexes button on the toolbar.
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.
Input Mask: Phone Number and Other Entries
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.
Steps
- 1. Click a table name in the Tables tab of the Database window, then choose the Design button.
- 2. Click the field and click in the input mask property at the bottom of the Table Design window.
- 3. If you want to use a predefined input mask, click the Build button to the right of the text box and choose one of the samples in the Input Mask Wizard; choose the Next button.
- 4. On the next two steps of the Input Mask Wizard, choose the placeholder character that you want to appear as the user types each character and whether you want to store the symbols with the table. Choose the Finish button when done.
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.
Lookup Column: Create with Wizard
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.
Steps
- 1. Click a table name in the Tables tab of the Database window, then choose the Design button.
- 2. If necessary, type the name for the field or go to an existing field. Choose Lookup Wizard as the data type.
- 3. If the data is from another table, identify that you want to use an existing table in the first step of the wizard and choose the table in the second step.
- 4. In the third and fourth step of the Lookup Wizard dialog box, double-click the fields that you want to appear in the lookup list and choose whether you want to hide the key column (usually an ID column).
- 5. Give the column a name in the last step of the Wizard and choose Finish. Access will prompt you to save the table.
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.
Lookup Column: Properties
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 guide.)
Steps
- 1. Click a table name in the Tables tab of the Database window, then choose the Design button.
2. Choose the lookup field and click the Lookup tab in the Field Properties section of the Table Design window.
3. The first property--Display Control--is usually set to Combo Box. This enables you to choose a drop-down arrow or type in the value. If you choose List Box, you only choose from the list. If you choose Text Box, you remove the lookup portion of the field and only type in the value in the field.
4. If you told the Lookup Wizard to use an existing table or query, the second property--Row Source Type--is Table/Query and the third property will be the name of a query or an SQL statement that you can edit by clicking the build button (...) on the right. If you typed a list of values in the Lookup Wizard, the values that you can edit appear in this area.
5. The Bound Column stores in the table the value from the specified column in Row Source. You might need to change the Column Widths property so you can see the entire columns from your Row Source. - 6. Click the Close (X) button on the Table Design window and choose Yes to save the changes to the table design.
Relationships Between Tables
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.
Steps
- 1. With the Database window showing, click the Relationships button on the toolbar to open the Relationship window.
- 2. Click and drag a relationship between a field from the parent table and the field in the child table.
- 3. The Relationships dialog box opens. In the lower half of the window, choose whether you want to enforce referential integrity and how the child table will be updated.
- 4. Finish creating the relationship by choosing OK and closing the relationship window.
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 guide.)
Status Bar: Display User Message
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 guide.)
Steps
- 1. Click a table name in the Tables tab of the Database window, then choose the Design button.
- 2. Choose the field, move to the Description column and type what you want to appear in the status bar.
3.Click the Close (X) button on the Table Design window and choose Yes to save the changes to the table design.
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.
Table: Create by Table Wizard
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 guide.) 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").
Steps
- 1. Click the Tables tab in the Database window, then choose New and then select Table Wizard in the New Table dialog box, then choose OK.
- 2. Click the Business or Personal option button to view a set of sample tables.
- 3. Select the table(s) you want to view fields from in the Sample Tables list box; then move the fields of interest from the Sample Fields list box to the Fields in My New Table list box; choose Next.
- Use the Rename Field button to rename any selected field you add. Access uses the same data type for your fields when you rename a field.
- 4. Enter a name for the table in the text box; select either Yes for the wizard to set a primary key, or No if you will set the primary key; choose Next.
- 5. Select any desired relationships in the My New <Tablename> Table Is list box; then choose the Next button.
- 6. Select from one of the following: Modify the Table Design; Enter Data Directly in the Table; or Enter Data Into the Table From a Form that the Wizard Creates for Me. Choose Finish.
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.
Table: Create in Datasheet View
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.
Steps
- 1. Click the Tables tab in the Database window, then choose the New button.
- 2. Select Datasheet View in the New Table dialog box.
- A datasheet with 20 columns and 30 rows appears with default field names.
- 3. Rename the column headings by double-clicking them and entering your field name(s); press Enter or click another column or value in the datasheet.
- 4. Enter data into the datasheet; each column is a field, each row is a record.
- 5. Click Save on the Table Datasheet toolbar. Enter the name of the table in the Table Name text box in the Save As dialog box, then choose OK.
- An alert box is posted asking you if Access can create a primary key; click Yes if you haven't created a field with unique values that can identify each row of your data-sheet (records in the table); click No if you have created such a field.
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.
Table: Create in Design View
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.
Steps
- 1. Click the Tables tab in the Database window, then choose New. Select Design View in the New Table dialog box, then choose OK.
- 2. Enter a name for a field in the Field Name column, then Tab and enter the data type in the Data Type column.
- 3. Enter into the Description column the information you want displayed in the status bar when the insertion point is in that field in the table. Enter Field Size (number of characters), Format and Input Mask (display and allowable characters), Caption (for the Datasheet view), Default Values, Validation rules, and other properties in the General section.
- 4. Click the next blank line of the field grid and create the next field in your database; then repeat Step 5. To insert a field between two other fields, click the Insert Rows button on the Table Design toolbar.
- 5. To select the field you want to use to create a primary key, click the field selector to the right of the field name. Or, select multiple fields for a compound primary key by holding Ctrl and clicking each field selector; then click the Primary Key button on the toolbar.
- 6. Click Save on the Table Design toolbar; enter a name for the table in the Table Name text box of the Save Table dialog box and choose OK.
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.
Table: Modify Design
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 guide.)
Steps
- 1. Click a table name in the Tables tab of the Database window, then choose the Design button.
2.Make your changes or additions in the Table Design View.
3.Choose Insert, Rows to add fields, or Edit, Delete Rows to remove fields.- 4. Choose View, Indexes to create or modify table indexes, or View, Properties to add or alter table properties.
- 5. Enter any General field properties like Captions, Default Values, Format, Input Masks, Validation Rule, Validation Text, Allow Zero Length, Required (mandatory data entry), and so on, that you want.
- 6. After you have finished modifying your table, select File, Save. Or, to save the resulting table as a different file, select File, Save As; name and locate your table in the file system using the Save As dialog box; then choose OK.
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.
Table: Properties
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 guide.)
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.")
Steps
- 1. Right-click the table in the Table tab of the Database window and select the Properties command from the shortcut menu.
- 2. Enter the description and whether the table is hidden. Or, right-click in the Table Design View (to the left of the leftmost column header of the field grid), and select the Properties command in the shortcut menu.
- There you can set validation rules and validation text, description, the field to sort the table by, and how you want to filter the table. An example of a filter would include a name of a field, a comparison sign (equals, greater than, less than), and a value. For example, Title = "Sales Manager".
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.
Validate Data: Field Validation
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 guide.)
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 guide and Expression sections in the Calculations part of this guide.
Steps
- 1. Click the table name in the Tables tab of the Database window, and then choose the Design button.
- 2. Move to the field and click in the Validation Rule box in the bottom of the Table Design window and enter an expression.
- 3. If you want an error message to appear if this rule is violated, type the message in the Validation Text box.
- 4. Click the Save button on the toolbar to save your new database rule.
When you enter a value in that field, the value is allowed if the expression is evaluated as "True."
Validate Data: Record Validation
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.
Steps
- 1. Click the table name in the Tables tab of the Database window, then choose the Design button.
- 2. Click the Properties button on the toolbar and click in the Validation Rule box and enter an expression.
- 3. If you want an error message to appear if this rule is violated, type the message in the Validation Text box; click the Close (X) button to close the Property sheet.
- 4. Click the Save button on the toolbar to save your new database rule.
Validate Data: Required Field
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.
Steps
- 1. Click a table name in the Tables tab of the Database window, and then choose the Design button.
- 2. Go to the field you want to require.
- 3. In the field property area of the Table Design window, set the Required property to Yes.
4.Click the Close (X) button on the Table Design window and choose Yes to save the changes to the table design.
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.
| |
Popular Tutorials
-
MS Access
1109
-
C++
1222
-
HTML
584
-
JavaScript
616
-
Vbscript
873
-
Oracle
473
-
VC++
875
-
SQL
2959
-
XML
514
-
Java
814
-
Perl
455
-
Linux
451
