Action Query Exercises

1. Action Query Exercises Chapter 9

2. Use Northwinds.mdb

3. 1. Update Query • Products and Categories Tables • Look for products in the category “condiments” • Increase the present price by 6% PgP MIS 342 Exercises

4. 1. Update Query • Good practice to do a select query first and make sure you have selected the correct records to update. • Should have 12 records PgP MIS 342 Exercises

5. Update Query-Design View PgP MIS 342 Exercises

6. Expression Result "Salesperson" Changes value to Salesperson #8/10/96# Changes date values to 10-Aug-96 Yes Changes No values in a Yes/No field to Yes "PN"&[PartNumber] Adds PN to the beginning of each specified part number [UnitPrice]*[Quantity] Calculates the product of UnitPrice and Quantity 1. Other Criteria-from Help PgP MIS 342 Exercises

7. Expression Result [Freight]*1.5 Increases freight charges by 50 percent DSum("[Quantity]*[UnitPrice]","Order Details","[ProductID]=" & [ProductID]) Where the Product IDs in the current table match the Product IDs in the Order Details table, updates sales totals based on the product of Quantity and UnitPrice Right([ShipPostalCode],5) Truncates the leftmost characters in a field, leaving the five rightmost characters 1. Other Criteria-from Help PgP MIS 342 Exercises

8. 2. Delete Query • Order Details Table • Criteria- discount of 25% • Delete any order with this discount rate • Note-watch out for the discount field format! PgP MIS 342 Exercises

9. 3. Make Table Query • Orders Table • Criteria- orders that have not shipped • Make a new table with only these orders in it • Include all fields in this new table • Name table ‘tblUnshippedOrdersAsOfToday’ PgP MIS 342 Exercises