LAB #10
CSC 101
Introduction to Databases
R Mohammadi
 

 

Objectives:

 

Using Microsoft Access, learn to...

Create a query with calculated fields.

Filter data with a query.

Develop a report.

Sort records.

 

About this lab...

Its is assumed that you have already done lab9 and created the lab9 database on your disk. We will learn to take advantage of the power of databases in this lab. Two of the main features of Access are the ability to easily filter records based on a criteria and to create reports summarizing the information in our tables. So far, all you have learned is to keep some records, which you could have as easily been done with a spreadsheet, hopefully, you'll learn much more in today's lab. Imagine that you have hundreds of records in your database. Finding an individual may be time consuming; being able to work with a subset of records that share a certain characteristic would also be imperative. Regardless of the size of a table, you may want a nice listing of its content with summary data. All of these capabilities exist in Access and we'll explore them here. Good luck.

 

 1. Learn about queries, reports, and sorting with Microsoft Office Starts Here.

                a.    Insert your CD in the CD-ROM drive.

                b.    Start Microsoft Office Starts Here by clicking the Icon on your desktop.

                c.    Choose the Basics by clicking on it.
                d.    Under Sections, pick 

                e.    Do the last three lessons, Create Reports, Sort and Query a Database, and save your database work.

                f.    Take Quiz after each lesson. Be sure to let your lab instructor see the results before you continue.

                g.    Close this window.
 

2. Starting Microsoft Access.

Start Microsoft Access by clicking on Start, clicking on Programs, and then clicking Microsoft Access. An Access window should be on your screen.

 

 3. Open the lab9 database.
    1. When you start up Access, an initial dialog box pops up that inquires if you want a blank database, start up database wizard, or open an existing database. Select Open an Existing Database and Click OK.
    2. Open window will pop up, Change the Look In to 3 ½" Floppy (A:).
    3. Double-click on lab9.
    4. The lab9: Database window pops up.
 

 

4. Database query.

Access lets us filter records as you saw in one of the lessons earlier; filtering is a form of database query that lets you select a subset of the record/rows in a table. You also saw how a Query is built with query wizard, but it was in the context of limiting the fields/columns that you see. Here we will use a query to introduce the concept of calculated field and we will reinforce the concept of filtering.

 
    1. Click on the Queries tab to bring it in front, click on New.

      2.  

    2. The New Query window will pop up. Choose Design View and click OK.

      3.  

    3. In the Show Table popup dialog box, Student_grades should be highlighted, Click on Add.

      4.  

    4. Close the Show Table dialog box. The following Query1:Select Query window is where you define your query.

      5.  

       

    5. Double-click on First Name and then on Last name to select them for your query; they should appear in the bottom half of the window in the first two columns of the row labeled as Field:.

      6.  

    6. Click the 3rd cell for fields and type Total:GradeA+GradeB+GradeC

      7.  

    7. Hit Enter and in the 4th cell type Average:Total/3

      8.  

    8. Lets place a criteria on the Total; in the row labeled Criteria, in the column for Total, type >50; this will create a filter which simply says we are only interested in student records with the Total greater than 50 in this query.

      9.  

    9. Close the Query 1: Select Query window.

      10.  

    10. Click on Yes when asked if you want to save the query.

      11.  

    11. Name the query Student_tot_avg.

      12.  

    12. Double-click on the Student_tot_avg query to open it. As you can see it looks like a table, but its records are made from the records in the Student_grade table. The first two fields come directly from Student_grade and the Total and Average fields are Calculated Fields based on the grades of each student. You should also have no records with a Total less than or equal to 50.

      13.  

    13. To sort the records based on the descending order of Totals:
      1. Click on the header button of the Total column to highlight the column.

      2. Click on the sort in descending order button in the toolbar.
  n. Close the Student_tot_avg: Select Query window.

 

 5. Create a report.

We'll create a report on the on the Student_tot_avg query.

 

    1. Click on the tab for Reports.

      2.  

    2. Click on New.

      3.  

    3. Pick AutoReport: Tabular.

    4. Click on the List button at the bottom of the New Report window and pick Student_tot_avg.

    5. Student_tot_avg window should be on the screen, change to the design view by clicking in the tool bar.

      6.  

    6. Change the Report Header to Student Total/Average Report.

      7.  

    7. If the Toolbox window is not already visible, in the View menu, click on it to.

      8.  

    8. Click on the Text Box button in the Toolbox.

      9.  

    9. Move the mouse pointer into the portion of the report identified as Report Footer, notice that as you move it, it looks like the image of the Text Box button.

      10.  

    10. Click to complete the insertion of the Text Box.

      11.  

    11. Change the label for the Textbox, which is probably defaulted to Text13:, to Avg of Tot. Simply, click in the label portion and delete what is there and type the new text.

      12.  

    12. Click in the data portion of the Text Box, which should contain the word Unbound, and type =avg(Total)

      13.  

    13. Click on the Print Preview button, in the Toolbar at the top of the Access window. Make sure the lab instructor sees this preview.

      14.  

    14. Close the Student_tot_avg window.

      15.  

    15. Click Yes, when asked if you wish to save the report.

      16.  

    16. Click OK in the Save As Dialog window.
 

6. Close the Access window and logout.

Close your Access window and logout of the workstation. No need to save anything, the query and report were already saved in the lab9 database.