Create a database • Create tables in Datasheet and

Page 1 of 43 MGT3HRI – Human Resource Information Systems In‐Lab MS ACCESS Database Exercise Tutorial Session 1 ‐ Fundamentals of MS ACCESS Design and MS ACCESS Database Design and implementation of ACCESS query Databases and Database Objects: An Introduction Objectives: • Design a database to satisfy a collection of requirements • Describe the features of the Access window • Create a database • Create tables in Datasheet and Design views • Add records to a table • Create and use a query • Create and use a form • Create and print custom reports • Modify a report in Layout view In order to understand the development of MS ACCESS database from concept to use of the system, one must be able to describe the following in detail: • Identify the tables • Determine the primary keys • Determine the additional fields • Determine relationships between the tables • Determine data types for the fields • Identify and remove any unwanted redundancy • Determine a storage location for the database • Determine additional properties for fields • Determine the best method for distributing the database objects Start the MS ACCESS file by clicking on the MS ACCESS short key, and name the new data base by choosing your name_session 1. Page 2 of 43 The first step of creating a database is to create a table. The table in this context refers to the set of information put together and categorized based on a number of parameters. These parameters are then used to deliver results upon query and/or develop modifications in the data. In order to develop the tables, you need to design your table first. In the design view, enter the following table with the descriptions provided: Page 3 of 43 Page 4 of 43 Enter the information table b “Business Analysis Table” Once you have set up the table including the primary key, you need to close the table to save the content. To do this, click the Close button for the open table to close the table. Page 5 of 43 Creating Additional tables • Open the Navigation Pane • Click Create on the Ribbon to display the Create tab • Click the Table Design button (Create tab | Tables group) to create a new table in Design view • Type the desired field name in the Field Name column • Click the Primary Key button (Table Tools Design tab | Tools group) if the field you are adding is the primary key • Type the desired description in the Description column Page 6 of 43 Using the Simple Query Function in the Data base Queries are simple questions, the answers to which are in the database. ACCESS contains powerful query functions that helps to find answers to a wide variety of questions pertaining to the data that is present in the query. Hence it is imperative that in order to have as much information included in the answers, the tables of data be an complete as possible. Once you have examined the question you want to ask to determine the fields involved in the questions, you can begin creating the query. If there are no restrictions involved, in the query nor any special orders or calculations, you can begin creating a simple query. For a simple query, start with a query wizard button; • If necessary, open the Navigation Pane • Select the table you wish to query • Click Create on the Ribbon to display the Create tab • Click the Query Wizard button (Create tab | Queries group) to display the New Query dialog box • Be sure Simple Query Wizard is selected, and then click the OK button (New Query dialog box) to display the Simple Query Wizard dialog box • Follow the remaining steps in the wizard to finish creating the query Page 7 of 43 Page 8 of 43 Page 9 of 43 Click Finish and the report (query result) should look like this; Save your results. Page 10 of 43 Using a Criterion in a Query • Right‐click the query to open in the Navigation Pane to produce a shortcut menu • Click Design View on the shortcut menu to open the query in Design view • Click the Criteria row in the column for the field for which you want to specify the criteria, and then type the criteria • Click Design View on the shortcut menu to open the query in Design view Page 11 of 43 Creating and Using Forms • Select the table in the Navigation Pane for which you want to create a form • If necessary, click Create on the Ribbon to display the Create tab • If the form appears in Layout view, click the Form View button on the Access Status bar to display the form in Form view Page 12 of 43 Page 13 of 43 • Click the Save button on the Quick Access Toolbar to display the Save As dialog box • Type the desired form name, and then click the OK button to save the form • Click the Close button for the form to close the form Page 14 of 43 Creating Reports Reports are a tool to communicate the findings of the querries that have been asked from the ACCESS databse. Generally, reports are as inclusive as possible, however, depending on the merits of the report audience, you can be selective in terms of information disclosure etc. • Select the table in the Navigation Pane for which you want to create the report • Click Create on the Ribbon to display the Create tab • Click the Report button (Create tab | Reports group) to create the report • Click the Save button on the Quick Access Toolbar to display the Save As dialog box and then type the name of the report • Click the OK button (Save As dialog box) to save the report Page 15 of 43 Adding totals to the report • Select the field you want to total • Click Design on the Ribbon to display the Design tab • Click the Totals button (Report Layout Tools Design tab | Grouping & Totals group) to display the list of available calculations • Click Sum to calculate the sum of the amount of paid values • Click the Save button on the Quick Access Toolbar to save your changes to the report layout Page 16 of 43 MGT3HRI – Human Resource Information Systems In‐Lab MS ACCESS Database Exercise Tutorial Session 1 ‐ Querying a Database Objectives: • Create queries using Design view • Include fields in the design grid • Use text and numeric data in criteria • Save a query and use the saved query • Create and use parameter queries • Use compound criteria in queries • Sort data in queries • Join tables in queries • Create a report and a form from a query • Export data from a query to another application • Perform calculations and calculate statistics in queries Create a Query in Design View Most of the time, you will use the Design view to create queries. Once you have created a new query in the Design View, you can specify fields, criteria, sorting, and calculations and so on. Page 17 of 43 • Click Create on the Ribbon to display the Create tab • Click the Query Design button (Create tab | Queries group) to create a new query • Click the table to add to the query • Click the Add button to add the selected table to the query • Click the Close button to remove the dialog box from the screen • Drag the lower edge of the field list down far enough so all fields in the table appear To add Fields to the Design Grid Once you have a new querydisplayed in Design view, you are ready to make entries in rhw design grid located at the lower pane of the wondows. You add the fields you want included in the query to feach Field row in the grid. Only the fields that appear in the design grid will be included in the results of the query. The following steps begin the creation of a query that might be used to pbtain the client number, client name, amount paid and current due for a particular client. • Double‐click each field to add to the query Page 18 of 43 Using Text Data in a Criterion To use TEXT DATA (Data in the field whose type is TEXT) in the criteria, simply type the text in the Criteri
a row below the corresponding field name, the following step finishes the creation of the query the company may use to obtain the client number, client name, amount paid and current due amount of the client BC76. • Click the Criteria row for the field to produce an insertion point • Type the criterion • Click the View button (Query Tools Design tab | Results group) to display the query results Page 19 of 43 • Click the Save button on the Quick Access Toolbar to display the Save As dialog box • Type the name of the query • Click the OK button (Save As dialog box) to save the query Page 20 of 43 To Use Wild card • If necessary, click the Criteria row below the desired field to produce an insertion point • If necessary, delete the current entry • Type the criterion containing the wildcard character (*) • Type Gr* as the criterion (wildcard) Page 21 of 43 View the query result by clicking the View Button Make sure you save this object as a report To use Criteria for a field not included in the Results Page 22 of 43 • Click the View Button • Erase the Criteria that you had for the previous query • Add Georgetown as the new criteria in the City Field. View the results Page 23 of 43 Create a Parameter Query This is known as Parameter query that asks you for a specific parameter when doing a search. The following steps create a parameter query that will provide answers within the predefine boundaries of the parameters defined. • If necessary, return to Design view and type the criterion for a parameter query ([Enter City], for example) Run the query Page 24 of 43 Save the above as Client City Query. Page 25 of 43 Using Compound Criteria Using a Compound Criterion Involving AND To combine Criteria with AND place the criteria on the same row as the design grid. The following steps use an AND criterion to enable the organization to find those clients whose amount paid is greater than $3000 and whose business analyst is Analyst 11. • Open the query in Design view • Add the criteria for two fields in the Criteria row To use a compound criterion involving OR To combine Criteria with OR place the criteria on the separate row as the design grid. The following steps use an OR criterion to enable the organization to find those clients whose amount paid is greater than $3000 and whose business analyst is Analyst 11 (or both). • Open the query in Design view • Add criterion for one field to the Criteria row Page 26 of 43 • Add criterion for another field in the or row (the row below the Criteria row) Sorting To Clear Design Grid Page 27 of 43 • Open the query in Design view • Click just above the column heading in the first column in the grid to select the column • Hold the SHIFT key down and click just above the last column heading to select all the columns • Press the DELETE key to clear the design grid Sort Data in a Query • Open the query in Design view • Click the Sort row below the field you wish to sort, and then click the Sort row arrow to display a menu of possible sort orders Page 28 of 43 • Click the desired sort order View results Page 29 of 43 To Omit Duplication • Open the query in Design view • Click an empty field in the design grid • Click the Property Sheet button (Query Tools Design tab | Show/Hide group) to display the property sheet • Click the Unique Values property box, and then click the arrow that appears to produce a list of available choices • Click Yes and then close the Query Properties property sheet by clicking the Property Sheet button (Query Tools Design tab | Show / Hide group) a second time Page 30 of 43 Sorting on Multiple Keys • Open the query in Design view • Select a sort order in the Sort column for multiple fields View the results Page 31 of 43 To Create a Top‐Values Query • Open the query in Design view • Click the Return box arrow (Query Tools Design tab | Query Setup group) to display the Return box menu • Click an option corresponding to the values you wish to return Page 32 of 43 Joining Tables values in matching fields. In this case you need to find records in the Client table and the Business Analyst that have the same value in the Business Analyst Number field. Page 33 of 43 To Join Tables • Click the Query Design button (Create tab | Queries group) to create a new query • Add two related tables to the new query • Add the desired fields from each table to the query Page 34 of 43 • Click the Client table • Click the Add button to add a field list for the client table • Close the SHOW TABLE dialog box by clicking the close button  In the design grid, include the Business Analyst Number, Last Name and the First Name Fields from the Business Analyst table as well as Client Number and the Client Name field from the Client Table.  Selecting the Ascending sort order for both the Business Analyst Number field and the Client Number field. Page 35 of 43 View the Query results Page 36 of 43 Creating a form for a Query • Select the query in the Navigation Pane • Click the Form button (Create tab | Forms group) to create a simple form Page 37 of 43 Using a Calculated Field in a Query • Click Zoom on the shortcut menu to display the Zoom dialog box • Type the calculation in the Zoom dialog box • Click the OK button (Zoom dialog box) to enter the expression Page 38 of 43 To Change a Caption • Open the query in Design view Page 39 of 43 • Click the field in the design grid to which you wish to add the caption, and then click the Property Sheet button (Query Tools Design tab | Show/Hide group) to display the properties for the field • Click the Caption box, and then type the desired caption • Close the property sheet by clicking the property Sheet button a second time Crosstab Querries Page 40 of 43 To Create a Crosstab Query Page 41 of 43 • Click the Query Wizard button (Create tab | Queries group) to display the New Query dialog box • Click Crosstab Query Wizard (New Query dialog box) • Click the OK button to display the Crosstab Query Wizard dialog box • Follow the instructions in the wizard to select the row and column headings for the query, and then name the query Page 42 of 43 Click the Next button to display the next Crosstab Query Wizard screen. Page 43 of 43

Leave a Reply

Your email address will not be published. Required fields are marked *