How do I create a lookup column by typing a list?

How do I create a lookup column by typing a list?

1.   Activate the Datasheet tab. (These instructions assume you are in the Datasheet view.)

2.   Click the Lookup Column button in the Fields & Columns group. The Lookup Wizard appears.

3.   Click the radio button next to “I will type the values I want.”

4.   Click Next. The Lookup Wizard moves to the next page.

5.   Type the number of Columns you want in the Number Of Columns field.

6.   Type the values you want under the column heading.

7.   Click Next. The Lookup Wizard moves to the next page.

8.   Type the column label you want.

9.   Click Finish. Access creates a lookup column based on your list.

Read More

Adjust column widths

Adjust column widths

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

1.   Deselect Hide Key Column, if you wish.

2.   Adjust the column widths by dragging or double-clicking the right vertical border for the column.

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

1.   Click the key field.

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

 1.   Type the name you want to give the column.
2.   Click if you want to allow multiple values in the field.
3.   Click Finish. Access creates the lookup column.

Read More

Select your table or query

Select your table or query

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.

 1.   Click a radio button to select what you want to base your lookup column on. Choose from

Tables, Queries, or Both.

2.   Click to select the table or query you want.

3.   Click Next. The Lookup Wizard moves to the next page.

Select fields

You choose the fields you want to appear in your lookup column. Be sure to include the primary key.

1.   Click the field you want.

2.   Click the single right-arrowbutton . 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: Usethe single left-arrow and the double left-arrows to deselect fields.

          3.   Click Next. The Lookup Wizard moves to the next page.

            You choose the fields you want to appear in your lookup column. Be sure to include the                   primary key.

Sort fields

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.

1.   Click the down-arrow and then select the field you want to sort by.

2.   Click to select a sort direction (the button toggles between ascending and descending). You can sort within a sort for up to four levels.

3.   Click Next. The Lookup Wizard moves to the next page.

Read More

Create a Lookup Column

Create a Lookup Column

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.

Departments
Department ID	Department
Primary Key
1	Administration
2	Computer Science
3	English
4	History
5	Math

Access has a wizard to help you create lookup columns. Creating a Lookup column creates a relationship between two tables. See the section Create Relationships in Lesson 3 to learn more about relationships.

To use the Lookup Wizard to create a lookup column:

1.   Open the table to which you want to add a lookup column.

2.   Click the field label for the field before which you want to add a lookup column.

3.   Activate the Datasheet tab. (You must be in Datasheet view.)

4.   Click the Lookup Column button in the Fields & Columns group. The Lookup Wizard appears.

5.   Make sure the radio button next to “I want the lookup column to look up the values in a table or query.” is selected.

6.   Click Next. The Lookup Wizard moves to the next page.

Open the Lookup Wizard

Read More

To assign a data type or format to a field

To assign a data type or format to a field:

To use Design view to create a new table:

1.   Activate the Create tab.

2.   Click Table Design in the Tables group. Access changes to Design view and the Table Tools become available.

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.

Date/Time

Use for dates and times.

Currency

Use for currency.

Prevents rounding during calculation.

AutoNumber

Unique sequential numbers or random numbers automatically inserted when you create a record. Use to create a primary key.

Yes/No

Logical data. Use when only one of two values is valid. Yes/No, True/False, etc.

Hyperlink	Use to store hyperlinks.
Attachment	Use to store attachments.
OLE Object	Use to attach an OLE object such as a Word document, Excel spreadsheet, or PowerPoint presentation.

Read More

Understanding Data Types

Understanding Data Types

In Access, you use data types to specify the type of data each field can capture.

Data Types
Data Type	Use	Notes
Text	Alphanumeric data. Use for text and for numbers that are not used in mathematical calculations. Use for names, addresses, and other relatively short pieces of text. Can store up to 255 characters.	.
Memo	Long text. Use for long pieces of text, such as notes and long descriptions. Can store up to 64,000 characters.
Number	Numeric data. Use for numbers you want to use in mathematical calculations.	If you are working with currency, use the currency type.
Date/Time	Use for dates and times.
Currency	Use for currency.	Prevents rounding during calculation.
AutoNumber	Unique sequential numbers or random numbers automatically inserted when you create a record. Use to create a primary key.
Yes/No	Logical data. Use when only one of two values is valid. Yes/No, True/False, etc.

Hyperlink	Use to store hyperlinks.
Attachment	Use to store attachments.
OLE Object	Use to attach an OLE object such as a Word document, Excel spreadsheet, or PowerPoint presentation.

Read More