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$].$

$5.$ 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.

Product Number Product Category Product Name Purchase Cost Selling Cost Supplier Number Quantity On Hand Reorder Level

$1$ Office Write$-$a$-$Lot $$207\mathrm{.}49$ $$290\mathrm{.}49172$

$10$ Entertainment Blue Moon $$75\mathrm{.}29$ $$105\mathrm{.}41342$

$11$ Children Color My World $$15\mathrm{.}00$ $$25\mathrm{.}50385$

$12$ Children Sarah's Rabbits $$69\mathrm{.}99$ $$97\mathrm{.}98165$

$13$ Children Red Rhino $$95\mathrm{.}47$ $$133\mathrm{.}66345$

$14$ Office Suite $$500\mathrm{.}50$ $$700\mathrm{.}70302$

$2$ Office Groups $$281\mathrm{.}52$ $$394\mathrm{.}13172$

$3$ Productivity TaxMan $$350\mathrm{.}52$ $$490\mathrm{.}73252$

$4$ Productivity Photos Galore $$401\mathrm{.}11$ $$561\mathrm{.}54222$

$5$ Productivity Home Office $$410\mathrm{.}01$ $$574\mathrm{.}01212$

$6$ Educational Fanny $7$ $$150\mathrm{.}89$ $$211\mathrm{.}46292$

$7$ Educational Tea for Two $$175\mathrm{.}02$ $$245\mathrm{.}03332$

$8$ Entertainment Skippy $4$ $$100\mathrm{.}47$ $$140\mathrm{.}66312$

$9$ Entertainment Midnight Racer $$50\mathrm{.}00$ $$70\mathrm{.}00135$

Supplier Number Company Name Street Address City State Zip Code

$1$ Safety Software $107$ Piedmont Tulsa OK $74114$

$2$ Electrosoft $2000$ Oceanside Lane San Diego CA $92110$

$3$ Software Is Us $1459$ Washington Avenue Sioux Falls SD $57106$

$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.

a$.$ 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.

b$.$ 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.

c$.$ 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 R