Microsoft Access (part 2)

Tables

INTRODUCTION TO TABLE

A table is a collection of Data, such as Result or Diary Memo. Using a separate table for each subject means that you store that data only once, which makes your database well-organized, .

Access provides different ways to creating a table .Double-click on the icons to create a table.

CREATE TABLE

(A) create table in Design view will allow you to create the fields of the table. this is the most common way of creating a table and is explained in detail below.

(B) Create table using wizard will step you through the creation of a table.

(C) Create table by entering data will give you a blank datasheet with unlabelled columns that looks much like an Excel worksheet. Enter data into the cells and click the Save button. You will be prompted to add a primary field. After the table is saved, the empty cells of the datasheet are trimmed. The fields are given generic names such as "Field1", "Field2", etc. fields, select Format|Rename olumn from the menu bar or highlight the column, right-click on it with the mouse, and select Rename Column from the shortcut menu

Table in Design View

Design View will allow you to define the fields in the table before adding any data to the datasheet. The window is divided into two parts: a top pane for entering the field name, data type, and an option description of the field, and a bottom pane for specifying field properties

Steps:

1. Select 'Tables' tab From the Database view, click 'New' and then choose 'Design View' from the 'New Table' dialog box, and click OK.

2. Enter a field name in the first row of the Field Name Column.

3. Press Enter or Tab to move to the Data Type Field.

4. Enter a data type for this field. Access will scroll using the first letter of the data type, or you can also use the drop down list provided to you.

5. Alter the Field Properties section of the table design grid as needed.

6. Add a comment in the description column for your better understanding

Using Table Wizard

Like all Wizards, the Table Wizard simplifies the Layout of the fields. You are guided visually through a series of steps, which help you to decide on the type to be created, and then the table is created automatically. The steps in creating a table through 'Table Wizard' are as follows:

1. Open the 'New Table' Dialog box by using one of the followings:

Select the 'Tables' option from the Insert menu. Select the 'Tables' tab and then click on 'New' button in the Database window. Select the 'New' Object toolbar button and choose the Table option.

2. Select table type from the 'New Table' Dialog box.

3. Select table and field from the list of sample tables and sample fields.

4. Give title of the table and create relationship if exist.

Datasheet View to Make a Table

1. Launch MS Access and start a new blank database. Access now launches a blank table for use.

2. Enter data for the first field in the first row.

3. Right click on the first column header (labeled Field 1) and choose Rename from the shortcut menu. This allows you to change the column header, change it to Employee ID for this field.

4. Enter come name data for each of the next three fields, editing the field names to Last Name, First Name, and Middle Name respectively.

5. Move to the next column, Enter yes.

6. Move to the next column to enter date.

7. Click on the Save icon in the toolbar.

8. Click the view button to switch to Design View.

WORKING ON TABLES

NAMING FIELD

1. This will bring up the Table Design View

2. There are three columns here that should be explained in detail

o Field Name: This is where you type the name for your column. A common practice is to make it one word and to use capitalization for multiple words squished into one (e.g. SaleNumber)

o Data Type: This column is where you specify the type of data that will be stored. If you are storing money then select Currency. The most common types of data are: Text, Number, Currency and Date/Time.

o Description: Here you can type optional notes to remind yourself or provide useful information for others who might be viewing this file later.

3. The first column in our tbl_Sales example was Employee, so let's enter in Employee in the Field Name column and choose Text from the Data Type column. If click inside the Data Type column you will see that it is actually a drop down select box with many options to choose from. Select the Text option.

4. Enter the following information for our remaining three columns of tbl_Sales:

o Field Name: Product, Data Type: Text

o Field Name: Price, Data Type: Currency

o Field Name: SaleNumber, Data Type: Number

5. Before we are finished here, we need to make a Primary Key. A primary key is restriction that we place on a column stating that there can be no duplicate values in that column. We will be talking about keys later, but for now right-click in the SaleNumber row and choose Primary Key from the pop-up menu.

6. We have finished our table's outline so click the X in the top right to close the design view (don't close Access, just the Design Window).

7. Click yes and enter "tbl_Sales" for your table's name

Although this process of creating an Access table might seem overly complicated, with time you'll be able to create and edit existing tables very quickly. Feel free to revisit this page if you are having trouble creating an Access table.

DESCRIPTION OF DESIGN VIEW

• Field Name - This is the name of the field and should represent the contents of the field such as "Name", "Address", "Final Grade", etc. The name can not exceed 64 characters in length and may include spaces.

• Data Type is the type of value that will be entered into the fields.

