Table And Query

Overview:

In a relational database the data is stored in tables. The table is the fundamental concept of relational databases. It is also called relation. It is the foundation of every Relational Database Management System is a object called table.

          Tables are grids that store information in a database similar to the way an Excel worksheet stores information in a workbook. Access provides three ways to create a table for which there are icons in the Database Window. Double-click on any of the icons to create a able.

          Every database consists of one or more tables which store data. Each table has its own unique name and consists of columns and rows. It is a very convenient way to store information.

          The columns in a table (also called table fields) have their own unique  names and have a pre-defined data type. The field can be a primary key, an index defined on i and i can have certain default value.
          The able columns describe he data types, whereas he table rows contain the actual data. Here is an example of a simple database table, containing students' data. he firs row, listed in bold, contains the names of the table columns:

Characteristics of Table:

The tables of a relational database have following characteristics:

• Each cell of the table contains only one value.
• Each column has a distinct name, which is the name of the attribute (field) it represents.\
• The order of he columns is immaterial.
• Each row represents a record.
• Each row is distinct; there are no duplicate rows.
• The order of rows is immaterial.

Using a separate table for each entity means that you store that data only once, which makes your database more efficient, and reduces data entry errors, Tables form the foundation of an Access database structure.

Degree of a Relation:

The number of fields in a relation is called the degree of a table. Once the the table has been created, its degree usually does not changes, e.g. a table with five fields has a degree of 5.

Cardinality of a Relation: 

the number of record in a relation is called the cardinality of the relation. The cardinality of a relation changes as new records are added or existing records are deleted, e.g. a table with 50 records has a cardinality of 50.

A Basic Terminology:

These words are used often in Access so you will want to become familiar with them before using the program.

• A database is a collection of related data (or record).
• An object is a component in the database such as a able, query, form, or macro.
• A table is a group of related data organized in fields (columns) and records (rows). By using a common field in two tables, the data can be linked. many tables can be stored in a single database.
• A field is a column in a table and defines a data type for a set of values in the table. For example a mailing list able might include fields for first name, last name, address, city, state, zip code, and telephone number.
• A record is row in a table and is a set of values defined by fields. In a mailing list table, each record would contain the data for one person as specified by the intersecting fields.
• Design View provides the tools for creating field in a table.
• Datasheet View allows you to update, edit, and delete information from a table.

Access IDE: 

IDE stand for Integrated Development Environment. It is an interface that is used to create a database. An IDE makes the using of Database simple, manageable for end users who may not have a complicate programming knowledge of the database system.

Microsoft Access is an example of a database management system. The access IDE simplifies the task of creating, designing good-looking screens with features (i.e. text boxes, list boxes, button, dialog boxes etc). It provides the facilities for searching, sorting, and retrieving the data.

Starting Microsoft Access:  

You can build a database in two ways by using the Database Wizard, or by opening an empty database and building all your objects with wizards or from scratch.

1. Double click on the Microsoft Access icon on the desktop if its icon in the desktop.
2. Click on start.

Table Design View:

Design view allows you to defines fields in the table before adding any data to datasheet.

Datasheet View:

When you open a table or query using he database window, i will be displayed in datasheet view. a table or query is opened in Datasheet view to perform different operations on the data in the table such as displaying data, adding new data, searching data etc.

The datasheet view is like worksheet. When table is opened in datasheet view, the fields name are displayed as header of columns and each row contains a complete record.

Switching Views:

• To switch views from the datasheet (spreadsheet view) to the design view, simply click the button in the top-left corner of the Access program.

Datasheet View                                        Design View

Display the view, which allows you to                                  Display the view, which allows you
enter raw data into your database table.                                 define fields, data-types, and
                                                                                         descriptions into your database table.

Table Creation:

You can open or create a table in several ways in Microsoft Access I.E.,

(i) Creating database in Design view.

(ii) creating database by wizard.

(iii) Creating database by entering data.

Creating database in Design View:

• Click on the Table object from the list of database object.
• in Database Window, Double-Click on "Create table in Design view"; a table window in Design View is appeared to design the structure of table.

                                             OR

• Click on    New, in Database Window. The New Table dialog box will open.
• Click on Design View Option.
• Click on OK. A blank table will open in Design View.
• Define each of the fields in your table.
• Under the Field Name column, enter the categories of your table.
• Under Data Type column, enter the type you want for you categories.

