You work for a beverage company that sells coffee and tea. Your boss has asked you to help set up thecompany’s new database using Microsoft Access and then perform some queries in Access to determineinformation that your boss needs to give to the senior management. You are given a flat file(Beverage_Co_Sp25.xls) with the company’s sales information. When you open the file, you notice that it is notset up in efficient tables. You must create efficient tables in Excel before importing the data into MicrosoftAccess. Then, you need to run queries to gather the information your boss needs.Excel InstructionsYou will need to create 5 efficient tables from the one worksheet in the Excel file you have been given:Customers, Employees, Sales, Inventory, and SalesItems. You will need to determine which columns need to beincluded in each table and delete any duplicates and blank rows in the tables.Furthermore, once you have created the Sales table, make sure to change the name of the Sales_CustomerIDcolumn to CustomerID and change the name of the Sales_EmployeeID column to EmployeeID. Also, theSalesItems table is a linking table that will need a dual primary key (and you will need to determine which itemsmake up the primary key) Using these : CustomerID CompanyName Address City State ZipCode PhoneNumber CreditLimit EmployeeID EmployeeFirstName EmployeeLastName EmployeeWorkPhone Title EmployeeHireDate EmployeeDOB InvoiceID InvoiceDate OrderDate Sales_CustomerID Sales_EmployeeID CustomerPO InventoryID Name BeverageType Caffeinated Flavored OnHand QuantitySold UnitPrice
You work for a beverage company that sells coffee and tea. Your boss has asked you to help set up the
company’s new database using Microsoft Access and then perform some queries in Access to determine
information that your boss needs to give to the senior management. You are given a flat file
(Beverage_Co_Sp25.xls) with the company’s sales information. When you open the file, you notice that it is not
set up in efficient tables. You must create efficient tables in Excel before importing the data into Microsoft
Access. Then, you need to run queries to gather the information your boss needs.
Excel Instructions
You will need to create 5 efficient tables from the one worksheet in the Excel file you have been given:
Customers, Employees, Sales, Inventory, and SalesItems. You will need to determine which columns need to be
included in each table and delete any duplicates and blank rows in the tables.
Furthermore, once you have created the Sales table, make sure to change the name of the Sales_CustomerID
column to CustomerID and change the name of the Sales_EmployeeID column to EmployeeID. Also, the
SalesItems table is a linking table that will need a dual primary key (and you will need to determine which items
make up the primary key)
Using these :
| CustomerID | CompanyName | Address | City | State | ZipCode | PhoneNumber | CreditLimit | EmployeeID | EmployeeFirstName | EmployeeLastName | EmployeeWorkPhone | Title | EmployeeHireDate | EmployeeDOB | InvoiceID | InvoiceDate | OrderDate | Sales_CustomerID | Sales_EmployeeID | CustomerPO | InventoryID | Name | BeverageType | Caffeinated | Flavored | OnHand | QuantitySold | UnitPrice |
AI-generated content may present inaccurate or offensive content that does not represent bartleby’s views.
Unlock instant AI solutions
Tap the button
to generate a solution
