data analytics question and need guidance to help me learn.
see the file for more informaitions
Requirements: as long as it take
Systems Analysis BUS 3511
Class Project: Sarah’s Software Database
1. In this assignment we will work with a database using Microsoft Access. If you do not have Access on your computer, MS Access is available in the lab/classroom in the College of Business. In all likelihood, it is available in other labs or library on campus as well.
Microsoft has an Access Video Training at https://support.microsoft.com/en-us/office/access-video-training-a5ffb1ef-4cc4-4d79-a862-e2dda6ef38e6
Please watch the training videos to familiarize yourself with Access, then proceed to the assignment below.
3. Sarah’s Software wants to create a database to keep important information about its products and suppliers. This database will contain two tables, a supplier table and a product table. We would like to perform several queries and produce several managerial reports based on the data contained in the two tables.
4. Create a new database named [Lab_Assignment]_[YourFirstName]_[YourLastName].
Create a new Excel file. Copy the following tables into Excel first. Highlight the first table below and copy. In Excel use Paste Special and choose Text. Name the worksheet as “Products”. Copy the second table into a different worksheet in Excel. Name it as “Suppliers” Then in Access, choose the External Data tab and click Excel. Browse to find the Excel file with the information for the tables. An Import Spreadsheet Wizard will pop up. Choose the name of the worksheet you wish to import. (Do each worksheet separately.) Click Next. The checkbox for first row contains column headers is checked by default. Leave it and click Next again. Click Next again. Then click Choose My Own Primary Key. For the first table choose Product Number, for the second Supplier Number. Click Next and name the table Products and Finish. Do the same for the Supplier Table.
6. Create two forms, Products Form and Suppliers Form, to enable user to enter data into the tables more easily. An easy way to do this is, while the Products Table is open, go to the Create Tab and click on Form. Then use File/Save Object As to rename it ProductsForm. Do the same from the Suppliers Table. When you go to close the tab for the form or save it, the program will ask you for a name. Use Products Form and Suppliers Form as indicated above.
7. Create three queries called Most Expensive Products Query, Suppliers And Their Products Query and Low In Stock Query. These queries should be written to give information according to the criteria below. You can create the queries by going to the Create Tab and choosing Query Design.
For the Most Expensive Products Query, identify the five most expensive pieces of software, based on selling cost. List the product name, selling cost, purchase cost and the quantity on hand in the query. Open the query in Design View and to sort from most expensive to least, based on selling cost, go to sort and choose descending. Then in the query Design View, under Query Setup in the Design tab, go to Return and choose 5. Then hit the Run ! . When you go to close the tab for the form or save it, the program will ask you for a name. Name it Most Expensive Products Query. Be sure to include Query since we will have a report of the same name later. If you need to make corrections, go into Design View.
For Low In Stock Query, include the names and quantities on hand for all software. Set the criteria for those with 2 or less items on hand. Sort alphabetically. Then hit the Run ! . When you go to close the tab for the form or save it, the program will ask you for a name. Name it Low In Stock Query . Be sure to include Query since we will have a report of the same name later.
For the Suppliers And Their Products Query, you will first have to create a relationship between the two tables. Put both tables in the query and the program will automatically connect the like fields. Choose Company Name, Product Name, Quantity on Hand, and Reorder Levels. You’ll have to get these from whichever table has them. Then sort alphabetically by Company Name. Then hit the Run ! . When you go to close the tab for the form or save it, the program will ask you for a name. Name it Suppliers And Their Products Query. Be sure to include Query since we will have a report of the same name later.
8. Create three reports called Most Expensive Products Report, Suppliers And Their Products Report and Low In Stock Report. Create these reports using the three queries you’ve already created. You can create them using the Report Wizard under the Create tab. Just select the appropriate query in the wizard for each one.
At the end of the wizard it asks for the report name. Again, make sure you change it from Query to Report.
Then go back to Design View for any modifications you need to make the tables look good. Anytime you want to delete any item—table, form, query or report—you must first close it in Access. Then right-click to delete it.
While in Design View, click on the green tab above View to get formatting tools. Format all prices as currency. Make sure headings are visible in their entirety and columns look good. You can also choose themes in Access 2013. Go back to Report View to see how it looks after making changes in Design View.
For the first report, MostExpensiveSoftwareReport, prepare a report that identifies the five most expensive pieces of software, based on selling cost. List the product name, selling cost, purchase cost and the quantity on hand. Order from most to least expensive.
For the second report, Low In Stock Report, include the names and quantities in stock for all software with 2 or less items on hand. Sort alphabetically.
For the third report, Suppliers And Their Products Report, list each supplier, its products, quantity on hand, and associated reorder levels. The report should be sorted alphabetically by supplier. Within each supplier category, the products should be sorted alphabetically. This can be done in the report wizard.
You can use any style you want for the reports but make sure they are clear and easy to read.
9. Submit the assignment before the due date.
10. To submit your assignment, go to Canvas. Under Assignment tab, click the button Add Attachments. Then attach your file named [Lab_Assignment]_[YourFirstName]_[YourLastName]. Then hit Submit and OK. Be sure that you submitted successfully. If you don’t, go back and try it again. If it doesn’t submit, and you think it has, I won’t be able to see it and won’t know that you submitted on time.
11. Review the Academic Honesty Policy and make sure you comply with it. DO NOT GIVE YOUR WORK TO ANYONE ELSE.