INTRODUCTION TO DATA MODELLING; Creation of Table, Query, Forms and Report

Definition of Data Modelling
Data Modelling is the process of designing Logical and Physical structure of on or more database to accommodate the information needs of the users in an organisation for a defined set of application.
CREATING 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 the icons to create a table.

1. 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. 
2. Create table using wizard will step you through the creation of a table. 
3. 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 key 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. To rename them with more descriptive titles that reflect the content of the fields, select FormatlRename Column from the menu bar or highlight the column, right-click on it with the mouse, and select Rename Column from the shortcut menu.

Create a 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. 

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

3.   Description (optional) - Enter a brief description of what the contents of the field are. 

4.    Field Properties - Select any pertinent properties for the field from the bottom pane. 

Create a Query in Design View

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

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

• From the Queries page on the Database Window, click the New button. 
• Select Design View and click OK. 
• Select tables and existing queries from the Tables and Queries tabs and click the Add button to add each one to the new query. 
• Click Close when all of the tables and queries have been selected. 
• 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.

Creating Forms

Forms are used as an alternative way to enter data into a database table.

Create Form by Using Wizard

To create a form using the assistance of the wizard, follow these steps: 

• Click the Create form by using wizard option on the database window. 
• From the Tables/Queries drop-down menu, select the table or query whose datasheet the form will modify. Then, select the fields that will be included on the form by highlighting each one the Available Fields window and clicking the single right arrow button > to move the field to the Selected Fields window. To move all of the fields to Select Fields, click the double right arrow button >>. If you make a mistake and would like to remove a field or all of the fields from the Selected Fields window, click the left arrow < or left double arrow << buttons. After the proper fields have been selected, click the Next > button to move on to the next screen. 
• On the second screen, select the layout of the form. 

1. Columnar - A single record is displayed at one time with labels and form fields listed sideby-side in columns 
2. Justified - A single record is displayed with labels and form fields are listed across the screen 
3. Tabular - Multiple records are listed on the page at a time with fields in columns and records in rows Datasheet - Multiple records are displayed in Datasheet View 
4. Click the Next > button to move on to the next screen. 

• Select a visual style for the form from the next set of options and click Next >. 
• On the final screen, name the form in the space provided. Select “Open the form to view or enter information“ to open the form in Form View or “Modify the form's design“ to open it in Design View. Click Finish to create the form. 

Create Form in Design View 

To create a form from scratch without the wizard, follow these steps: 

• Click the New button on the form database window. 
• Select “Design View“ and choose the table or query the form will be associated with the form from the drop-down menu. 
• Select ViewiToolbox from the menu bar to view the floating toolbar with additional options. 
• Add controls to the form by clicking and dragging the field names from the Field List floating window. Access creates a text box for the value and label for the field name when this action is accomplished. To add controls for all of the fields in the Field List, double-click the Field List window's title bar and drag all of the highlighted fields to the form. 

Reports

Reports will organize and group the information in a table or query and provide a way to print the data in a database.

Using the Wizard

Create a report using Access’ wizard by following these steps: 

• Double-click the “Create report by using wizard“ option on the Reports Database Window. 
• Select the information source for the report by selecting a table or query from the Tables/Queries drop-down menu. Then, select the fields that should be displayed in the report by transferring them from the Available Fields menu to the Selected Fields window using the single right arrow button > to move fields one at a time or the double arrow button >> to move all of the fields at once. Click the Next > button to move to the next screen. 
• Select fields from the list that the records should be grouped by and click the right arrow button > to add those fields to the diagram. Use the Priority buttons to change the order of the grouped fields if more than one field is selected. Click Next > to continue. 
• If the records should be sorted, identify a sort order here. Select the first field that records should be sorted by and click the A-Z sort button to choose from ascending or descending order. Click Next > to continue.  
• Select a layout and page orientation for the report and click Next >. 
• Select a color and graphics style for the report and click Next >. 
• On the final screen, name the report and select to open it in either Print Preview or Design View mode. Click the Finish button to create the report.