Calculating by using queries 21? Updating records by using queries As you use a database and as it grows, you might discover that errors creep in or that some information becomes out of date. You can tediously scroll through the records looking for those that need to be changed, but it is more efficient to use the tools and techniques pro- vided by Access for that purpose. If you want to find or replace multiple instances of the same word or phrase, you can use the Find and Replace commands in the Find group on the Home tab. These commands work much like the same commands in Microsoft Word or Microsoft Excel. If you want to manipulate information stored in the database only under certain circum- stances, you need the power of an action query. An action query finds records that match the selection criteria and performs an action on them. For example, you can increase the price of all products in one category by a certain percentage, or remove all the items be- longing to a specific product line. This type of data manipulation is easy to do with an action query. Not only does using a query save time, but it helps to avoid errors. Four types of actions are available: ' Append Adds records from one or more tables to the end of one or more other tables. ' Delete Deletes records from one or more tables. ' Make-table Creates a new table from all or part of the data in one or more tables. ' Update Makes changes to records in one or more tables. Running an update query makes irreversible changes to the underlying table, so you should always create a backup copy of the table before running this type of query. You can quickly create a copy of a table by displaying the Tables list in the Navigation pane, clicking the table you want to copy, pressing Ctrl+C, and then pressing Ctrl+V to paste a copy. In the Paste Table As dialog box, enter a name for the new table, and then click OK. The backup table then becomes part of the database. You can delete it when you are sure that the update query produced the results you want. TIP In addition to these queries, you can create SQL queries, including union, pass through, and data denition queries. SQL queries are beyond the scope of this book. 218 Chapter 7 Create queries You can't create an action query directly; you must first create a select query and then convert it. With an existing select query open in the Query Designer, click the appropriate button in the Query Type group on the Design tool tab. (You can also right-click the query in the Query Designer, click Query Type, and then click the type of query you want.) In this exercise, you'll create an update query to increase the price of selected items by 10 percent. _ involves working with Microsoft cess 0 ca ' ' ' * Delivera e. ne MS Access le only. Ensure that ly one database which will contain all re database objects. * For Each database object you create (e. g., table, query, or report), right click on its icon in the Navigation pane, click 'View properties' [or 'Object Properties' or 'Table Properties'], and then in the 'Description' Window, enter a short description of about 40 words regarding how you created that database object. Setting: This is a ctitious body of students set in 2015. These students are set to go on trips (vacations) to various destinations. As can be seen from the two tables in the Microsoft Excel document, each trip has its destination, departure date, return date, and cost, and rrthermre, each student has made a deposit towards the nal cost of their trip. Carry out the following 5 tasks using the Microsoft Access application: 1. Import the data from the excel spreadsheet 'Travel Information.xlsx' (two sheets); Create two Tables: E Travel Information\\\\ Field Name Data Type 3 Trip 10 Short Text __ Destination Short Text Cost of Trip Currency Departure Date Date/Time Return Date Date/Time Contact Last Name Short Text Contact First Name Short Text Phone\" Short Text g Students \\' Field Name Data Type 1' Student ID\" Short Text First Name Short Text _Last Name Short Text Trip ID Short Text Deposit Paid Currency \f\f3. Create a query that shows the following student travel statistics: Total Number of Trips; Total Cost of all Student Trips; Total Amount of Deposits Paid; Total Outstanding Balance; Average Trip Cost. 4. Create a report based on the query of outstanding balance of the students greater than $2000. [Show only the below 6 fields]: Students Outstanding Balance greater Wednesday, December 12, 20 15 than $2000 1021:36 AM Trip ID First Name Lut Name Deposit Paid Castof Tap Outstanding Balance T-02 Flacc $1,000 $5,000 4,000.0 T-02 Hardy $1,500 $5,000 $3,500.0 T-03 Berguin $2,500 $5,000 $2,500.0 7-03 52,500 $5,000 $2,500.00 Hanby $2,000 $4,500 $2,500.00 Youson 53,000 $6,000 $3,000.00 7-07 Carlto Ramos $2,700 $5,400 $3,700.00 T-05 Flite oft $1,600 $4,300 $2,700.00 T-10 52,500 $7,250 $4,750.00 $4,300 57 ,250 $2,750.00 $5,000 $7 ,250 3,250.00 83,000 $5,600 13,600.00 $2,800 $5,600 2,800.00 BEEEEEEE $5,300 57,580 $2,080.00 52,000 $7,580 5,580.00 Bod ward $2,800 $4,950 52,150.0 52,500 $5,000 3,500.0 7 - 15 Campbell $1,000 $4,780 35,750.00 7 -15 $2,500 $4,780 $2,250.0 $107,820 $56,920 Page ! 5. Using Queries for Calculations. Perform a calculation of your choice and create a new field in which to store it. [This portion of the assignment will be unique to you] See the section, "Calculating by using queries" in the Microsoft Access Reference book previously referred to (see pages 212-217 of the file, 'access2013.pdf' available i es section of canvas). Use an expression [of your choice] in a query to compute the desired information from the table(s) you have imported for this assignment. Use the MS Access 'Expression Builder' function which has Expression Elements, Expression Categories and ExpressionCalculating by using queries 213 6 In the Field row, right-click in the first blank column, and then click Build to open the Expression Builder dialog box. Expression Builder X Enter an Expression to define the calculated query field (Examples of expressions include [field 1] + [field2] and [field 1] . Functions Order ID A MyGardenCompany07.acc ProductName UnitPrice La Constants Quantity Operators Discount Common Expressions In the Expression Builder dialog box, the Order Details Extended query is selected in the Expression Elements box, and the Expression Categories box displays the fields from the query. Here is the expression you are going to build in the expression box: CCur([Order Details]![UnitPrice]*[Order Details]![Quantity]* (1-[Order Details]![Discount])) The CCur function converts the results of the math inside its parentheses to currency format. TIP If you wanted to enter this expression directly into the field, you could simplify it to this: CCur([Order Details]![UnitPrice]*[Quantity]*(1-[Discount])) The [Order Details]! part is required only for the UnitPrice field, which appears in both tables. It tells the query which table to use. 7 In the Expression Elements list, double-click Functions, and then click Built-In Functions. 214 Chapter 7 Create queries 8 In the Expression Categories list, click Conversion. Then in the Expression Values list, double-click CCur. Expression Builder X Enter an Expression to define the calculated query field: (Examples of expressions include [field 1] + [field2] and [field 1] ) OK Cancel13 In the expression box, click to select it. In the Expression Elements list, in the Tables area of the GardenCompany07.accdb list, click Order Details. Then in the Expression Categories list, double-click Quantity. So far, we have entered an expression that calculates the total cost by multiplying the price of an item by the quantity ordered. However, suppose the sale price is dis- counted due to quantity or another factor. The discount, which is stored in the Order Details table, is expressed as the percentage to deduct. But it is easier to compute the percentage to be paid than it is to compute the discount and subtract it from the total cost. TIP The Discount field values are displayed in the Order Details table as percentages, but they are stored in the database as decimal numbers between 0 and 1. (For ex- ample, a discount displayed as 10% is stored as 0.1). So if the discount is 10 percent, the percentage to be paid is 1-Discount, or 0.9. In other words, the expression will multiply the unit price by the quantity and then multiply that result by 0.9. 14 With the cursor to the left of the closing parenthesis in the expression box, enter *(1-. In the Expression Categories list, double-click Discount. Then enter ) (closing parenthesis). Expression Builder X Enter an Expression to define the calculated query field (Examples of expressions include [field 1] + [field2] and [field 1] (4 MyGardenCompany07. ProductID -Tables UnitPrice Quantity Categories Discount Customers Employees Order Details Orders The entire expression now appears in the expression box. 216 Chapter 7 Create queries TIP If the entire expression isn't visible in the expression box, you can widen the Expression Builder dialog box by dragging its left or right border. 15 In the Expression Builder dialog box, click OK to insert the expression into the design grid. 16 Press Enter to complete the entry of the expression. Then widen the column so that the entire expression is visible. Access has assigned the label Expri to the field. (This label is known as the field alias.) Let's change the label to something more meaningful. 17 In the design grid, double-click Expri, and then enter ExtendedPrice. 18 Run the query.1. Import the data from the excel spreadsheet 'Travel Information.xIsx' (two sheets); Create two Tables: Travel Information Field Name Data Type a Trip ID Short Text Destination Short Text Cost of Trip Currency Departure Date Date/Time Return Date Date/Time Contact Last Name Short Text Contact First Name Short Text Phone# Short Text Students Field Name Data Type Student ID# Short Text First Name Short Text Last Name Short Text Trip ID Short Text Deposit Paid Currency Date Paid Date/Time 2. [In regard to the upcoming trips] Create a query that shows only the students' outstanding balance(s) greater than $2000. Students Outstanding Balance greater than 2000 Trip ID * First Name . Last Name . Deposit Paid . Cost of Trip . Outstanding T-02 Florence Zimmerman $1,000 $5,000 $4,000.00 T-02 Jamal Hardy $1,500 $5,000 $3,500.00 T-02 Marianne Berguin $2,500 $5,000 $2,500.00 T-02 Moniquin Lett $2,500 $5,000 $2,500.00 T-03 Donald Hanby $2,000 $4,500 $2,500.00 T-06 Marilyn Youson $3,000 $6,000 $3,000.00 T-07 Carlito Ramos $2,700 $5,400 $2,700.00 T-08 Prince Flitcroft $1,600 $4,300 $2,700.00 T-10 Hendrick Themis $2,500 $7,250 $4,750.00 T-10 Kirsten Riccardi $4,500 $7,250 $2,750.00 T-10 Lloyd Murphy $5,000 $7,250 $2,250.00 T-11 Andrew Lee $3,000 $5,600 $2,600.00 T-11 Caitlyn Hardy $2,800 $5,600 $2,800.00 T- 12 Christopher Danko $5,500 $7,580 $2,080.00 T-12 Zack Rowe $2,000 $7,580 $5,580.00 T-13 Shawn Boulward $2,800 $4,950 $2,150.00 T-14 Kevin Leopoldo $2,500 $5,000 $2,500.00 T-15 Darxia Campbell $1,000 $4,780 $3,780.00 T-15 Stephan Elraheb $2,500 $4,780 $2,280.00 Total $50,900 $107,820 $56,920.00offTy une DCTOW o TICTUST. Students Outstanding Balance greater Wednesday, December 12, 20 15 than $2000 1021:35 AM Trip ID First Name Lat Name Deposit Paid Cat of Tap Outstanding Balance T-02 Flarence $1,000 $5,000 $4,000.00 T-02 Jamal Hardy $1,500 $5,000 $3,500.00 T-02 Marianne Berguin $2,500 $5,000 $2,300.00 T-03 Moniquin Let 32,300 $5,000 $2,500.00 T-03 Donald Hamby $3,000 $4,500 $2,500.60 T-06 Youton $3,000 $8,000 $3,000.00 T-07 Carlto Ramos 52,700 35,400 $2,700.00 T-06 Flite of $1,600 $4,300 $2,700.00 T-10 Themsis $2,500 57 250 $4,750.00 T-10 $4,300 $3,750.00 10 Word $5,000 $3,250.00 7-11 Andrew $3,000 35,600 $2,600.00 T-11 Hardy $3,800 $5,600 $3,800.00 T-12 Chris topher Danko $5,500 $2,080.00 T-12 Zack Rome $2,000 57 ,380 $5,580.00 T-13 Bodward $2,800 $4,950 $2,150.00 Leopoldo 53,300 $5,000 $2,500.00 T-15 Campbex $1,000 34, 780 $3,780.00 T-15 Stephan Elesheb $2,500 $4,730 $2,250.00 $107,820 $ 56,9 20 5. Using Queries for Calculations. Perform a calculation of your choice and create a new field in which to store it. [This portion of the assignment will be unique to you]. See the section, "Calculating by using queries" in the Microsoft Access Reference book previously referred to (see pages 212-217 of the file, 'access2013.pdf' available in the 'Files' section of canvas) Use an expression [of your choice] in a query to compute the desired information from the table(s) you have imported for this assignment. Use the MS Access 'Expression Builder' function which has Expression Elements, Expression Categories and Expression Values. Clearly state what information you intended to derive, the MS Access expression(s) you needed, and how you went about performing the calculation(s). Save the query as firstnamelastnamepersonalquery5' and save it in the same database. Running the query should create a new field, using one or more of the existing fields in your calculation (for example, an employee database can be used to calculate yearly bonus or number of years in the company, based on data already in the database).8 In the Expression Categories list, click Conversion. Then in the Expression Values list, double-click CCur. Expression Builder X Enter an Expression to define the calculated query field: (Examples of expressions include [field 1] + [field2] and [field1] ) OK Cancel Help Asc - Functions Arrays AsCW Built-In Functions Conversion Book Database CByte MyGardenCompany Date/Time cCur Web Services Domain Aggregate CDate ).[A] MyGardenCompany07. Error Handling CDbl La Constants Financial Chr General Chr$ - Operators Inspection ChrW Math ChrW$ CCur(expression) Coerces an expression to a Currency. 7 The > inside the parentheses represents the expressions that will eventually result in the number Access should convert to currency format. 9 In the expression box, click > to select it, so that the next thing you enter will replace it. (The next expression element is the UnitPrice field from the Order Details table.) 10 In the Expression Elements list, click the minus icon to the left of Functions to collapse that element. Then click the plus icon to the left of GardenCompany07. accdb (or your version of that file-ours is MyGardenCompany07), double-click Tables, and click Order Details. 11 In the Expression Categories list, double-click UnitPrice to replace the > placeholder with the table/field information. Now let's multiply the amount in the UnitPrice field by the amount in the Quantity field. 12 With the cursor after [UnitPrice] in the expression box, click Operators in the Expression Elements list; click Arithmetic in the Expression Categories list; and double-click *(multiply) in the Expression Values list. Calculating by using queries 215 13 In the expression box, click to select it. In the Expression Elements list, in the Tables area of the GardenCompany07.accdb list, click Order Details. Then in the Expression Categories list, double-click Quantity. So far, we have entered an expression that calculates the total cost by multiplying the price of an item by the quantity ordered. However, suppose the sale price is dis- counted due to quantity or another factor. The discount, which is stored in the Order Details table, is expressed as the percentage to deduct. But it is easier to compute the percentage to be paid than it is to compute the discount and subtract it from the total cost. TIP The Discount field values are displayed in the Order Details table as percentages, but they are stored in the database as decimal numbers between 0 and 1. (For ex- ample, a discount displayed as 10% is stored as 0.1). So if the discount is 10 percent,The entire expression now appears in the expression box. 215 Chapter 7 Create queries 15 16 17 18 19 TIP If the entire expression isn't visible in the expression box, you can widen the Expression Builder dialog box by dragging its left or right border. In the Expression Builder dialog box, click OK to insert the expression into the design grid. Press Enter to complete the entry of the expression. Then widen the column so that the entire expression is visible. Access has assigned the label Exprl to the field. (This label is known as the eld alias.) Let's change the label to something more meaningful. In the design grid, double-click Exprl, and then enter ExtendedPrice. Run the query. nmimnpostbln 55am \"l $50.00 \"939.90%"5 \"F .. $37.5 . .. . . 0.' ears . unauj Fortune nhododendrc' 94.00 0.1} $13.10 : 11001 Golden Larch $27.00 0' $27.00 . 1.10811Lawn cert _ 535.00 0 1i $15.50 _ um: an box $14.75 11093? compost am 550 an 7 11083, GrowGood potting sol 110.3} leknoot $18-00 11053' Grass rake . $11.35 110843 Gooseberrles _ $7.50 11034 Amhrnsll SI.\" 11on Blackberries $4.50 11035. Cam: and -- In- m 9.50 Mmmf-Im The orders are sorted by the Order\") field, and the extended price is calculated in the last eld. 35'. 3 uauupuupuuuwup L919 33%? E b! H \"l $17.00 _ In the few records with discounts, verify that the query calculates the extended price correctly. 0 CLEAN UP Close the query, saving it when prompted. Keep the GardenCompanyO? database open for use in later exercises. Calculating by using queries 217 Updating records by using queries As you use a database and as it grows, you might discover that errors creep in or that some information becomes out of date. You can tediously scroll through the records looking for those that need to be changed. but it is more efficient to use the tools and techniques pro- vided by Access for that purpose. 1 Open the Query Designer, and add the Order Details and Products tables to the query. 2 Drag the following five fields from their field lists to consecutive columns in the design grid. From this field list Drag this field Order Details OrderID Products ProductName Order Details UnitPrice Order Details Quantity Order Details Discount 212 Chapter 7 Create queries Query1 X Order Details Products OrderID ProductID ProductID ProductName UnitPrice LatinName Quantity SupplierID Discount CategoryID Field: OrderID ProductName UnitPrice Quantity Discount v Table: Order Details Products Order Details Order Details Order Details Sort: Show Criteria or: The results datasheet will display the extracted information in the order of the fields in the design grid. 3 Run the query. Query1 OrderID Product Name . UnitPrice . Quantity . Discount - 11091 Autumn Crocus $18.75 11079 Compost bin $58.00 11083 Compost bin $58.0 7 11138 Compost bin $58.00 11152 Compost bin $58.00 11085 Cactus sand potting m $4.50 11093 Cactus sand potting m $4.50 11121 Cactus sand potting m $4.50 11132 Cactus sand potting m $4.50 11148 Cactus sand potting m $4.50 11114 Weeping Forsythia $18.00 11147 Weeping Forsythia $18.00 UNWHWHH 11082 Bat box $14.75 11086 Bat box $14.75 11159 Bat box $14.75 Record: 14 4 1 of 215 > > > No Filter Search The results show that the query is working correctly. 4 Save the query with the name Order Details Extended. 5 Switch to Design view. Then in the OrderID column, display the Sort list, and click Ascending Now in a new field in the design grid, let's use the Expression Builder to insert an expression that computes the extended price by multiplying the unit price by the quantity sold, minus any discount.You can't create an action query directly; you must first create a select query and then convert it. With an existing select query open in the Query Designer, click the appropriate button in the Query Type group on the Design tool tab. (You can also right-click the query in the Query Designer, click Query Type, and then click the type of query you want.) In this exercise, you'll create an update query to increase the price of selected items by 10 percent. SET UP You need the GardenCompany07 database you worked with in the preceding exercise to complete this exercise. If necessary, open the database. Then follow the steps. 1 In the Navigation pane, copy and paste the Categories table, naming the new object Copy Of Categories, so that you have a backup in the event that your query produces unexpected results. 2 On the Create tab, in the Queries group, click the Query Wizard button. Then with Simple Query Wizard selected in the New Query dialog box, click OK. 3 Display Table: Categories in the Tables/Queries list, and in the Available Fields list, double-click CategoryName to move it to the Selected Fields list. 4 Display Table: Products in the Tables/Queries list, and in the Available Fields list, double-click ProductName and UnitPrice to move them to the Selected Fields list. 5 Click Finish to create the query by using the default detail setting and title and to run the query. Categories Query Category Name . Product Name . Unit Price . Bulbs Magic Lily $44.00 Bulbs Autumn Crocus $20.63 Bulbs Anemone $30.80 Bulbs Lily-of-the-Field $41.80 Bulbs Siberian Iris $14.2 Bulbs Daffodil $14.25 Bulbs Peony $21.9 Bulbs Lilies $11.55 Bulbs Begonias $20.85 Bulbs Bulb planter $7.65 Cacti Prickly Pear $3.30 Ground covers Crown Vetch $12.9 Ground covers English Ivy $5.95 Ground covers European Ginger $6.25 Ground covers St. John's Wort $9.75 Record: 14 4 1 of 189 , Filter Search Only the Category Name, Product Name, and Unit Price fields are displayed. Updating records by using queries 219 The current query results include the products in all categories. We want to raise the prices of the products in only the Bulbs and Cacti categories, so let's change the query to select only those categories. 6 Switch to Design view. Categories Query Categories Products CategoryID ProductID CategoryName ProductName Description LatinName SupplierID CategoryID Fiald Init Dries