The attribute of a variable or field determines what kind of data it can hold. For example, in a Microsoft Access Database, the ext and Memo field data types allow the field to store either text or numbers, but the Number data type allow the field to store numbers only. Number data type fields store numerical data that is used in mathematical calculations. Use the Currency data type to display or calculate currency values. Other data types are Date/Time, Yes/No, Auto Number, and OLE object (Picture).

• Under the Description column, enter the text that describes the field. (This field is optional).

Defining Field Properties in Design View:

The properties of each field can be set in design View. The window is divided into two parts: a top pane for entering the field name, data type, and an optional description of the field, and a bottom pane for specifying field properties.
To assign Primary Key, select the field and click the Primary Key button in the toolbar. You can set the remaining properties in the Table Window's lower pane. The following properties are defined briefly.

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 cannot exceed 64 characters in length and may include spaces (However, this is not considered a good practice).

Data Type:

Before you start creating a new table in Access, you first consider how you want to break down the information you are organizing into smaller units of data in the table. Dividing the data into units of information is the process of determining the fields. Each field will be assigned a unique field name. Each field is also assigned a unique field name. Each field is also assigned a data type . Following are the data types available in Ms. Access:

• 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 can store more than 64000 characters and is used for detailed descriptive fields 0.
• Number - This data type is used to store numbers that are used in mathematical calculations. Several number field sizes are available. The most useful are summarized in table.
• Date'Time - A date, Time, or combination of both can be specified in this field.
• Currency - Monetary values that can be set up to automatically include a dollar sign ($) and correct decimal and comma positions.
• Auto Number - 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 distinguish 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.
• Hyper Link - A hyperlink will link to a website, or another location in the database. A hyperlink address have up to four parts: the text that is displayed in the field; the path to a file or URL; a sub-address which is a location in the file or page in the web sit; and the text is displayed as the tool tip. The data consist of up to four parts each separated by the pound sign (#): Display-Text#Address#Sub-Address#Screen-Tip. The address is the only required part of the string. Example:

          Internet hyperlink example: Google Home
          Page#http://www.google.com#
       
          Database link example: #c:\My
          Documents\database.mdb#MyTable

Description (optional)

Enter a brief description of what the contents of the field are.

Field Properties

Select any pertinent properties for the field from the bottom pane. Properties for each field are set from the bottom pane of the Design View window.

• 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:

1. Byte - Positive integers between 1 and 255
2. Integer - Positive and negative integers between -32,768 and 32,767
3. Long Integer (default) - Larger positive and negative integers between -2,147,483,648 to 2,147,483,647.
4. Single - Single-precision floating-point number
5. Double - double-precision floating-point number
6. Decimal - Allows for Precision and Scale property control

• Format conforms the data in the field to the format specified in the format property. For text and memo fields, this property has two parts that are separated by a semicolon. The first part of the property is used to apply to the field and the second applies to empty fields.

Text and memo format:

                                                         Text Format

Format                   Datasheet Entry     Display      Explanation

@@@-@@@@          1234567                      123-4567     @ indicates a required character or space
@@@-@@@&           123456                       123-456      & indicates an optional character or space
<                                 HELLO                    hello           < converts characters to lowercase
>                                  HELLO                        HELLO      > converts characters to uppercase
@\!                             Hello                          Hello!          \adds characters to the end
@;"No Data Entered"    Hello                          Hello         
@;"No Data Entered"    (blank)                       No Data Entered

Number Format:

Select one of the preset options from the drop down menu or construct a custom format using symbols explained below:

                                                     Number Format

Format     Datasheet Entry   Display        Explanation

###,##0.00   123456.78        123,456.78     o is a placeholder that displays a digit or 0 if there is none
$###,##0.00        0                   $0.00         # is a placeholder that displays a digit or nothing if there                                                                   is none
###.00%          123                 12.3%         % multiplies the number by 100 and added a percent sign

• Currency Format: This formatting consist of four parts separated by semicolons:format for positive numbers; format for negative numbers; format for zero values; format for Null values.

                                                            Currency Format
Format                                                 Explanation
$##0.0;($##0.00)[Red];$0.00;"none       Positive values will be normal currency format, negative
                                                             numbers will be red in parentheses, zero is entered for zero
                                                             values, and "none" will be written for Null values.

• Date format

         In the table below, the value "1/1/01" is entered into the datasheet, and the following values are            displayed as a result of the different assigned formats.