Microsoft Access Day Five

Field And Column Group :

New Field :  Insert a new field (column) into the table, so that you can store a specific type of information.

Add Existing Fields: Insert a field into this view, based on an existing field.

Insert Column : The Insert option inserts a column before the selected column.

Delete Column : The Delete option permanently deletes columns and all the data contained in them. You cannot undo a column delete.

Steps :

  • Click and drag to select the columns you want to delete.
  • Activate the Datasheet tab.
  • Click Delete in the Fields & Columns group. A prompt appears.
  • Click Yes. Access deletes the columns you selected.
Rename Column: Option is used  Rename the Selected Column.

What is a lookup column?

A lookup column (or field) is a field in a table whose value is retrieved from another table or from a value list. You can use a lookup column to display a list of choices in a combo box or list box. The choices can come from a table or query, or they can be values that you supply. You can create a lookup column manually by setting a field's Lookup field properties, or automatically by completing the Lookup Wizard. Whenever possible, you should use the Lookup Wizard to create a lookup column. The Lookup Wizard simplifies the process and automatically populates the appropriate field properties and creates the appropriate table relationships.

Create a lookup column in Datasheet View:

By default, when you open a table, it opens in Datasheet view. You can add a lookup column by clicking Lookup Column in the Fields & Columns group on the Datasheet tab. doing so starts the Lookup Wizard, which leads you through the process of creating the lookup column.

  • In the Navigation Pane, double-click the table in which you want to create the lookup column — this opens the table in Datasheet view.
  • On the Datasheet tab, in the Fields & Columns group.

  • Click Lookup Column

  • Choose the Option “ I Will type in the value that I want “
  • Click on Next Button
  • Type the Require the data {Microsoft Office ,C  language, C++,Oracle,Java,Html, Computer Hardware}

  • Click on Next Button
  • Type the Field Name  “Courses”

  • Click on Finish Button

Lookup option in Between Tables:

  • Create the Department Table based on below format

  • Click the Create Tab
  • Choose the Table Group Area
  • Click on Table option
  • Click on the Lookup Column option
  • Choose the Option  “ I Want the lookup column to look up the values in a table or query.

  • Click on Next Button

  • Select the Required Table and View Type “ Tables”.
  • Click on Next Button

  • Choose the Required Field Name  “Department Name”
  • Click on Next Button

  • Choose Sort Order
  • Click on Next Button
  • Adjust the Column Width

  • Click on Next Button
  • Type the Column Name “Departments”
  • Click on Finish Button

Lookup field Properties :

Set this property To
Display ControlSet the control type to Check Box, Text Box, List Box or Combo Box. Combo Box is the most common choice for a lookup column.
Row Source TypeChoose whether to fill the lookup column with values from another table or query, or from a list of values that you specify. You can also choose to fill the list with the names of the fields in a table or query.
Row SourceSpecify the table, query, or list of values that provides the values for the lookup column. When the Row Source Type property is set to Table/Query or Field List, this property should be set to the name of a table or query or to a SQL statement that represents the query. When the Row Source Type property is set to Value List, this property should contain a list of values separated by semicolons.
Bound ColumnSpecify the column in the row source that supplies the value stored by the lookup column. This value can range from 1 to the number of columns in the row source.
NOTE: The column that supplies the value to store does not have to be the same column as the display column.
Column CountSpecify the number of columns in the row source that can be displayed in the lookup column. To select which columns to display, you provide a column width in the Column Widths property.
Column HeadsSpecify whether to display column headings.
Column WidthsEnter the column width for each column. If you don't want to display a column, such as an ID number, specify 0 for the width.
List RowsSpecify the number of rows that appear when you display the lookup column.
List WidthSpecify the width of the control that appears when you display the lookup column.
Limit To ListChoose whether a user can enter a value that isn't found in the list.
Allow Multiple ValuesSpecify whether the lookup column employs a multi valued field and allows multiple values to be selected.
Allow Value List EditsSpecify whether you can edit the items in a lookup column that is based on a value list. When this property is set to Yes, and you right-click a lookup field that is based on a single column value list, you will see the Edit List Items menu option. If the lookup field has more than one column, this property is ignored.
List Items Edit FormSpecify an existing form to use with which you can edit the list items in a lookup column that is based on a table or query.
Show Only Row Source ValuesShow only values that match the current row source when Allow Multiples Values is set to Yes.