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 |
2 | 3 |
4 | 5 |
6 | 7 |
8 | 9 |
10 | 11 | 12 | 13
| 14 || AppDatabase |
Ex1 | 2 |
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
Open the Microsoft Access database that you created in Database Exercise 2.
To do this, open Microsoft Access, click File Menu, Open... .
In the Open window, click on folders until you come to your port folder that you created for the Portfolio Exercise 1.
Then,
double click on AppMIS.mdb. This is the database you created in
Database Exercise 2.
Create a Microsoft Access query for the Exception Report of the Transaction File joined with the Personnel File.
To do this, continue with your opened AppMIS.mdb database.
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.
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.
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.
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.
To do this, continue with your opened AppMIS.mdb database.
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.
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.
Change to the Design view. Edit the report title to start with "DB Ex 7".
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).
Compact (and Repair) your
database. See Database Exercise 2, Step 3.d.
This will make your AppMIS.mdb as small as possible.
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.
If not all the
files copy over to stupub, try dragging and dropping AppMIS.mdb by itself.
See Database Exercise 3, Step 3.
Print your Exception Report of the Transaction File joined with the Personnel File (should have landscape orientation).
Note: you may update the Db1SysDesign.doc file to reflect any desired changes.