COSC1371               PROJECT-9(ACCESS-2)               Due: Tuesday, November 15, 2011 

                           Query and Report Creation  

        Objectives:

1.  Create and save a new database file: Project9.accdb

2.  Import a  table called Physicians into this database from Access Chapter-3 datafiles

of our textbook resources.

        3. Create and save a table called CLASSES into this database.

        4. Create and apply two filters to select/sort records of this table.

        5. Save the results of applying the two filters as Queries named Query1 and Query2.

        6. Create a report from the imported table using the Report Wizard named PhysicianReport.

           This wizard lets us 

·         choose table fields to be included to the report (hence, not all fields of a table need to be included)

·         choose some selected fields for grouping levels

·         choose remaining fields (not selected for Grouping Levels) as sorting keys and

·         choose report layout from Stepped, Block and Outline, and Orientation of the report.

        Steps:

1.  Open ACCESS2010.

When the Access is opened, the BackStage view with NEW Option selected by default will appear for you to choose a new database template and file name. You can select the Blank Database and enter a database name into the File Name box and select the file location by clicking Browse icon (yellow folder) and click Create button.

2.  The Access creates a blank new database and a new table will automatically appear in the Datasheet view with a temporary name.

3.  Change the temporary table name to CLASSES and enter all 38 records of the attached sheet after defining the following nine fields of this table in the Design View first.

The Day field should be a Lookup field. You can set up a lookup field by clicking Lookup Wizard… Option in the data type list in the table Design view or Lookup&Relationship Option in the table Datasheet view. 

               Sequence:      A (AutoNumber) //Note only one such data type per a table

               Dept:          T (Text: Default)

               Course_No:     N (Number)

               Section_No:    N (Number)

               Instructor:    T

               Day:            T

               Time:          D (Date/time)   //Entering "17:00" turns to 5PM.

               Building:      T

               Room:          N (Number)

4.  When done entering all field names and data types, save the table by clicking Save 

Button before switching to the DataSheet View by clicking the View button.

5.  In this view, enter all 38 class Records into this table.

           When done, save again (CTRL-S will do).

6.  Define two filters and save both as Query1 and Query2 by clicking the Save Button 

which will display “Save as Query” dialog box.

7.  The Filter Definition D.B. will appear when you click the "Advanced Filter/Sort..." option included in the "Advanced" drop-down menu in the Sort&Filter Group on the

           Home Tab. 

8.  This D.B. has two sections: The Fields Section and the Filter Definition

           Section. The latter section is where you define a filter using field names of 

           the open table as filter criteria

9.  Query1 is to select MW or MWF classes between 10AM and 4PM and sort selected records by Time (Ascending) (14 records)

           When finished defining the filter, you can CTRL-S to save "Save As Query"

           When you click this button, "Save As Query" D.B. will appear.

           Type the filter name "Query1" and click the OK Button.

           Note typing ">= 10:00 and <= 16:00" is to mean "between 10AM and 4PM"

10.Query2 is to select TTH Classses starting at or after 9:30AM and sort the selected records by time first and then by Course-No., both ascending. (12 Records).

11.Open table Physician included in the database sfinsurance in Access Chapter-3 datafiles of our textbook web resources. And export it to your database, Project9.

You can use Access Button available in the Export Group on External Data Tab.

Access lets us copy multiple Access objects by importing, but only an open single object by exporting.

12.Create a report out of this table using the Report Wizard. Select five fields, FirstName, LastName, City, ZipCode and MemberCount for inclusion to the report. And choose City as the top grouping level followed by ZipCode. And use the MemberCount as the descending sorting key for the report. Other choices can be made as you wish.

13.Your USB must have: Database named "Project9.accdb" inside the folder: COSC1371, 

that contains two tables CLASSES and Physicians, one report PhysicianReport and two Queries, query1 and Query2.

        *** Some ACCESS useful key shortcuts***

            F6:        Move between window sections.      

            CTRL;:     Insert current date.

            CTRL':     Enter the same value from previous record.

            CTRL=:     Move to the first Blank record.