Week 4 (Compatibility Mode)

Creating Microsoft Access Tables
Prepared by : Cik Siti Khairunnisa Baharudin
Understanding Tables
A table is a set of columns and rows. Each column is called a field. Within a table, each field must be given a name and no two fields can have the same name. Each value in a field represents a single category of data. For example, a table might have three fields: Last Name, First Name, and Phone Number. The table consists of three columns: one for last name, one for first name, and one for phone number. In every row of the table, the Last Name field contains the last name, the First Name field contains the first name, and the Phone Number field contains the phone number. Each row in a table is called a record.
To add fields to a table: - Click the Add New Field column label. - Activate the Datasheet tab. - Click Rename in the Fields & Columns group. - Type the field name. - Press Enter. Access creates the field. - Type the next field name. Access creates the field. Continue until you have created all of the fields in your table. - Press Enter without entering a field name to end your entries. Or - Right-click the Add New Field column label. A menu appears. - Click Rename Column. - Type the field name. - Press Enter. Access creates the field. - Type the next field name. Access creates the field. Continue until you have created all of the fields in your table
To explicitly assign a data type or format to a field:

Click the field label for the field to which you want to assign a data type. Activate the Datasheet tab. Click the down-arrow next to the Data Type field and then choose a data type. Click the down-arrow next to the Format field and then choose a format. Access assigns a data type and format to the field you selected.
To use Design view to create a new table:

1. 2. Activate the Create tab. Click Table Design in the Tables group. Access changes to Design view and the Table Tools become available.
Type the first field name in the Field Name field. Press the Tab key. Click the down-arrow that appears when you click in the Data Type field and then select a data type. 6. Click Primary Key if the column you created is a primary key. A small key appears next to the field name. 7. Press the Tab key. 8. Type a description. The description is optional. 9. Press the Tab key. Access moves to the Field Name field. 10. Repeat steps 3 through 10 until you have created all of your fields.
3. 4. 5.
To set field properties:

1. Click the field for which you want to set the field properties. 2. Activate the General tab in the Field Properties area. 3. Set the properties you want to set. 4. Repeat steps 1 through 3 until you have set all the properties for all fields.
Create a Lookup Column

1. If a field can contain a finite list of values, you can create a Lookup Column and users can select the value they want from a list. For example, if the employees at a school can only work in one of the following departments: Administration, Computer Science, English, History, or Math. You can create a table Departments table that lists the departments and then use the list in the Employee table to assign each employee to a department. 2. Access has a wizard to help you create lookup columns. Creating a Lookup column creates a relationship between two tables
To use the Lookup Wizard to create a lookup column: Open the Lookup Wizard
Open the table to which you want to add a lookup column. Click the field label for the field before which you want to add a lookup column. Activate the Datasheet tab. (You must be in Datasheet view.) Click the Lookup Column button in the Fields & Columns group. The Lookup Wizard appears. Make sure the radio button next to I want the lookup column to look up the values in a table or query. is selected. Click Next. The Lookup Wizard moves to the next page.
Select your table or query

1. A lookup column can be based on a table, a query, or a list of values you type. If you base your lookup column on a table or query, you must create the table or query before creating the lookup column. A query is a list of rows and columns based on one or more tables. A query only displays the rows and columns you specify.
Click a radio button to select what you want to base your lookup column on. Choose from Tables, Queries, or Both. Click to select the table or query you want. Click Next. The Lookup Wizard moves to the next page.
Select fields
1. You choose the fields you want to appear in your lookup column. Be sure to include the primary key.
Click the field you want. Click the single right-arrow button . Access places the field in the Selected Fields column. Repeat this process to select additional fields. If you want all the fields in the table, click the double right-arrow button . Note: Use the single left-arrow and the double left-arrows to deselect fields. Click Next. The Lookup Wizard moves to the next page.
Sort fields
1. The Lookup Wizard allows you to sort the records in a lookup column. You can display records in order, either ascending (alphabetical from A to Z, lowest number to highest number, earliest date to latest date) or descending (alphabetical from Z to A, highest number to lowest number, latest date to earliest date). You can also sort within a sort. For example, you can sort by state and then within each state by city, and then within each city by street address. If you are creating a sort within a sort, create the highest level sort on line one, the next level sort on line two, and so on. In the state, city, and street address example, you create the state on line one, the city on line two, and the street address on line three.
Click the down-arrow and then select the field you want to sort by. Click to select a sort direction (the button toggles between ascending and descending). You can sort within a sort for up to four levels. Click Next. The Lookup Wizard moves to the next page.
Adjust column widths

1. A key column is the column that connects one table or query to another table or query. For example, you can use the Department ID field in the Employees table and the Department ID field in the Departments table to connect the two tables. You may, however, want to display the name of the department when you view the table but not the department ID; if so, leave the Hide Key Column box checked.
Deselect Hide Key Column, if you wish. Adjust the column widths by dragging or double-clicking the right vertical border for the column. Click Next. The Lookup Wizard moves to the next page.
Specify the Key Field (if you deselected Hide Key Column)
A key field is a field that uniquely identifies a record. If you deselected Hide Key column, you must tell Access which field is the key field.
Click the key field. Click Next. The Lookup Wizard moves to the next page.
Name the column

Field names appear at the top of each column. On this page of the Wizard you tell Access what you want to name your lookup column. In Access 2007, multiple values can appear in a field; click the Allow Multiple Values checkbox if you want to allow multiple values.
Type the name you want to give the column. Click if you want to allow multiple values in the field. Click Finish. Access creates the lookup column.
Working with Microsoft Access Tables

After you create an Access table, you can modify it, enter data into it manually or import data from somewhere else, such as Excel. This lesson teaches you how to modify a table and enter data. Enter Records After you have created a table, you can enter data into it. To enter data into an AutoNumber field: Press the Tab key. When you make an entry into another field in the record, Access will automatically make an entry
Click the down-arrow that appears when you click in the field. Click to select the entry you want. Press the Tab key.
To enter data into a Yes/No field:

Click the checkbox for Yes; leave the checkbox unchecked for No.
To add an attachment to an attachment field: Double-click in the attachment field. The Attachments dialog box appears. Click Add. The Choose File dialog box appears.
Click the file you want to add. Click Open. The Choose File dialog box closes.
Click OK. Access attaches the file. Note: You can attach multiple files to a single attachment field.
To add data to an OLE Object field:

An OLE object is an object such as a Word document or an Excel Spreadsheet.
Right-click in the field. A menu appears. Click Insert Object. The Microsoft Office Access dialog box appears.
Create New:
To add data to an OLE Object field:

Click the Create New radio button if you want to create a new object. Click the object type you want to create. Click OK. Access opens the program for the object type you selected. You can create the object. Create the object and then close the program for the object type you selected. Access links to the object. Create From File:
Click the Create From File radio button if you want to use an existing file.Type the path to the file or click the Browse button and locate the file. Click OK. Access links to the object.

Week 4 (Compatibility Mode)

Uploaded by

Copyright:

Available Formats

Week 4 (Compatibility Mode)

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Week 4 (Compatibility Mode)

Uploaded by

Copyright:

Available Formats

Creating Microsoft Access Tables

Prepared by : Cik Siti Khairunnisa Baharudin

To explicitly assign a data type or format to a field:

To use Design view to create a new table:

To set field properties:

Create a Lookup Column

Select your table or query

Adjust column widths

Name the column

Working with Microsoft Access Tables

To enter data into a Yes/No field:

To add data to an OLE Object field:

To add data to an OLE Object field:

You might also like