• Text - The default type, text type allows any combination of letters and numbers up to a maximum of 255 characters per field record.

• Memo - A text type that stores up to 64,000 characters.

• Number - Any number can be stored.

• Date/Time - A date, time, or combination of both.

• Currency - Monetary values that can be set up to automatically include a dollar sign ($) and correct decimal and comma positions.

• AutoNumber - When a new record is created, Access will automatically assign a unique integer to the record in this field. From the General options, select Increment if the numbers should be assigned in order or random if any random number should be chosen. Since every record in a datasheet must include at least one field that distinguishes it from all others, this is a useful data type to use if the existing data will not produce such values.

• Yes/No - Use this option for True/False, Yes/No, On/Off, or other values that must be only one of two.

• OLE Object - An OLE (Object Linking and Embedding) object is a sound, picture, or other object such as a Word document or Excel spreadsheet that is created in another program. Use this data type to embed an OLE object or link to the object in the database.

Hyperlink - A hyperlink will link to an Internet or Intranet site, or another location in the database. The data consists of up to four parts each separated by the pound sign (#): DisplayText#Address#SubAddress#ScreenTip. The Address is the only required part of the string

Properties of Fields

• Field Size is used to set the number of characters needed in a text or number field. The default field size for the text type is 50 characters. If the records in the field will only have two or three characters, you can change the size of the field to save disk space or prevent entry errors by limiting the number of characters allowed. Likewise, if the field will require more than 50 characters, enter a number up to 255. The field size is set in exact characters for Text type, but options are give for numbers:

o Byte - Positive integers between 1 and 255

o Integer - Positive and negative integers between -32,768 and 32,768

o Long Integer (default) - Larger positive and negative integers between -2 billion and 2 billion.

o Single - Single-precision floating-point number

o Double - Double-precision floating-point number

o Decimal - Allows for Precision and Scale property control

Primary Key

A primary key is a field or grouping of fields that uniquely identify a record in a table, so that an individual record can be placed without confusion.

• NOTE:

A primary key is the field(s) (a primary key can be made up of more than one field) that uniquely identifies each record, i.e. the primary key is unique for each record and the value is never duplicated in the same table, so in the above table the EmployeeID field would be used. A constraint is a rule that defines what data is valid for a given field. So a primary key constraint is a rule that says that the primary key fields cannot be null and cannot contain duplicate data.

Switching Views

• To Simply click on these two option either click on Datasheet iew or Design view

Datasheet View(option) Design View(option) Displays the view, which allows you to enter raw data into your database table. Displays the view, which allows you to enter fields, data-types, and descriptions into your database table.

Entering Data

Click on shell where you want to enter your data,each field have own attributes and column ,enter data according coulmn and row

Manipulating Data

Adding Records

Add new records to the table in datasheet view by typing in the record beside the asterisk (*) that marks the new record. You can also click the new record button at the bottom of the datasheet to skip to the last empty record.

Editing Records

To edit records, simply place the cursor in the record that is to be edited and make the necessary changes. Use the arrow keys to move through the record grid. The previous, next, first, and last record buttons at the bottom of the datasheet are helpful in maneuvering through the datasheet.

Deleting Records

Delete a record on a datasheet by placing the cursor in any field of the record row and select Edit|Delete Record from the menu bar or click the Delete Record button on the datasheet toolbar.

Adding and Deleting Columns

Although it is best to add new fields (displayed as columns in the datasheet) in design view because more options are available, they can also be quickly added in datasheet view. Highlight the column that the new column should appear to the left of by clicking its label at the top of the datasheet and select Insert|Column from the menu bar.

Entire columns can be deleted by placing the cursor in the column and selecting Edit|Delete Column from the menu bar.

Resizing Rows and Columns

The height of rows on a datasheet can be changed by dragging the gray sizing line between row labels up and down with the mouse. By changing the height on one row, the height of all rows in the datasheet will be changed to the new value.

Column width can be changed in a similar way by dragging the sizing line between columns. Double click on the line to have the column automatically fit to the longest value of the column. Unlike rows, columns on a datasheet can be different widths. More exact values can be assigned by selecting Format|Row Height or Format|Column Width from the menu bar.

Freezing Columns

Similar to freezing panes in Excel, columns on an Access table can be frozen. This is helpful if the datasheet has many columns and relevant data would otherwise not appear on the screen at the same time. Freeze a column by placing the cursor in any record in the column and select Format|Freeze Columns from the menu bar. Select the same option to unfreeze a single column or select Format|Unfreeze All Columns.

Hiding Columns

Columns can also be hidden from view on the datasheet although they will not be deleted from the database. To hide a column, place the cursor in any record in the column or highlight multiple adjacent columns by clicking and dragging the mouse along the column headers, and select Format|Hide Columns from the menu bar.

To show columns that have been hidden, select Format|UnhideColumns from the menu bar. A window displaying all of the fields in the table will be listed with check boxes beside each field name. Check the boxes beside all fields that should be visible on the data table and click the Close button.

Finding Data in a Table

Data in a datasheet can be quickly located by using the Find command.

1. Open the table in datasheet view.

2. Place the cursor in any record in the field that you want to search and select Edit|Find... from the menu bar.

3. Enter the value criteria in the Find What: box.

4. From the Look In: drop-down menu, define the area of the search by selecting the entire table or just the field in the table you placed your cursor in during step 2.

5. Select the matching criteria from Match: to and click the More >> button for additional search parameters.

6. When all of the search criteria is set, click the Find Next button. If more than one record meets the criteria, keep clicking Find Next until you reach the correct record.

Replace

The replace function allows you to quickly replace a single occurrence of data with a new value or to replace all occurrences in the entire table.

1. Select Edit|Replace... from the menu bar (or click the Replace tab if the Find window is already open).

2. Follow the steps described in the Find procedure for searching for the data that should be replaced and type the new value of the data in the Replace With: box.

Click the Find Next button to step through occurrences of the data in the table and click the Replace button to make single replacements. Click Replace All to change all occurrences

Relationships

To Set multiple tables in your database, you need to Access how that information back each other again.This process is to define relationships between your tables. After that, you can create queries, forms, and reports to display information from many tables at once.

Handling Queries

Introduction to Query

Queries select records from one or more tables in a database so they can be viewed, analyzed, and sorted on a common datasheet. The resulting collection of records, called a dynaset (short for dynamic subset), is saved as a database object and can therefore be easily used in the future. The query will be updated whenever the original tables are updated. Types of queries are select queries that extract data from tables based on specified values, find duplicate queries that display records with duplicate values for one or more of the specified fields, and find unmatched queries display records from one table that do not have corresponding values in a second table.

Creating A QUERY IN DESIGN VIEW

Use these steps to create a new query in Design View:

1. From the Queries page on the Database Window, click the New button

2. Select Design View and click OK.

3. Select tables and existing queries from the Tables and Queries tabs and click the Add button to add each one to the new query.

4, Click Close when all of the tables and queries have been selected.

5. Add fields from the tables to the new query by double-clicking the field name in the table boxes or selecting the field from the Field: and Table: drop-down menus on the query form. Specify sort orders if necessary.

Enter the criteria for the query in the Criteria: field. The following table provides examples for some of the wildcard symbols and arithmetic operators that may be used. The Expression Builder can also be used to assist in writing the expressions

Query and Expression Operators

Operator Explanation ? The question mark is a wildcard that takes the place of a single letter. * The asterisk is the wildcard that represents a number of characters. <100> Value less than 100 >=1 Value greater than or equal to 1 <>"FL" Not equal to (all states besides Florida) Between 1 and 10 Numbers between 1 and 10 Is Null Is Not Null Finds records with no value or all records that have a value "a*" All words beginning with "a" >0 And <=10 All numbers greater than 0 and less than 10 "Bob" Or "Jane" Values are Bob or Jane

6. After you have selected all of the fields and tables, click the Run button on the toolbar.

7. Save the query by clicking the Save button.

Query wizards

Click the Create query by using wizard icon in the database window to have access step you through the process of creating

QUERY

1. From the first window, select fields that will be included in the query by first selecting the table from the drop-down Tables/Queries menu. Select the fields by clicking the > button to move the field from the Available Fields list to Selected Fields. Click the double arrow button >> to move all of the fields to Selected Fields. Select another table or query to choose from more fields and repeat the process of moving them to the Selected Fields box. Click Next > when all of the fields have been selected.

2. On the next window, enter the name for the query and click Finish.

Finding Duplicate Query

1. Click the New button on the Queries database window, select Find Duplicates Query Wizard from the New Query window and click OK.

2. Select the table or query that the find duplicates query will be applied to from the list provided and click Next >.

3. Select the fields that may contain duplicate values by highlighting the names in the Available fields list and clicking the > button to individually move the fields to the Duplicate-value fields list or >> to move all of the fields. Click Next > when all fields have been selected.

4. Select the fields that should appear in the new query along with the fields selected on the previous screen and click Next >.

Name the new query and click Finish.