In the Database window, click the Tables tab (or choose Database Objects from the View menu and select Tables). Choose the New button and select Table Wizard (or double-click on the Create table by using wizard shortcut in the Database window).

Follow the directions in the Wizard dialog boxes for adding fields to your table.

The Wizards work fine for many tables, but you will find that creating your own tables offers much more flexibility for your database. In the Database window, click the Tables tab (or choose Database Objects from the View menu and select Tables). Choose the New button and select Design View (or double-click on the Create table in Design view shortcut in the Database window). The table's design window will appear. See "Parts of the Design Window" later in this document for more information about the design window.

Access offers you two different ways to retrieve data from tables created in other programs: importing the table into Access and creating a link to the table. If you wish to change the structure of the data you are connecting to, you should choose Import Table. But while this option will allow you to add, remove, or resize fields in your table, once you have copied the table into your database, you will not see any subsequent changes to the original data source. If you would like to "share" data between the original database and your new database, you should choose Link Table. This option will allow you to see and propagate your own changes between the databases.

In the Database window, click the Tables tab (or choose Database Objects from the View menu and select Tables). Choose the New button and select Import Table or Link Table. Follow the directions in the Wizard dialog boxes for adding fields to your table.

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 in the Field Name column and enter a new name.

When you name fields, think "beyond the box". That is, you should think about other databases when you name fields. Some databases don't like spaces in their field names. If you think you might use your database outside Access (including Internet connections with ODBC), you'll save yourself some headaches by naming your fields without spaces from the beginning. Solution? Either capitalize the first letter of each word in your field name or replace the spaces with underscores (_).

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 status bar, 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 users an example to work from ("The year of release (e.g., 1999).").

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:

The following table summarizes each data type:

Note: The Lookup Wizard is not a data type; it is a tool that allows you to create a list of values that users can select when entering data. Access determines the data type based on the values listed. Not surprisingly, this tool can expedite data entry and insure that certain values are entered while insuring that some values are not. See the [ http://help.unc.edu/?id=2494 ] Access Forms document for an in-depth discussion of this tool. For more information about setting field properties (found on the General tab in the Design View), refer to the [ http://help.unc.edu/?id=1898 ] Access Tables document.

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.

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>.

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 adding or editing a record, you will lose the record or any changes made.

Select the record that you wish to edit by clicking in the specific field you want to change. Type in the new data for that field. 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.

Access offers a way to view related data from other tables directly from the current table's Datasheet View. After you have created the relationships between your tables (as discussed later in this document), you can click on the plus sign (+) on the far left of each record in your table to see related records in other tables. Click on the minus sign (-) to collapse that view.

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.

Referential integrity helps to ensure that relationships between records are valid and that you do not accidentally delete or change related data. Access can enforce referential integrity when the matching field from the primary table is a primary key or has a unique index, the fields have the same data type, and both tables belong to the same Access database. When referential integrity is enforced, there must already be a record in the primary table to add a value to the matching field in the related table. Also, you cannot delete a record from a primary table if matching records exist in the related table. Access will adhere to these rules for a relationship if you select the Enforce Referential Integrity option (which you should do) when you create the relationship in the Relationships dialog box. When you enforce referential integrity, you can choose how to handle Cascade Delete and Cascade Update operations. (In other words, if you delete or change records in the primary table, choose to delete or update records in the related table by selecting the corresponding checkbox.)

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:

In the Database window, click the Query button.

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. You should take note that the QBE grid is read from left to right and that this may change the results of your query, specifically in the sorting options. 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.

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.

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:

To insert a field:

To delete 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.

Access gives you a great deal of flexibility in how you can enter expressions. To specify criteria as either a simple word, like Faulkner, or as a complex definition, "Between 100 And 500," you can type the expression in the Criteria cell in the field's row on the QBE grid. For example, you want find to find only works written by authors whose last names are Faulkner, then you could type any of these in the Criteria cell: Faulkner, =Faulkner, "Faulkner", or ="Faulkner". After you have entered your criteria and moved to another cell, Access checks behind you and display the criteria in a standard format, "Faulkner" in this case. For the complete list of rules on expressions, consult Access' on-line Help.

Access also has a helpful tool for creating complex expressions, the Expression Builder. Clicking the Build button on the toolbar once the cursor is in a QBE grid Field or Criteria cell opens this tool. Refer to the [ http://help.unc.edu/?id=2504 ] Access Queries 1 document for more information on the Expression Builder.

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.

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 on the same row, 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.

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.

Another useful string operator that allows you to select data in a specified field that do or do not contain values is the Null operator. While running a mail order business, you might want to see which customers who receive catalogs have not placed an order. To select this value, find the "Amount of Purchase" field that is blank and enter Is Null as its criteria. To select only those customers who have made purchases, you would use the Is Not Null statement.

You can identify a range of records in an expression by using the Between . . . And operator or the relational operators (<,>,<>,<=, and >=). The following examples show various operators.