Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
EXTENDED LEARNING MODULE J (Office 2007 Version)
IMPLEMENTING A DATABASE WITH MICROSOFT ACCESS
JUMP TO THE SUPPORT YOU WANT ? Lecture Outline
? Modules, Projects, and Data Files ? Slide Reviews
? Assignments and Exercises
CONTACT INFORMATION: Stephen Haag (shaag@du.edu)
STUDENT LEARNING OUTCOMES 1. Identify the steps necessary to implement the structure of a relational database using
the data definition language provided by Microsoft Access.
2. Demonstrate how to use the data manipulation subsystem in Access to enter and
change information in a database and how to query that information.
3. Explain the use of the application generation subsystem in Access to create reports and
data entry screens.
MODULE SUMMARY This Extended Learning Module teaches your students how to implement a database design in Microsoft Access, version 2007.
It is assumed that your students have completed Extended Learning Module C and have also read Chapter 3.
The primary sections of this module include:
1. Defining Relationships within the Solomon Enterprises Database 2. Entering Information into the Solomon Database 3. Creating a Simple Query Using One Relation
4. Creating an Advanced Query Using More than One Relation 5. Generating a Simple Report
6. Generating a Report with Grouping, Sorting, and Totals 7. Creating a Data Input Form
Mod J Web-1
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
LECTURE OUTLINE
INTRODUCTION (p. J.2)
1. Implementing the structure of the Solomon Enterprises Database 2. Implementing the Raw Material Relation Structure 3. Implementing the Concrete Type Relation 4. Implementing the Bill of Material Relation
DEFINING RELATIONSHIPS WITHIN THE SOLOMON ENTERPRISES DATABASE (p. J.12)
ENTERING INFORMATION INTO THE SOLOMON DATABASE (p. J.15) 1. Changing the Structure of Information in Relations
CREATING A SIMPLE QUERY USING ONE RELATION (p. J.18) 1. Simple Query with a Condition (Conditional Query)
CREATING AN ADVANCED QUERY USING MORE THAN ONE RELATION (p. J.21)
GENERATING A SIMPLE REPORT (p. J.23)
GENERATING A REPORT WITH GROUPING, SORTING, AND TOTALS (p. J.25)
CREATING A DATA INPUT FORM (p. J.32)
END OF MODULE (p. J.34)
1. Summary: Student Learning Outcomes Revisited 2. Key Terms and Concepts 3. Short-Answer Questions 4. Assignments and Exercises
Back to Jump List
Mod J Web-2
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
MODULES, PROJECTS, AND DATA FILES
? Building Value Chains: Helping Customers Define Value
? Using Relational Technology to Track Projects: Foothills Construction ? Build a Web Database System: Web-Based Classified System
? Creating a Database Management System: Mountain Bike Rentals
DATA FILES
There are no data files associated with this chapter. There may, however, be data files for the Group Projects you choose to use.
Back to Jump List
Mod J Web-3
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
? ? These are the Student Learning Outcomes for the module. Use them as a road map to inform your students of what you will be covering. At the end of the module is a summary of each SLIDE 2 ? ? ? SLIDE 3 This slide presents the organization for the module. It identifies the major sections and learning outcomes associated with each. ? ? SLIDE 4 This slide presents the organization for the module. It identifies the major sections and learning outcomes associated with each. ? ? ? ? ? ? This slide shows the steps involved in creating a database. o Step 1: Define the entity classes and primary keys (covered in Extended Learning Module C) o Step 2: Define relations among the entity classes (covered in Extended Learning Module C) o Step 3: Define fields for each relation or file (covered in Extended Learning Module C) Step 4: Use a data definition language to create the database (covered in this Module) The next 4 slides provide the structure of the Solomon Database from Extended Learning Module C. This module shows your students how to implement the last step in the process of designing and building a database. It builds on the material in Extended Learning Module C, although you don’t have to have covered C in order to follow this module, since Extended Learning Module C shows you how to design entity relationship diagrams and normalize a database, and this module is hands-on demonstration of using Microsoft’s Access. If it’s been some time since you covered Extended Learning Module C, you might want to do a quick refresher course. SLIDE 6 SLIDE 5 Mod J Web-4
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
? ? This slide and the next two show the structure of the Solomon Enterprises database (Figure J.1 on page J.3). This slide shows the Concrete Type, Customer and Employee relations SLIDE 7 SLIDE 8 ? This is the second slide showing the structure of the Solomon Enterprises database (Figure J.1 on page J.4). ? This slide shows the Supplier, Truck and Order relations. ? ? SLIDE 9 This is the third and last slide showing the structure of the Solomon Enterprises database (Figure J.1 on page J.4). This slide shows the Raw Material and Bill of Material relations. SLIDE 10 ? This slide defines a data dictionary. ? This slide and the next three present the steps, followed by illustrative screens that you take to create a table or relation. (Student Learning Outcome #1) ? SLIDE 11 This slide shows the first two screens in the series on how to create a database and shows the initial screens that you see when you start the process (Figure J.2 on page J.5). Mod J Web-5
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
? ? This third slide shows the next step in creating a database. This slide shows how to create a relation in Design view. SLIDE 12 ? This last slide in the set shows the last set of screens involved in creating a database (Figure J.3 on page J.6). SLIDE 13 ? This is the first of two slides focusing on creating the Raw Material relation. SLIDE 14 ? This second of two slides for this relation shows what the screen looks like for this step (Figure J.4 on page J.7). SLIDE 15 SLIDE 16 ? This slide shows the data dictionary screen you would get when creating the Concrete Type relation (Figure J.5 on page J.8). ? Creating the Concrete Type relation is very similar to the creation of the Raw Material relation. Mod J Web-6
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
? This is the first of three slide showing how to create the Bill of Materials relation to illuminate the many-to-many relationship between Concrete Type and Raw Material. ? This relation is a bit different in that it has a composite key since it’s an intersection table that connects the Raw Material relation to the Concrete Type relation. SLIDE 17 ? ? SLIDE 18 This slide deals with the primary key of this relation. For this composite primary key, you have to highlight both the Concrete Type and the Raw Material ID fields to make a primary key out of the combination. SLIDE 19 ? This figure illustrates the creation of a composite primary key (Figure J.6 on page J.9). ? The key symbol appears beside each of the parts of the composite key. ? This slide is the first of five showing how to define relationships between the tables. ? This is the last step in the creation of the structure of the database. It involves showing what primary key fields relate to which foreign keys. ? A foreign key is a primary key of one file (relation) that appears in another file (relation). SLIDE 20 ? ? SLIDE 21 This slide illustrates the relationships between all the tables in our database (Figure J.7 on page J.10). Tables are linked pair-wise according to the primary keys that appear in other tables. Mod J Web-7
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
? This slide defines the steps involved in creating the relationships. ? First you must get the tables on the palette. SLIDE 22 ? This second slide illustrates the first step in defining relationships (Figure J.10 on page J.13). ? Tables must be added to the palette if they are to be included in the relationship definition. ? It’s important that you establish these relationships because if your queries involve fields from different tables, they will probably not produce the correct results if you don’t correctly definition how the tables are linked (or “joined” in database parlance). ? The third slide and last slide in the series is the figure showing how the relationships between the tables look in Access (Figure J.11 on page J.14). The linking line doesn’t appear until you’ve closed the Edit Relationship dialog box. SLIDE 23 SLIDE 24 ? ? ? ? ? ? ? ? SLIDE 26 ? ? ? This slide starts the process of entering information into the database we created in the previous slides. (Student Learning Outcome #2) It’s not until we reach this point that we can actually enter our records into our database. Our previous steps were just preparing the database for the information. The preparations ensured that we would have a field for every data item, that insofar as possible, only the right type of information could be entered and that the tables are linked together correctly. This slide shows the structure of the Supplier relation as we specified it when creating the relation (Figure J.12 on page J.15). This relation has two fields that are numeric and text respectively. This slide shows the steps in entering information into the relation (Figure J.13 on page J.16). First, you have to open the relation in the datasheet view. In the design view, you get the data dictionary; in the datasheet view, you get what looks like a spreadsheet. The spreadsheet-looking screen is where you enter the information. SLIDE 25 Mod J Web-8
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
? This slide introduces the concept of referential integrity. ? Referential integrity ensures that your information is consistent. ? It ensures that you don’t put in a value as a foreign key that is not a primary key value in the original table. ? This makes sure that you don’t put in a non-existent Supplier ID into the Raw Material relation. ? A non-existent value would cause trouble when you create a view that links the table with the primary key (Supplier relation) and the table with the foreign key table (Raw Material relation). ? This slide show what happens in Access if you try to violate referential integrity (Figure J.14 on page J.17). ? We’re getting these error messages because when we established the relationships between the Supplier and Raw Material relations, we specified that referential integrity should be enforced. ? Therefore, Access won’t let up put in a supplier that is not in the Supplier relation. SLIDE 28 SLIDE 27 ? ? ? This slide demonstrates how you would begin if you wanted to change the structure of a relation (Figure J.15 on page J.18). If you’re looking at the left-hand screen, you would click on the Design button in the button bar. It’s the second one from the left. If you have the datasheet view of the relation open, you would again click on the Design button in the button bar. This time it’s the first one on the left. SLIDE 29 ? ? SLIDE 30 This slide begins the tutorial on creating queries. A query is a different type of structure from a table. It filters the information in the tables to show you only those fields you want to see. ? ? ? SLIDE 31 This figure shows the first step in creating a simple query (the top two screens in Figure J.16 on page J.19). We move down now from the Tables tab to the Queries tab. Again, we’re creating the queries in Design view, so we see a palette and a grid that we’ll use to specify what we’re querying. Mod J Web-9
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
? This slide is the continuation of the previous one showing the rest of Figure J.17 on page J.20). ? First, we must specify what tables to use. In this case, we need only one table since all the fields we need are in the Raw Material relation. ? Then we drag and drop the fields we want to see in the order in which we want to see them. ? When that’s done, we click on the exclamation button (Run) in the button bar to see the results and then save the query. SLIDE 32 ? ? SLIDE 33 This figure shows how to restrict the fields that appear based on some criterion (Figure J.17 on page J.20). If we only want to see the raw material items on hand in quantities of 400 or more units, we need to restrict the output to just these records or rows. ? ? ? ? SLIDE 34 This slide continues the conditional query theme with a slightly more complex condition. The query we implemented in the previous slides used only one table. This query necessitates the use of four tables. We can tell that by checking on which table each of the fields we want to see appears in. ? This is the first of several slides that show how to create an advanced query. This first slide in the series presents the first two steps. ? The beginning steps, the Queries tab and the calling up the design view window are the same as the last example. ? We have to tell Access which table or tables we want to use. ? As those tables pop up on the palette there should be lines linking them. ? The links should appear between the primary and foreign keys of respective tables. ? This slide shows the third and fourth step in creating an advanced query. ? Again, as in the simple query, you drag and drop the required fields into the grid. ? The advanced query is different in that many tables are involved and fields come from the different tables. ? To see the results of the query, you click on the exclamation button as before. SLIDE 36 SLIDE 35 Mod J Web-10
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
? ? SLIDE 37 ? This slide shows the first screen in Figure J.18 on page J.22. This is how the query window should look after you’ve chosen the tables and before you drag and drop the fields into the grid. Note the links between the tables. If you don’t see those, chances are that the query will not work correctly. SLIDE 38 ? This slide shows the second screen in Figure J.18 on page J.22. ? This is what you’ll see after having dragged and dropped all the necessary fields into their respective spots in the grid. ? This slide shows the last screen in Figure J.18 on page J.22 that shows the output of our query. ? Now you can see the results generated by the query. ? All that remains now is to save the query for future use. ? If the information in the original tables changes, then the query results will also change. SLIDE 39 ? ? ? ? This slide is the first of seven that show how to generate a simple report. (Student Learning Outcome #3) This slide shows the first three steps in the process of generating a report. In this case, contrary to what we did when creating tables and queries, we’re using the wizard to create the report. Later, we’ll open up a report in Design view to make some changes. SLIDE 40 SLIDE 41 ? This second slide shows the 4th, 5th and 6th steps in generating a report using the wizard. ? These steps allow you to dictate the layout of information. ? You can specify how to group items, sort the information, and how it will lay out on the printed page. Mod J Web-11
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
? SLIDE 42 ? This third slide showing the steps of creating a report has the final three steps in the process. These first two steps allow you to specify the style and heading of the report. SLIDE 43 ? This forth slide shows the screens for the first four steps of the process (Figure J.19 on page J.23). ? You need to choose the table or query you want to have in the report. ? Then you need to specify which fields of that table or query you want to use. ? This slide shows the screens for selecting the fields in the report and any grouping (Figure J.19 on page J.23). SLIDE 44 SLIDE 45 ? This slide shows the choice of to add a sort order and layout (Figure J.18 on page J.22). ? In this simple report, we’re simply accepting lots of default options. ? We’re allowing default ordering, sorting, layout and style. SLIDE 46 ? This slide shows the choice of default values and the window you get for entering a report style and report title (Figure J.20 on page J.24). ? The only part we have to type is the title that will appear on the top of the finished report. Mod J Web-12
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
? This slide shows the final report (the last screen in Figure J.21 on page J.24). SLIDE 47 ? This slide is the first of eleven that show that steps necessary to generate a report that’s a little more complex than the previous one. ? The report we’re embarking on now takes advantage of the grouping, sorting, and summary features that Access offers. ? The report we want to generate is the Supply Chain Management Report that you saw in Extended Learning Module C. ? The first thing we’re going to do is to create a query that has the fields we need for the report. ? ? ? This slide shows the first three steps in the report creation process. You’ll notice that the first three steps are pretty much identical to those we saw earlier when creating a simple report. We first have to use the Reports tab, call up the wizard, choose the query or table we want to use and the fields from that query or table. SLIDE 48 SLIDE 49 ? ? SLIDE 50 This slide shows the screens we see which take the first four steps of the process (Figure J.22 on page J.26). This is almost identical to what we saw with the simple report. SLIDE 51 ? ? ? ? ? This slide shows the 4th, 5th and 6th steps in the process. Access defaults to show grouping on the first field in the table/query. This is one of the advantages of first creating a query – it makes the grouping easier. If you play around with highlight other fields in the query, you’ll see the grouping change. Since we want top-level grouping, we get a choice of further sub-grouping. Mod J Web-13
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
? ? SLIDE 52 This slide shows two grouping windows (Figure J.22 on page J.26). These screens allow you to choose any top and subsequent grouping levels you might want. ? ? ? SLIDE 53 This slide shows the screen for sorting information (Figure J.22 on page J.26). You can sort on up to four fields. Each sort field can shows values in either ascending or descending order. SLIDE 54 ? ? ? This slide shows the 7th step in the report generation process. In this report we have an extra step because of the grouping. Specifying top-level grouping triggers an option to further group information that allows us to use an option for totaling. ? ? ? SLIDE 55 This slide shows the summary option screen (first screen in Figure J.23 on page J.27). A button on the sort screen opens a Summary window. This is where you specify totaling, averaging, and so on for the grouping you entered. SLIDE 56 ? ? ? ? This slide shows the last three steps in the report generation process. All we need now is to specify the look of the report. We need to choose the layout, orientation, and style of the report We also need to enter the title that will appear on the finished report. Mod J Web-14
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
SLIDE 57 ? This slide shows the options for formatting the report. ? The background screen lets you specify the layout and orientation. ? The foreground screen lets you specify the report title. ? ? ? ? SLIDE 58 This slide shows the final report as generated by the previous steps (Figure J.23 on page J.27). This is the report that we just generated. It has all the information there, but is not a good looking report. The next set of slides show you how to fix the little problems in the looks of the output. ? ? ? ? SLIDE 59 This slide gives general directions for adjusting the report to your liking. We generated this report using the wizard. But, to make our little adjustments, we need to revert to the Design view where we have much more control. There, we can remove entries and rearrange the spacing. SLIDE 60 ? This slide shows the screens in Figure J.25 on page J.28. ? We can view the results of the report or how the report is structured. ? We could, of course, have used the Design view to generate the report, but it’s sometimes easier to let the wizard take the initial stab at it and then fix what we don’t like. SLIDE 61 ? This slide shows what we adjusted and how the new version looks (Figure J.26 on page J.29). ? The Design view shows the report broken down into zones. ? We’ll start with the top zone (after the report header). ? We changed the position and contents of some of the column headers. Mod J Web-15
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
? SLIDE 62 ? ? Again, this slide shows what we adjusted for the grouping header and how the new version looks (Figure J.27 on page J.29). This adjustment process is not difficult, just a bit tedious and time consuming. However, it’s well worth it in the end since it results in a much more professional looking report. ? ? SLIDE 63 One more time, this slide shows what we adjusted for the detail section and how the new version looks (Figure J.28 on page J.30). Again, it’s a case of moving boxes, enlarging or shrinking them, and perhaps changing the contents a little. ? ? ? ? ? ? ? ? SLIDE 65 And again, this slide shows what we adjusted for the detail section and how the new version looks (Figure J.29 on page J.30). We choose to group by Concrete Type so it gets a header and a footer all its own. The footer contains the summary information that we specified while in the sort window. This adjustment is slightly different in that we deleted some of the words that Access put in for the group summary line. We also changed the color of the text and the total value. This slide shows the result of all the changes we made (Figure J.30 on page J.31). To finish off the report, we deleted the grand total (report total) since it made no sense in this context. This is automatically entered by Access, but we don’t have to show it. SLIDE 64 ? ? ? ? ? The last four tutorial slides show how to create an input form. You create an input form so that people who don’t have a knowledge of Access can enter information into tables. This would be necessary if your database were to be in production and had to be used by people on a regular basis. The first three steps look awfully like the ones we took to create the reports. Here again we’re using the wizard, so the process is very similar to what we’ve just seen. SLIDE 66 Mod J Web-16
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
? This slide shows the windows you get for choosing the tables or queries and fields that you want on the input form (the first screen in Figure J.31 on page J.32). ? These screens look very similar to those for report generation. ? In this case you use the Forms tab instead of the Reports tab, of course. ? Choosing the table or query and the fields is the same process we saw before. SLIDE 67 ? This slide shows the final input form. ? This is the second screen in Figure J.31 on page J.32 SLIDE 68
Back to Jump List
Mod J Web-17
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
ASSIGNMENTS & EXERCISES (p. J.34)
1. Enter new employee information. It’s likely that Solomon Enterprises would need to enter the information for a new employee. Create a new input form to enter Employee ID, Employee Last Name, Employee First Name, and Date of Hire. Design the form so that the information appears in tabular form, and has Sandstone background.
DISCUSSION
? Your students should be able to do this with no problem; refer them to the last section of the module on creating an input form.
2. What are the ingredients for premier marble concrete? Write a query to show how many units of each raw material are in concrete type 4. Print out the name of the concrete type, its ID, the name of the raw material (not its ID), and the number of units of each of the raw materials.
DISCUSSION:
? This is a conditional query that will use the Bill of Material and the Concrete Type relation.
? It will be similar to the example in the Creating an Advanced Query Using More than One Relation section of this chapter, except that it uses two tables instead of four.
3. Inventory report. Create a report that shows how many units Solomon Enterprises has of each of the raw materials. Don’t include water (hint: you want all Raw Material fields that do not equal water). Choose your own layout and page orientation.
DISCUSSION
? You only need one table (Raw Material) for this query. ? The Design view is shown below
Mod J Web-18
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
Extended Learning Module J (Office 2007 Version) - Implementing a Database with Microsoft Access
4. Sort query information. The datasheet view of a query allows you to sort the information in that query. Try this out with the Order relation. Download the Solomon Enterprises database from the Web site that supports this text www.mhhe.com/haag. (Select XLM/J. The name of the file is XLMJ_Solomon_Enterprises.mdb.) Click on the Queries tab and open the Order relation in datasheet view. Sort the table alphabetically on Employee Last Name. Place your cursor anywhere in the column you want to sort by (in this case Employee Last Name), and click on the sort-ascending button. It has an “A” above a “Z” with an arrow pointing downwards.
DISCUSSION ? This is a very convenient feature of Access.
? It’s easy to use and can be very helpful when analyzing information.
5. Filter query information. You can request Access to show you any other occurrences of a data item that is in the same column. Use the same file you needed for question #6 above. You can download it from the Web site for this text www.mhhe.com/haag. (Select XLM/J. The name of the file is XLMJ_Solomon_Enterprises.mdb.) Filter the information so that only those records in which the truck is a Ford. To do this click in the Truck Type column on any one of the occurrences of Ford. Then, click on the Filter by Selection button (that’s the button in the button bar with the funnel and a lightning strike). You will instantly see only the three records where the truck is a Ford. To return the data to its previous state, click on Remove Filter button (that’s the button that has the funnel without any other symbol). If you click on this button again, it will reapply the filter.
DISCUSSION ? Like the Sort button in the button bar, the Filter button is very useful. ? It allows you to include or exclude information as necessary.
Back to Jump List
Mod J Web-19
? 2013 by McGraw-Hill Education. This is proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any
manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.
信息时代的管理系统 - 英文原版教学手册 - Module - J - Web - 图文
