Description
Create a new database named YourLastnameFirstnameReview. Import data from Excel Spreadsheet and the text file to create tables following the instructions below Save each query with the name of the step for which it was created. For example, the query created in step 15 would be called Q15. For each query include all fields unless otherwise specified or if that would change the results in the query.
1. If there is one delete the default Table1
2. Import the Excel spreadsheet into a new table in the database. Use the first row as column headings. Remember field names with spaces can problematic. It is best to remove the spaces from the field names.
3. Select appropriate data types. The vaccine field is a Yes or No type field. The rest should be apparent from the column name and data they contain.
4. Select an appropriate primary key from the existing fields.
5. Name the new table Pets. You do not need to save the import steps.
6. Import the data from the text file to a new table.
7. Accept the default of delimited data
8. Accept the default of comma for the delimiter and use the first row as column headings. Do not allow the wizard to add any additional fields
9. Select appropriate data types.
10. Select the Origin field to be the primary key
11. Name the new table Owners
12. Create a relationship between the tables and enforce referential Integrity with cascading updates
13. Set a validation rule that cost cannot be less than 10 cents or more than $300. Make sure to include an appropriate error message if the user violates the limits.
14. Make the Pet field a lookup field and type in the values dog, cat, fish, rabbit, guinea pig, rat
15. Create a query that shows all pets from the origin of Brea.
16. Create a query that will show all the origins that start with the letters Lo.
17.
18. Create a query that will show all pets with cost of $13.50 or less.
19. Create a query that will show all Dogs with an Origin of Loma Vista
20. Create a query that will show all dogs or cats with the origin of Orange
21. Create a query that shows all pets sorted firstly descending by Cost and then ascending by Pet.
22. Create a query that shows the top 7 Pets
23. Create a query that shows the how many pets have had their vaccine
24. Create a Query that shows the owners and what pets they are responsible for
25. Create a query that includes a field that show the new cost if the current cost is raised by 35 cents. Label the field New Cost, Make it a currency format.
26. Create a query that will calculate the average price of all the pets that are rats. The average needs to be part of the query. Do not use a total row
27. Create a query that lists the pet types. Each pet type should only show up once in the results.
28. Create a query that will show all pets born on or after March 15th 2019
29. Create a parameter query based on the pet table will allow a user to input an origin name with the appropriate text to prompt the user for input.
30. Q15 -Create a query that will update all instances of the Origin Placentia to Diamond Bar. Run The Query
