CSC 455

Fall 2009

Lab 8: Microsoft Access Continued. 

Boating Supplies.  20 points

 

Open your Boating Supplies database.  Notice the Security Warning that some content has been disabled.  You can enable the content, but this will happen every time to you open it.  To change that, you need to create a trusted location.  Close the database leaving Access open and do the following:

1.     Click the Microsoft Office Button Button image, and then click Access Options at the bottom of the options box.

2.     Click Trust Center, click Trust Center Settings, and then click Trusted Locations. (Note: In practice, it is recommended that you don't make your entire Documents or My Documents folder a trusted location. Doing so creates a larger target for a hacker to potentially exploit and increases your security risk. Create a subfolder within Documents or My Documents, and make only that folder a trusted location.)

3.     Click Add New Location.  Browse to locate your USB drive or select the T drive if that is where your database is stored.

4.     Select the Subfolders of this location are also trusted check box.

5.     In the Description box, type what you want to describe the purpose of the trusted location.

6.     Click OK.

7.     Open the database.

8.     The Security Warning about disabled content should be gone.

Import Excel data (Note: When you download, you may need to change the file type from zip to .xlsx):

1.     Download the file called Products.xlsx from http://people.uncw.edu/mferner/CSC455/labs/products.xlsx and view it.  If you try to import it, there will be errors.  Why?

2.     Now download the file called ProductsV2 from http://people.uncw.edu/mferner/CSC455/labs/productsV2.xlsx.  This one should work.  Why?

3.     Since you already have a products table, you want to append this data to the existing Products table.  Proceed with the import.  The second screen of the wizard should recognize that the first row contains column headings.

4.     Open the Products table and verify the data import.

 

Next, you will learn some basics about filters and simple queries. Filters are a way to get a temporary view of desired criteria.  If the view is something that is repeated, it’s better to use a query which can be saved and re-run. With the Products table open, do the following:

1.     Use the Filter tools to find products from the supplier called Life Rescue.  Click anywhere in the SupplierID column.  Click on the Filter button on the Home tab.  Deselect the Select All box, and then select the box next to Life Rescue.  Apply the filter.  Remove the filter by clicking the Toggle Filter button.

2.     The Filter by Selection tool can be used to locate all products that do not contain 0 in the UnitsOnOrder column.  Click on a cell containing a zero in the UnitsOnOrder column.  Click on Selection in the Filter box of the Home tab.  Choose ‘Does not equal zero.  Remove the filter.

3.     Close the products table but DON'T save the changes.

4.     Click the Create tab on the Office ribbon.  Click Query wizard. Use the simple query wizard to create a query which includes the following fields from the Products table:  SupplierID, ProductName, UnitPrice, UnitsOnHand, UnitsOnOrder, and ReorderLevel. Name the query OnOrder Query, and accept all other default values form the Wizard but name the query OnOrder.

5.     Click the Home tab on the ribbon and switch to Design view, sort in ascending order by SupplierID and Product Name. In the Criteria row for UnitsOnOrder, enter <>0. Click !Run. This should show all products on order. Close it and save.

6.     Use the Simple Query Wizard to create another query.  Include the same fields from the Products table: SupplierID, ProductName, UnitPrice, UnitsOnHand, UnitsOnOrder, and ReorderLevel. Name this query ReorderList. In Design view, sort in ascending order by ProductName. In the Criteria row for UnitsOnHand, enter <=[ReorderLevel] and in the Criteria row for UnitsOnOrder, enter  =0 .  Run!  This query should show all products that need to be reordered by showing inventory less than the reorder level on products that have zero on order.  The square brackets around ReorderLevel in the criteria mean to reference that field.  Close and save that query.

7.     Create a calculated query: In Design view (Query Design button on the Create Tab.) Add the Products table.  Then add the ProductName, UnitsOnHand, UnitsOnOrder, and ReorderLevel fields from the Products tables by either double-clicking on each field name or selecting it from the pull-down list in the first row of the query grid.  Enter the following into the top row of the fifth column: UntilReorder: [UnitsOnHand]-[ReorderLevel].  Add the criteria =0 to UnitsOnOrder.  Run the query.  This should show all products that do not have any units on order but I will also show items that will need to be reordered soon or that should have been ordered and weren’t.  Save this query as UpcomingOrders.  If the result isn’t showing up, return to design view and click the Show box for that column.

8.     The negative numbers in the previous query show the products that should have already been reordered.  Return to design view, and add the criteria to the UntilReorder field <=0. Run the query, and verify that the numbers in the last column are <=0.  Use the Office Toolbar to find Save As, and then Save As Object. Save this query as CreateOrders. Switch back to Design View and change the query type to Make Table.  Run the query naming the new table NeedToOrder .  Open the newly created table to see what needs to be ordered.  Close the query and the new table.

9.     Now suppose they order 5 units of Fisherman Catch.  Update the OnOrder amount in the Products table or through the Form.  Close the object, and rerun the CreateOrders query.  Accept the warnings, and recreate the NeedToOder table.  Check the NeedToOrder table and verify that Fisherman Catch is not there.  Close the table and the query.

10.   Instead of manually entering order numbers as in the previous step, suppose the company automates the ordering process by making quantities reordered automatically be twice the reorder level.  First, open the CreateOrders in Design View.  In the next blank column, enter ReorderAmount: [ReorderLevel]*2.  In the next blank column add the SupplierID field. (This is in preparation for the next step). Run the query and close.  Check the NeedToOrder table to see how many of each item to order.

11.   It would be helpful to have a report to organize the NeedToOrder data better. An Access report provides an interface for generating formatted output for printing, viewing, or sending.  Reports do not allow data to be modified as forms do, but they provide a more professional-looking output than standard tables. Reports can be generated from tables or queries.  Click the Create tab, and select Report Wizard. Create a report containing the SupplierID and ProductName and ReorderAmount from the NeedToOrder table.  On the second screen of the wizard choose to Group By SupplierID. Acceept the other default values in the wizard, but name the report ReorderListBySupplier.

12.   Next, open the UpcomingOrders query in design view.  Change the query type to Update.  There is now an Update To: row.  Put in the formula [ReorderLevel]*2 in the UnitsOnOrder column, Update To: row.  Run the Query, and allow the update.  Save this query as ProcessOrders.  (Office Button -> Save As -> Save Object As ) This query could be run under the assumption that all orders from NeedToOrder have been completed instead of updating the Products table manually.  If you now rerun the CreateOrders query, the NeedToOrder table is blank.

13.   Parameterized queries are a way to make the Access database interactive.  They can prompt for a value to search for.  Close all open tables and queries. Create a new query that will show Product information for all products on order.  Run the query to make sure it works.  Now add a way to search by Category: put [Which category?] in the CategoryID/Criteria cell. Run the query, and you should be prompted with the text you entered.  Enter Flags.  The query is empty, but there are products from the Flags category on order.  To fix that, the Categories table containing the Category names needs to be referenced.  Remember the Products table only stores the CategoryIDs.  Switch to Design View, click the Show Table button, add the Categories table.  Add CategoryName to the design grid.  Move the prompt in [] from the CategoryID column to the CategoryName column. Run the query again, and enter Flags.  It should now show the product information for items on order in the Flags category.

Create a Switchboard:

1.     Under Database Tools, select Switchboard Manager. Access should report that no switchboard exists.  Answer yes to create one.

2.     Choose to Edit the Main Switchboard and add the following New items:

a.     Add a new supplier: open Supplier Form in Add Mode

b.     Edit existing supplier:  open Supplier Form in Edit Mode

c.     Add a new product: open Product Form in Add Mode

d.     Exit: exit the application

3.     Close the Switchboard page and the Switchboard Manager.  You should now have a Form called Switchboard.  To display the Switchboard at startup, from the Office button, choose Access Options.  In the Current Database page, click the arrow next to the Display Form box.  Choose Switchboard form the list.  Close the database and re-open to see your switchboard.

 

Submit your completed database into Blackboard. (http://ncvista.blackboard.com)