ProjectLearning Objectives➠ Know how to create queries using one or multiple tables.➠ Understand how to limit query results with single and multiple constraints.➠ Understand how a query can request constraint values from the query user.➠ Know how to use queries that look for partial values in fields.➠ Know how to make computations within queries on both numeric and text fields.11Database Queries—ClassProjects Database396McLepro11v1.qxd 2/17/06 06:36 PM Page 396This project uses Microsoft Access to create queries for an existing database. The initial database can be down-loaded from the Prentice Hall Web site for the text at www.prenhall.com/mcleod—“mcleod” must be in low-ercase letters. The database for this project, ClassProjects, concerns classes and the projects used inthose classes.Project 10 is a companion project; it covers the same learning objectives but uses a differentdatabase example. Projects 10 and 11 both cover query concepts so that instructors have theoption to further enforce those concepts with two projects or to assign different projects to stu-dents in different semesters.An Access database is a single file. Although a database may have many tables, theyare all contained in a single database file, along with any queries, reports, or other compo-nents of the database. You should create a copy of the data file with your database on a reg-ular basis.Within Access, you can use the “Edit” command to cut and paste objects. Objectsinclude tables, queries, and similar database components. You cannot copy the databaseitself from within Access. To copy the entire database, such as for a backup copy, you mustcopy the database file using Windows Explorer or clicking on the “My Computer” icon.One caution about using database software is that the size of the database file can growquickly. Users like to use features such as the “Undo” command that can easily reverse mis-takes. Such features come at a price—they require a lot of disk space to guide the reversal ofactions taken. To keep your database file at a reasonable size, you should compact it on a regularbasis, such as every time you exit Access.From within Access, the “Tools,” “Database Utilities,” and “Compact and Repair Database”sequence of commands will compact your database to remove all of the deletions and missteps that youmay have performed while using the database. You can set Access to automatically compact the databasewhen you exit the program by selecting “Tools,” followed by “Options,” “General,” and “Compact on Close.”However, many universities limit the ability of students to change computer settings, so you may have to manuallycompact your database each time you use Access.EXAMPLEThis example will generate a number of queries from the ClassProjects database. TheClassProjects database will be provided by your instructor or downloaded from the PrenticeHall Web site. Managers typically use queries to look for specific records; the query findsrecords that have certain field values. Other queries can be used to compute new values basedon the values of other fields in the query.It is important for decision makers to be able to generate their own queries. First, deci-sion makers who can create queries gain immediate access to the power of a database. Second,decision makers may not know exactly which records in the database are needed until a queryis generated, its results are seen, and more queries are created in an iterative process until theresults desired are ultimately generated. Third, decision makers cannot have an intuitiveunderstanding of the database unless they have a hands-on understanding of the data valuesand relationships in the database.Information is an essential decision-making tool. The understanding of the databasegained by writing queries trains a decision maker to use that tool better.397IntroductionMcLepro11v1.qxd 2/17/06 06:36 PM Page 397398 ••• PART 4 PROJECTSFigure P11.1 Tablesand Fields in theClassProjects DatabaseClassProjects DatabaseThe ClassProjects database consists of three tables that relate to each other by common val-ues. The tables and their data fields are shown in Figure P11.1. The COURSE table containsthree fields: Code, Description, and Abbreviation. The key fields of the tables are shown inbold font. It takes the combination of values from the Code and Number fields to make a key(a unique value) for the PROJECT table. The Abbreviation field is the only field needed to bethe key of the DEPARTMENT table.Fields from different tables can be combined when a common value exists between thetables. For example, the Abbreviation field is in both the DEPARTMENT table and theCOURSE table. If the value in the Abbreviation field is “INT” in both the DEPARTMENTand COURSE tables, then the name of the department from the DEPARTMENT table can beassociated with the description of the course in the COURSE record. From Figure P11.2, youcan see that the courses “Cultural Diversity,” “Spanish for Business,” and “French forBusiness” each have a value of “INT” for the Abbreviation field. The “INT” value in theAbbreviation field of the DEPARTMENT table (see Figure P11.3) indicates that the threecourses are in the International Business department.For the projects and assignments in this text, you will use an existing database; you willnot be required to create a database or tables within a database.Begin this project by opening Access and then opening the ClassProjects database.Notice from Figure P11.4 that the “Queries” tab is chosen.Figure P11.2 Fieldsand Values in theCOURSE TableFigure P11.3 FieldsAnd Values in theDEPARTMENT TableMcLepro11v1.qxd 2/17/06 06:36 PM Page 398PROJECT 11 DATABASE QUERIES—CLASSPROJECTS DATABASE ••• 399Creating a Query with ConstraintsRecords in the PROJECT table are shown in Figure P11.5. If a query were constructed with-out constraints, all of the records in the table would be displayed in the query result. It is moreproductive to restrict the records displayed to those that fit some decision-making criteria.Let’s look at projects that earn more than 35 points.Double-click the phrase “Create query in Design view,” as shown in Figure P11.4, andFigure P11.6 appears. For this query, choose the PROJECT table from the list by highlightingthe PROJECT choice and clicking the “Add” button. Close the screen that shows the tables byclicking the