Create a working MS Access database based on the attached data set. Each tab represents a table in the database. The field names are already defined for you as column headers. Use the MS Access import wizard for the best results.
Provided Tables – field names are provided as column headers. You need to import these
1. Locations – 5 provided records
2. Products – 25 provided records
3. Inventory – 125 provided records
4. Employees – 25 provided records
5. Transactions – 2000 provided records
Required relationships – you need to define these
1. The Inventory table has a relationship with both Products and Locations. There is 1 record in the Inventory table for each product, at each location, and each of those records has the quantity on hand for that product at that location.
2. The Employees table has a relationship with Locations. For this project, we assume that each employee only works at 1 location.
3. The Transactions table has a relationship with both Employees and Inventory. For every record in the Transactions table, we track which employee was the cashier, and which location the transaction occurred at.
Required Queries – you need to create these
1. List of Employees and the Locations that they work at. The fields that should be in your query are: first name, last name, hire_date, location. Name the query "Employee List by Location".
2. List of Employees ordered by pay_rate from highest to lowest. The fields that should be in your query are: first name, last name, pay rate. Sort by pay rate from high to low. Name the query "Employee Pay Rates".
3. List the top 5 most profitable individual products (HINT: this will take 2 queries to accomplish; One to get profit for all products, then a second query to pull the top 5 profitable products from the first query.) If you find another way to get the same result, that's fine too. Name these queries "Product Profit" and "Product Profit Top 5".
4. List all items in inventory, including the location, the product name, quantity, cost, and calculated field "total_cost" defined by quantity * cost. Name the query “Inventory Quantity by Store”.
5. Calculate the total cost of all items currently in inventory across all locations. Name the query “Sum of Inventory Cost”.
Required Reports – you need to create these.
You can base these on 1 or more of the queries above, if applicable. The reports should be formatted neatly, meaning rows and columns all fit in the printable space, and there are no extra pages as a result of improper formatting.
1. Employee List
2. Inventory By Store
This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.
This is only a preview of the solution. Please use the purchase button to see the entire solution