Microsoft Access Lab Notes

IS 312 Spring, 2012  Microsoft Access Lab Notes 1. Getting Started – Creating a new, empty database called “SCHOOL.” a. Insert your removable media. b. Load Access 2010. Click Blank Database under Available Templates c. In the right panel, click the Open File icon . In the File New Database dialog box, specify the drive letter for your removable media (see below left), click Ok and then click Create. The default file name for the new database is database1. d. The opening screen of the new database will prompt you to create the first table (below right). Ribbon Navigation Pane Object Window 2. Creating a table in Design View a. Click the View icon on the Ribbon to create the first table in Design View, save the table under the name “Student.” b. Enter the following field names and data types, set field properties, and set the Primary Key. ID (Primary Key) Last Name -------First Name -------Major -------------Units --------------GPA --------------- c. Click the Text Text Text Text Number (Field Size: Integer) Number (FieldSize: Single; Format: Fixed; Decimal Places: 2) icon to save the table definition. 3. Populating the Student Table (data entry) Click the View icon again to switch to the Datasheet View. Enter sample data as follows: Page 1 of 10 IS 312 Spring, 2012  Microsoft Access Lab Notes 4. Creating a second table, “Major”: a. Click the Create tab above the Ribbon, click Table Design, and save the table under the name “Major.” Major Name --------(Primary Key) Department ---------GPA Requirement ---- Text Text Number (FieldSize: Single, Format: Fixed, Decimal Places: 2) 5. Import tables from one Access database into another database a. Click the File tab and select New. In the right panel, click the Open File icon . In the File New Database dialog box, specify the drive letter for your removable media, name the database “SCHOOL”, click Ok, and then click Create. b. Click the External Data tab and select Access. A Get External Data dialog box opens (see below left). Use the Browse button to find and open database1 and click Ok. c. An Import Objects dialog box opens (see below right). Click Select All to select both tables you created earlier, and click Ok. After you close the Get External Data dialog box, the two imported tables will appear in the SCHOOL database. 6. Defining relationships between tables a. Click the Database Tools tab, and then click Relationships . Use the Show Table box (below left) to put both tables in the Object Window (below right). Close the Show Table box. b. Select the common field Major Name from the Major table, drag and drop it on field Major Page 2 of 10 IS 312 Spring, 2012  Microsoft Access Lab Notes of the Student table. c. In the Edit Relationships window (below left), check the Enforce Referential Integrity box, and click “Create”. A line now links the two tables (below right). The “1” and “∞” signs indicate that this is a one-to-many relationship (Access automatically detects such relationships). Save the relationship and close the Object Window. d. Referential integrity is a system of rules used to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data. In our example here, if referential integrity is enforced between the Major table and the Student table, Microsoft Access will make sure that every Student record has a valid Major field value that can be found in the Major table. Conversely, no Student record can have a Major field value that does NOT exist in the Major table. To see how these rules work, we will try to make some changes to the data and observe how the system responds: i. Open the Major table and attempt to delete the Marketing major record. You should receive an error message. Can you explain why this happened? ii. Open the Student table and try to change Jose Perez’s major to FIN (for Finance). Again, you should receive an error message. Can you explain why this happened? 7. Designing a single-table select query a. Click the Create tab. Click Query Design on the Ribbon. What appears in the Object Window is called Query-By-Example (QBE) window, along with the familiar Show Table dialog box. Add the Student table to the QBE window and close the Show Table box. b. Double click each field name or drag-and-drop it into a desired column. Page 3 of 10 IS 312 Spring, 2012  Microsoft Access Lab Notes c. Query exercise #1: to list all students who have a GPA greater than 2.50. Enter “> 2.5” in the row labeled criteria under the field: GPA (above left). d. To run the query, click the Run icon on the Ribbon. Above right is the output. 8. Defining Calculated Fields (Expressions) a. Calculated fields are used in queries to dynamically obtain results using existing fields. As a general rule, if a field value can be calculated based on other fields, then this value should not be stored permanently in a table (why?). Therefore, a calculated field is not stored in a table. Instead, it is defined in a query. Every time you run the query, values in a calculated field will be recalculated. b. We will insert a calculated field into the query. This new field, called Full Name, will link values from existing fields Last Name and First Name to form a student’s full name. If a student has a name change and thereafter we rerun the query, the calculated field will reflect the change. c. In the QBE window, highlight the column for Last Name, click Insert Columns on the Ribbon to add a blank column. d. Put your cursor in the first row (Field:) of the new column. Click the Builder icon on the Ribbon to open the Expression Builder. Enter the following expression in the blank box (see screen shot below left): Full Name: [First Name] & “ ” & [Last Name] If your expression includes a field name, that field name must be enclosed by a pair of square brackets “[].” In our example, both First Name and Last Name are field names, so they are each placed between a pair of “[].” If your expression includes any text characters (called a string), they must be placed between quotation marks. In our example, we would want a space between the first and last names of each student, hence the space between the quotation marks. e. Click OK to close the Expression Builder. Put a  in the Show box under Full Name. Remove the  from Last Name and First Name (below right). Now run the query. f. Question: how can we display the query output sorted by students’ names? g. We will now insert another calculated field into the query. This field, called Total points, will multiply values from existing fields Units and GPA. Again, because such a field is to be calculated dynamically using the most up-to-date values, it should NOT be permanently Page 4 of 10 IS 312 Spring, 2012  Microsoft Access Lab Notes stored in the source table (the Student table in this case). h. In the QBE window, put your cursor in the first row (Field:) of the blank column next to GPA. Open the Exp. Builder and enter the following expression (see screen shot below left): Total Points: [Units] * [GPA] Again, remember that a field name must be enclosed by a pair of “[].” i. Click OK to close the Exp. Builder. Put a  in the Show box under Total Points. Now run the query (below right). j. To change the format of Total Points, switch back to Query Design View and right click the column. Select Properties. In the Field Properties dialog box, set Format to Fixed and Decimal Places to 1. Close the dialog box and rerun the query to verify the result. k. Save the query under the default name, Query1. 9. Calculating simple statistics (Aggregate Queries) a. You can use the Totals ( ∑) tool to calculate simple statistics such as totals, averages, maximum, minimum, count, etc. Still in the Query1 design view, delete every column except Major, Units, and GPA. b. Click the Totals icon on the Ribbon. A new row, Total:, appears in the QBE window. To calculate average units and GPAs by major, change Group By under Units and GPA to Avg (below left). Change the format of the Units column so that the resulting value will keep one decimal place. Change the format of the GPA column to retain two decimal places. Run the query and you should see the result as shown (below right). c. Note from the result that with this type of query, for each field that you calculate a statistic, a calculated field will replace the original field. In our example, two calculated fields, AvgOfUnits and AvgOfGPA would replace the original fields, Units and GPA. d. Click the Office button at the top left corner, and select Save As => Save Object As. Enter Page 5 of 10 IS 312 Spring, 2012  Microsoft Access Lab Notes Query2 to replace “Copy of Query1.” Close Query2. 10. Designing select queries – single-field logical operators a. Any query involving more than one select criterion (condition) requires the use of logical operators. In the simplest case, you have two conditions linked by either an “and” or an “or.” Cond. A True True False False Cond. B True False True False A and B Cond. A True True False False ? ? ? ? Cond. B True False True False A or B ? ? ? ? b. Open Query1 in Design View. In MS Access, two conditions entered within the same field must be separated by an “and” or an “or.” The screen shots below show two examples. The one on the left specifies a number range between 75 and 90 inclusive. The one on the right specifies two number ranges, either < 75 or > 90. Note that you could also use the key word, “ Not,” to express the same condition. For example, “Not (> = 75 and < = 90)”is equivalent to “< 75 or > 90.” c. What would we get if we entered < 75 and > 90 ? d. What would we get if we entered > 75 or < 90 ? 11. Designing select queries – multi-field logical operators a. In MS Access, if your select criteria involve several fields, WHERE each criterion is located (i.e., same rows or different rows) determine the type of operator intended. b. If two conditions are each entered under a separate field but in the same row, the intended logical operator is an “and” (see below left). c. If two conditions are each entered under a separate field but in different rows, the intended logical operator is an “or” (see below right). d. Save and close Query1. Page 6 of 10 IS 312 Spring, 2012  Microsoft Access Lab Notes 12. Designing a multi-table select query (Inner-join Operation) a. Click the Create tab and then Query Design on the Ribbon. b. At the Show Table dialog box, add both Student and Major tables. Close the dialog box. c. Enter the following fields into the first row of the QBE window: Last Name, First Name, Major Name (from Major Table), GPA, and GPA Requirement. d. Specify a select criterion so the output will list those who have a current GPA below their major’s GPA requirement (see below left). e. Run the query (see below right). f. Delete the criterion under GPA Requirement. Save the query as Students by Major. An example of “crossfield comparison” 13. Design a Report a. We have very little control over the format and layout of a query output, but we can use Reports to custom-design an output document to fit our needs. However, it is always a good idea to use a query to gather all the information needed, and then use the query as the data source for the report (we will use the query, Students by Major, that we created earlier). b. Click the Create tab and then click Report Wizard on the Ribbon. c. In the Report Wizard dialog box, under Tables/Queries, select Query: Students by Major as source of data, then click the button to include all the fields in the report (below left). d. Click Next. Click Major in the left panel. This allows the Wizard to follow the established relationship to group student records under each major. Click Next twice. e. Click the button, Summary Options, to calculate any summary statistics needed (below right). f. In the Summary Options dialog box, check the Avg box to calculate the average GPA for each major (below left). Does it make sense to calculate the sum of all GPAs? Page 7 of 10 IS 312 Spring, 2012  Microsoft Access Lab Notes g. Click Next twice. Enter “List of Students by Major” as a title for the report. Click Finish to preview the report (below right). h. You may now switch to the Design View to make other format/layout adjustments (Select Close Print Preview = > View = > Design View). 14. Designing a Macro a. Macros are tiny programs that can be executed independently to perform a series of specific tasks. b. Click the Create tab and then click Macros on the Ribbon. We specify what the macro does by creating a list of actions. When the macro is executed, the actions on the list will be carried out sequentially. An example (see below left): 1. Click the dropdown list next to Add New Action. Select DisplayHourglassPointer. This action can have an argument of either Yes or No. Setting it to Yes will turn the mouse pointer (an arrow) into an hourglass. 2. For the next action, select OpenReport. For action arguments, select List of Students by Major for Report Name, and Report for View. 3. For the next action, select OpenQuery. For action arguments, select Query1 for Query Name. 4. For the last action, Select DisplayHourglassPointer, but this time set action arguments to No. This action will restore the mouse pointer back to an arrow. c. Save the macro under the name Macro1. Close the Object Window. Right click the macro and select Run. Observe what happens. Close both the report and the query opened by the macro. d. If a macro is named “AutoExec,” it will run automatically every time the database containing the macro is opened. Right click to rename Macro1 to AutoExec (below right). Close your database and then reopen it. The AutoExec macro allows you to launch many actions at the start of your database application. Page 8 of 10 IS 312 Spring, 2012  Microsoft Access Lab Notes 15. Designing a Form a. We use forms primarily for entering new data or updating existing data. A form is often designed to look like its paper version. This makes data entry using a source document (paper) more efficient and accurate. b. In this exercise, we will create a form that contains another form (Main/subform) using data from the two tables, Major and Student. c. Click the Create tab and select More Form Design => Form Wizard on the Ribbon. d. In the Form Wizard dialog box, select Table: Major under Tables/Queries. Click the button to include all the fields from the Major table. These fields will be on the Main Form (see above left). e. Under Tables/Queries, select Table: Student as the data source for the Subform. Click the button several times to include the following fields in the Subform: ID, Last Name, First Name, Units, and GPA (see above right). Do not include the field Major (why?). f. Click Next then Finish to view the draft version of this form. g. Switch to Design View. We will modify the form title and make other layout/format adjustments. (see below left). h. Switch back to the Form View to see the effect of the changes we made (below right). i. Save the form. In the Navigation Pane, note that Access has saved the Main form and the Subform separately under the names, Major and Student Subform, respectively. 16. Designing a custom menu (on a blank form) We can use a form to create a custom-designed menu that controls all of our database application activities (Access calls it a switchboard). We start with a blank form. We then add various control objects (primarily command buttons) onto the form. Each command button will be Page 9 of 10 IS 312 Spring, 2012  Microsoft Access Lab Notes associated with an action, such as opening a query or a form, running a macro, etc. Therefore, before we create such a form, we should have everything else ready (e.g., queries, forms, reports, macros, etc.). a. b. c. d. Click the Create tab and select Blank Form on the Ribbon. Switch to Design View. Adjust the blank form to desired size (3″ × 6″, for example), background color, etc. Use the label object Aa on the Ribbon to create a form title, Main Menu (below left). Click the Button (Form Control) icon on the Ribbon and then click any place in the form to open the Command Button Wizard dialog box (see below right). Actions to be associated with the button is organized by categories. e. Select Form Operations under Categories. Select Open From under Actions. Click Next. f. Select Major on the next screen (the button, when clicked, will open the Major main form we created earlier. Click Next twice. Select the radio button next to “Text” and change the default text to “Open Students by Major Form” (see below left). Click Finish. g. Use the same method to add two more command buttons to the form. One button is labeled “Preview List of Students,” and it will, when clicked, open the “List of Students by Major” report in print preview mode. The other button is labeled “Close,” and it will close the menu form when clicked (see below right). h. Save the form under the name, Main Menu. Switch to Form View to test all three buttons. j. We will now modify the AutoExec macro so it will open the Main Menu every time we open the SCHOOL database. Open the macro in Design View. Delete all the existing actions. Enter OpenForm as the new action, and select Main Menu as the Action Arguments. Save the macro. k. Close the database and then reopen it to test our work. Page 10 of 10