Creating And Running Queries Archives

DATA MODELLING Data modeling is a process used to define and analyze data requirements needed to support the processes within the scope of corresponding information system in an organization. Creating simple data mode Microsoft Access offers one table, queries, forms, reports, macros and modules and these will be created before creating a data model. Table: All data are stored in table i.e. MS. Access stores data in tables and can exist as a simple file that allows basic data as a single file that allows basic data storage and retrieval. Query: This is used to add, change, delete or update data in database. It enables one to find or operate on the data in the tables. Forms: These are screens used for displaying data. It is a screen that displays the output of data. Reports: This is used for outputting of data. It is a screen that displays the output of data. Pages: This is used to enter or display data online. Macro is a stored series of commands that automatically carries out an action. It is a toll that allows one to automate task and add functionality to the forms, reports and controls. Modules: This is a collection of user defined function, subroutines and global variables, i.e. it is where one can write his own functions and programs. Creating a table A table is a set of columns and rows. Each column is called a field. With a table, each field must be given a name and no field can have the same name. each row in a table is called a record. Employees Last Name First Name Phone Number Smith John 080278911224 John Mary 080771100821 Adams Steve 080919210022 Table’s name – Employee Fields – last name, first name, phone no Records – Smith John Before creating a table MS. Access must be loaded. To load MS. Access, take the following steps Click start button and then all program Move to MS Office, click on MS Access Once access is running, an initial screen will be displayed. From this screen, you can either Create a new blank database Open an existing database Choose blank database and specify a new filename for the database. Click on the OK button to create the new database Tables can be created in two ways (1) designing view (2) datasheet view Creating a table using design view Click create tab and click on table icon Pull down the view menu and choose design view. The table design view will appear. Fill in the field name, data type and description for each column/field in the table. High light the field you wish to make the primary key and click on the primary key button on the button bar. A small key appears next to the field name on the left side. Pull down the office menu and choose Save As menu icon. Change the table to customer and click OK button. Viewing and adding data to a table Data can be added, deleted or modified in table using a simple spreadsheet-like display. To bring up a table, highlight the name of the table, and then double-click on the name. To add to the table, simply type in values for each of the field. Press the tab key to move between records. To save the new data, pull down the office menu and choose save. To navigate to other records in the table, use the navigation bar at the bottom of the screen. Creating relationship between tables Note that one of the main characteristics of relational database is the fact that all tables are related to one another. In the bank database under consideration, the Customers table is related to the Account table by virtue of the Customer ID field appearing in both tables. Access has a way of showing this relationship by using the relationship screen. Steps in creating relationship Close the tables by right-clicking on each of the table and select close. NB, no relationship will be created for opened tables. Click on the database tools tab and the click on relationship button. The blank relationship screen will appear. The show table dialog box will also appear. Highlight both the Customers table and the Account table and then click on the Add button. Click on the close button to close the dialog box. The relationship screen will re-appear. Click on the Customer ID field in the Customers table and drag it on top of the Customer ID field in the Account table upon releasing the mouse button, the edit relationship dialog box will appear. Check off “Enforce Integrity” button. Click on the create button to create the relationship. The relationships screen reappears with the new relationship in place with the symbols ”I” (indicating one) and the infinity system (indicating many)on the Customers ID of Customers and Account tables respectively Creating and Running Queries Queries are fundament at means of accessing and displaying data from tables. Queries can access a single table or multiple tables. Examples of queries for our bank database might include: Which Customers live in Jos? Which Accounts have less than 1500 balance? What is the total amount of money deposited by each Customer? Which checking account has the largest balance? Etc. Activities on single table Queries Click on the create tab and select the query wizard button Highlight the simple query wizard and click on the OK button. Three sections will appear – Tables/Queries Available fields Selected fields Pull down the tables/queries list and choose Customer table. Note that the available fields change to list only those fields in the Customer table From the list of available fields on the left, move the first name, surname, address, city and state fields over to the selected field area on the right. Click on the next button to move to the next and final step in the simple query wizard. Type Customer address as the name of the query Choose open the query to view information and click on the finish button when the query executes, only the selected fields will appear with all the row Close the query by clicking on the (x) in the right corner of the query result. The access main screening showing the Queries tab should appear. Note that the new query Customer address appears under the Customers table. Creating of form Note that forms are created from the table. Select the table you wish to create form Click on create table e.g. for Customer Click on form, the form for Customer’s table will be displayed. Creating report Reports are created from tables e.g. creating report from account table. Click on Account table Click on create tab Click on report and report for the account table is displayed. Importance/significant of data model Data model facilities interaction among the designer, the application programmer and the end user It serves as communication tool A well-developed data model fosters improved understanding of the organization for which the database design is developed Hierarchical data model promotes data sharing Data model helps modelers to identify the highest – leve of relationship between different entities. The physical data model helps to represent the actual structure of a database – table and columns or the messages sent between computer processes. Examples of structured data modelling Physical data modelling Logical data modelling Conceptual data modelling Others are Canonical Application Business requirement Integration data modelling Enterprise Business information model Data usage model (data flow diagram) etc. Evaluation What is data modelling? Create a simple data model Explain table form, report Explain how to create table, form & report Mention how to run query Also See: DATA MODEL WORD PROCESSOR WORD PROCESSING Computer Software ART OF INFORMATION PROCESSING