Mgt 4322 - Home   Spring 2010   Email to Dr. Lyons     PatLyons Home
[ Calendar10:10 | PortWebsite | StuWebs  | Port1 | 2 | 3 | 4 | 5 | 6 | Showcase | Presentation ]
[ Ch 1 || 3 || 5 || 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 || AppDatabase | Ex1 || 3 | 4 | 5 | 6 | 7 ]
[ HW 1| 2 | 3 | 4 |
| Career1| 2 | 3 ] [
SJU | TCB | CareerCtr | StuInfo | CareerLinks ] [ SJU Closing ] [H1N1SelfAssessment]


  Database Exercise 7 - Create Personnel Exception Report

  1. Open the Microsoft Access database that you created in Database Exercise 2.

    1. To do this, open Microsoft Access, click File Menu, Open... .

    2. In the Open window, click on folders until you come to your port folder that you created for the Portfolio Exercise 1.

    3. Then, double click on AppMIS.mdb.  This is the database you created in Database Exercise 2.
       

  2. Create a Microsoft Access query for the Exception Report of the Transaction File joined with the Personnel File.

    1. To do this, continue with your opened AppMIS.mdb database.

    2. For Access 2003, in the Database window, click Queries, and double click Create query by using wizard.
      For Access 2007, click Create tab on the ribbon, Query Wizard, then Simple Query Wizard.

    3. Follow the wizard by
      selecting your Transaction File in the dropdown Tables/Queries menu, selecting all the fields,
      selecting your Personnel File in the dropdown Tables/Queries menu, selecting all the fields, click Next,
      Detail (not Summary), click Next,
      key in your desired title (such as ClientExceptionbyNewAssociate), and Finish.
      The query appears in datasheet view.  It displays all the records in your transaction file along with all the personnel data fields.
      Next, click on the Design Icon (Triangle and Ruler).  The query appears in the Design View as shown in the screen image below.

      Notice how there is a line from the AssociateID field in the Client Table to the AssociateID field in the Associate Table.  This line represents the Join Properties of the two tables.  If you right-click on this line, a window will appear explaining the properties of the join.  The line appears because the AssociateID field name is identical in each table.  If you have problems, go back to Database Exercise 6, Step 2.
      You must have a Join Line
      in order to correctly specify the query.
       

    4. Add the exception criteria for your report.  The screen image below displays, for all associates with 2 or less years of experience, the clients with:
      1. Stock Price Activity down by at least 6% and News Impacting Stock is Unfavorable, or
      2. Stock Price Activity down by at least 4% and Institutional Activity is Sell.
      Notice how the years experience condition appears on both lines.
      Now, add the exception criteria for your report.  Have at least two lines of criteria, with at least three conditions on each line, that produces at least two, but no more than ten records.

       

  3. Create a Microsoft Access report for the Exception Report of the Transaction File joined with the Personnel File using the query you created in step 2 above.

    1. To do this, continue with your opened AppMIS.mdb database.

    2. For Access 2003, in the Database window, click Reports, and double click Create report by using wizard.
      For Access 2007, click the Create tab, then click Report Wizard in the Reports group.

    3. Follow the wizard by selecting the query you created in step 2 above from the dropdown Tables/Queries menu, selecting all the fields, click Next,
      How do you want to view your data? select by Associate (or whatever your personnel table is named), click Next,
      Do you want to add any grouping levels (no), click Next,
           (If, in Access 2003, you are not asked how to view your data,
                  then group data by your personnel table key field.)
      sort by your transaction table key field, click Next,
      select Outline 1 Layout (For Access 2007, Outline Layout works well),
      and Landscape Orientation, click Next,
      select Formal style, click Next, (For Access 2007, the None style works well),
      key in your desired title (ClientExceptionbyNewAssociate), and Finish.  The report appears.

    4. Change to the Design view.  Edit the report title to start with "DB Ex 7".

    5. Add a Label containing the exception criteria for your report.  Click Label, and place label in Report Header.  Copy and Paste the exception criteria from Item II.E of your Database Exercise 1 file (Db1SysDesign.doc).

    6. Compact (and Repair) your database.  See Database Exercise 2, Step 3.d.  This will make your AppMIS.mdb as small as possible.
       

  4. Use Microsoft Internet Explorer with ftp://stupub.stjohns.edu/ to drag and drop your modified copy of the entire port folder from your Mgt4322 folder to your stupub website.  This will copy your updated database, AppMIS.mdb.  Follow the instructions of Portfolio Exercise 1, step 5.

    1. If not all the files copy over to stupub, try dragging and dropping AppMIS.mdb by itself.  See Database Exercise 3, Step 3.
       

  5. Print your Exception Report of the Transaction File joined with the Personnel File (should have landscape orientation).

    1. Note: you may update the Db1SysDesign.doc file to reflect any desired changes.
       

  6. Requirements - see Homework Set IV
                                            (This page was last edited on January 18, 2010 .)