Cindi asks you to update some information in the vendor database and
[ad_1]
Cindi asks you to update some information in the Vendor database and also to retrieve specific information from the database. Complete the following: 1. Open the Vendor database you created and worked with in Tutorials 1 and 2, and then click the Enable Content button next to the security warning, if necessary. 2. Open the Supplier table in Datasheet view, and then change the following field values for the record with the Supplier ID GRE364: Address to 1550 W Main St, Contact Phone to 401-625-2745, Contact First Name to Andrew, and Contact Last Name to Kline. Close the table. 3. Create a query based on the Supplier table. Include the following fields in the query, in the order shown: Company, Category, Contact First, Contact Last, Phone, and Initial Contact. Sort the query in ascending order based on the Category field values. Save the query as Contact List, and then run the query. 4. Use the Contact List query datasheet to update the Supplier table by changing the Phone field value for Aresco Surgical, Inc. to 203-335-0054. 5. Change the size of the font in the Contact List query datasheet to 12 points. Resize columns, as necessary, so that all field values and column headings are visible. 6. Change the alternate row color in the Contact List query datasheet to the Theme Color named Green, Accent 6, Lighter 60%, and then save and close the query. 7. Create a query based on the Supplier and Product tables. Select the Company, Category, and State fields from the Supplier table, and the Product Name, Price, Units, and Weight fields from the Product table. Sort the query results in descending order based on price. Select only those records with a State field value of CT, but do not display the State field values in the query results. Save the query as CT Suppliers, run the query, and then close it. 8. Create a query that lists all products that cost more than $200 and are sterile. Display the following fields from the Product table in the query results: Product ID, Product Name, Price, Units, and Temp Control. (Hint: The Sterile field is a Yes/No field that should not appear in the query results.) Save the query as High Price And Sterile, run the query, and then close it. 9. Create a query that lists information about suppliers who sell equipment or products that require temperature control. Include the Company, Category, Contact First, and Contact Last fields from the Supplier table; and the Product Name, Price, Temp Control, and Sterile fields from the Product table. Save the query as Equipment Or Temp Control, run the query, and then close it. 10. Create a query that lists only those products that cost $1000 or more, along with a 5% discount amount based on the price of the product. Include the Company field from the Supplier table and the following fields from the Product table in the query: Product ID, Product Name, and Price. Save the query as High Price With Discount. Display the discount in a calculated field named Discount Amt that determines a 5% discount based on the Price field values. Set the Caption property Discount Amt for the calculated field. Display the query results in descending order by Price. Save and run the query. 11. Modify the format of the Discount Amt field in the High Price With Discount query so that it uses the Standard format and two decimal places. Run the query, resize all columns in the datasheet to their best fit, and then save and close the query. 12. Create a query that calculates the lowest, highest, and average prices for all products using the field names Lowest Price, Highest Price, and Average Price, respectively. Set the Caption property for each field to include a space between the two words in the field name. Run the query, resize all columns in the datasheet to their best fit, save the query as Price Statistics, and then close it. 13. In the Navigation Pane, copy the Price Statistics query, and then rename the copied query as Price Statistics By Supplier. 14. Modify the Price Statistics By Supplier query so that the records are grouped by the Company field in the Supplier table. The Company field should appear first in the query datasheet. Save and run the query, and then close it. 15. Compact and repair the Vendor database, and then close it.
