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 to look for partial values in fields.➠ Know how to make computations within queries on both numeric and text fields.10Database Queries—Textbook Database380McLepro10v1.qxd 2/17/06 04:15 PM Page 380This 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 inlowercase letters. The database for this project, Textbook, concerns classes and the books used in thoseclasses. The database also contains records of students who have copies of books to sell.Project 11 is a companion project; it covers the same learning objectives but uses a differentdatabase. Projects 10 and 11 both cover query concepts so that instructors have the option to fur-ther enforce those concepts with two projects or to assign different projects to students in dif-ferent semesters.An Access database is a single file. Although a database may have many tables, they areall contained in a single database file, along with any queries, reports, or other components ofthe database. You should create a copy of the data file with your database on a regular basis.Within Access, you can use the “Edit” command to cut and paste objects. Objectsinclude tables, queries, and similar components of the database. You cannot copy the data-base itself from within Access. To copy the entire database, such as for a backup copy, youmust copy 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 cangrow quickly. Users like to use features such as the “Undo” command that can easily reversemistakes. Such features come at a price—they require a lot of disk space to guide the rever-sal of actions taken. To keep your database file a reasonable size, you should compact it on aregular basis, 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 thatyou may have performed while using the database. You can set Access to automatically compact thedatabase when you exit the program by selecting “Tools,” followed by “Options,” “General,” and “Compact onClose.” However, many universities limit the ability of students to change computer settings, so you may have tomanually compact your database each time you use Access.EXAMPLEThis example will generate a number of queries from the Textbook database. The Textbookdatabase will be provided by your instructor or downloaded from the Prentice Hall Web site.Managers typically use queries to look for specific records; the query finds records that havecertain field values. Other queries can be used to compute new values based on the values ofother 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.381IntroductionMcLepro10v1.qxd 2/17/06 04:15 PM Page 381382 ••• PART 4 PROJECTSFigure P10.1 Tablesand Fields in TextbookDatabaseTextbook DatabaseThe Textbook database consists of four tables that are related to each other by common val-ues. The tables and their data fields are shown in Figure P10.1. The STUDENT table containsfour fields: FirstName, LastName, BookNumb, and CopiesToSell. The key fields of the tablesare shown in bold. It takes the combination of values from the FirstName, LastName, andBookNumb fields to make a key (a unique value) for the STUDENT table. The ClassNumbfield is the only field needed to be the key of the CLASS table.The fields from different tables can be combined when they share a common value. Forexample, the BookNumb field is in both the STUDENT table and the BOOK table. If the valueof BookNumb is 409 in the BOOK table and in the STUDENT table, then the name of the stu-dent from the STUDENT table can be associated with the book title of the BOOK record.From Figure P10.2 you can see that Amy Abner and Brian Janski both have book number 409to sell. Book number 409 (see Figure P10.3) has a title of “Management InformationSystems” and was authored by McLeod and Schell.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 Textbook database. Noticefrom Figure P10.4 that the “Queries” tab is chosen.Creating a Query with ConstraintsRecords in the BOOK table are shown in Figure P10.5. If a query were constructed withoutconstraints, all of the records in the table would be displayed in the query result. It is moreFigure P10.2 Fieldsand Values in the STUDENT TableMcLepro10v1.qxd 2/17/06 04:15 PM Page 382PROJECT 10 DATABASE QUERIES—TEXTBOOK DATABASE ••• 383Figure P10.3 Fieldsand Values in theBOOK Tableproductive to restrict the records displayed to those that fit some decision-making criteria.Let’s look at books with a price greater than $100.Double-click the phrase “Create query in Design view” shown in Figure P10.4 andFigure P10.6 appears. For this query, choose the BOOK table from the list by highlighting theBOOK choice and clicking the “Add” button. Then close the screen that shows the tables byclicking the “Close” button.Figure P10.4Textbook DatabaseScreenFigure P10.5 Fieldsand Values in theBOOK TableMcLepro10v1.qxd 2/17/06 04:15 PM Page 383384 ••• PART 4