Purpose: To practice making queries in an Access database. Instructions - Answer all questions in the Kelsey Vets database - Rename the database as Lastname_Firstname_Kelsey_Vets.accdb, for example Miller_Rob_Kelsey_Vets.accdb, but use your own name! - Open the database and familiarize yourself with the tables and the relationships. - Note that not all queries will be graded, however, a solution will be posted for all. - Submit to Brightspace when done. Questions 1. Find pets who have a PetID with 002 in the 3td to 5th characters. Show pets who have visited Kelsey Vets at least once. Show the Customer Number, Customer Name, Visit Number, Pet ID and Visit Date. Save as qryPetIDWith002. 2. Kelsey Vets would like to set up a monthly billing system. a. For customers who have outstanding amounts owing, display the results in a single string as "Customer Johnathan Adams owes $16.67 per month." In addition, show the province the customer is from. Only show customers who have an outstanding amount owing. Sort ascending by amount owing. Save as qryMonthlyBillAmounts. b. Kelsey Vets would also like to see how much money will be coming in from each province. Display the results as "For the province of Manitoba the total monthly billing is $215.08. Only show provinces that have billing values >0. Save as qryMonthlyBillByProvince. 3. Determine how many pets have had a visit with a Total amount over $200 at some point. Make sure that a given pet is not counted twice. Save as qryCountOver 200. 4. Create a query that will display for each Customer the number of animals of each type that have had a visit to Kelsey Vets. Display each Type of Animal across the columns, the Customer Name and Customer Number in the rows. The intersection of row and column will display the count of each animal type. Save as qryAnimalCountByTypeAndCustomer. 5. The tblvisits table stores the Total Amount of the bill as a field. In general, this is not a good idea since the underlying data may change. The Total Amount equals the sum of Treatment Price + Medication Price for a particular visit. Create a query to show the Total Amount values stored that are not equal to the sum of Treatment Price and Medication price. Show the Customer Name, Visit Number, and Total Amount. Create a calculated field that will show the sum of Treatment Price and Medication for a particular visit. Add appropriate criteria to this field. Save as qryTotalAmountincorrect. 6. The supplier for the medication "Aspertane" has upped the price for the drugs. Create a query that will update the price of all versions of Apertane by $0.50 (50 cents). Save as qryUpdateAspertane. 7. Kelsey Vets would like to stop ordering drugs that are never prescribed. Write a query that will show all drugs that have not been associated with a particular visit. Show the Medication Code, Medication Name and Price. Save as qryUnusedMedications. 8. Kelsey Vets would like to categorize pets by weight as follows: Write a query that shows Customer Number, Pet Name, Breed, Type of Animal, and Weight. Add a field that will show the Category based on the pets weight. Sort by Type of Animal and then Customer Number. Save as qryPetWeightGroup. 9. Find a list of pets who have visited Kelsey Vets at least twice. The query shall show Pet ID and Pet Name. Save as qryPetsRevisited. tblAnimalType tblCustomer tbIMedications tbIPets