Access Tutorial- III

Fall 2003

Introduction

This tutorial has been designed to give an introduction to creating queries and web-enabled documents in MS Access. The tutorial will cover the following topics:

  • Creating a Query (from multiple tables) using the Simple Query Wizard
  • Defining Record Selection Criteria for Queries
  • Using Aggregate Functions in Queries
  • Queries Using a Calculated Field
  • Using a Pattern Match in a Query
  • Creating a Parameter Query
  • Creating dynamic HTML documents from MS Access tables
Creating a Query (from multiple tables) using the Simple Query Wizard

For all the queries, you will be using the database that you have just downloaded from the Web. In this section you will create a simple query using the Simple Query Wizard which will show some relevant Student Information such as, StudID, Name, Major, and Address and some course information such as, the Course_ID and Course_Name of all the courses that each student is taking.

  1. In order to do this open the tutorial database and click on the Queries tab in the main database window.
  2. Select New, and immediately you will see the New Query window.
  3. Select Simple Query Wizard and click OK.
  4. You will now see the Simple Query Wizard window.
  5. From the combo box titled Table/Queries select the Student Table.
  6. Move all the fields from the Available to the Selected column. Do not click Next as yet.
  7. Now select the Course table from the combo box and move the Course_ID and Course_Name fields to the Selected column and click Next.
  8. In the next screen, make sure the Detail option button is selected and click Next.
  9. In the text box titled “What title do you want for your query?”, type StudentInfo and click Finish.
  10. You should now be able to see the results of the query in tabular format.
  11. In order to see the SQL code that Access has generated, click on the down arrow beside the Design View icon (blue triangle) and select SQL view.
Defining Record Selection Criteria for Queries

While the last query showed the student and course information for all students, in this section we will create a query that will show the student and course information for only those students who are majoring in information systems.

  1. In order to insert the new criteria, select the first query and click on Design.
  2. The design view of the query will now be seen.
  3. Beside the row titled Criteria, under the Major column, type “Information Systems” just as you have in the tables, and press enter.
  4. Let us say that we will no longer like to see the Major field in our results since we know that all of them are IS majors.
  5. In order to do that, uncheck the Show box under Major.
  6. Click on the diskette icon to save the changes.
  7. Now click on the Run icon (red exclamation) to view the results.
Using Aggregate Functions in Queries

Now that we have seen all relevant information about IS majors, we will like to do a count of the number of IS majors. MS Access has the Aggregate Functions feature that enables users to perform arithmetic functions such as total, average, sum, etc. on one or all of the fields of the different tables.

  1. In order to do this, select New from the Queries tab.
  2. Select Simple Query Wizard from the New Query window.
  3. In the Simple Query Wizard window, select the Student table, move the Stud_ID and the Major fields to the Selected Fields column and click Next.
  4. Name the query “Total”, select the option button Modify the Query Design, and click Finish.
  5. You should now be able to see the design view of the query.
  6. Click on the Sort row under the Stud_ID column and click on the  icon from the upper tool bar.
  7. Immediately a new row titled Total will be inserted.
  8. Click on the down arrow on the Total row under Stud_ID, and select Count.
  9. Now move to the Major column and beside Criteria type “Information Systems”.
  10. You may uncheck the show box for Major if you like.
  11. Click on the run icon to run the query.
  12. Now if you want to see the total number of students per major, return back to the design view.
  13. Remove the Information Systems criteria from the Major column, and make sure that the Group By option is selected in the Total row.
  14. Run the query again to see the new results.
  15. You should be able to see each major and the number of students under each.
Queries Using a Calculated Field
You have noticed by now that your current tutorial database has two additional fields under the Student table: Hourly_rate and Number_of_Hours_Worked. In this section we will create a query that will return the total wages earned by each student who has worked.
  1. In order to do that, click the New query button, select the Simple Query Wizard.
  2. Select the Student table and move the Stud_ID and the Stud_Name fields to the Selected Fields column.
  3. Now name the query as total wages, and click on Finish.
  4. In the design view of the query, move to the empty column beside Stud_Name, right click on the Field box, and select Build.
  5. Immediately, the Expression Builder window will open.
  6. In the lower section of the window, you will notice three columns.
  7. Double-click on the plus (+) sign beside the word table, and open the folder called Student.
  8. Immediately you will see all the fields under the Student table in the next column.
  9. Now click on the Hourly_rate field and select paste.
  10. You will now see the Hourly_rate field in the upper box.
  11. Now select the asterisk (*) sign to signify that you want to multiply values in this field.
  12. Now click on the Number_of_Hours_Worked field and click on paste again.
  13. Click OK and close the expression builder.
  14. Scroll to the left most part of the expression in the Field box, and type in “Total Wages:” (do not put in the double quotes)
  15. Save the query and run it.
Using a Pattern Match in a Query

Now we will create a query that will return the name, ID, and major of all students who live in Pullman. The city information (for example, Pullman) is located under the Address field along with street numbers and names. Hence, we will have to use the pattern matching feature of MS Access, where we can specify a string of characters and ask Access to find any row that matches the specified string of characters in a certain field.

  1. To do this, click on the New button under the Queries tab, and select the Simple Query Wizard.
  2. Now select the Student table, move the Stud_ID, Stud_Name, Address and Major fields to the Selected Fields column, and click Next.
  3. Name the query as “Students in Pullman,” select the “modify the query design,” and click Finish.
  4. In the design view, move to the Address column, and in the criteria row type Like “*Pullman”.
  5. The asterisk will tell Access that there can be other characters before the string Pullman in this field.
  6. Run the query and then select the SQL view to see the generated SQL code.
Creating a Parameter Query

Say, we now want to see the names of students who have received an “A” grade in any course. However, we would like to give users the option to select the major for which they would like to see the A students. This can be done through a parameter query, where the user can type in a parameter (or the name of a major).

  1. In order to do this, select NewSimple Query Wizard.
  2. Select the Student table and move the Stud_Name and Major fields to the Selected Fields column. Do not click on Next as yet.
  3. Now select the Courses table and move the Course_ID and Course_Name fields to the Selected Fields Column.
  4. Finally, select the Takes table, move the Grade field to the Selected Fields column, and click Next.
  5. Select a detail query, name the query as Major Parameter, select the “modify query design” option and click Finish.
  6. In the design view, type “A” in the criteria box under Grade, and uncheck the show check box.
  7. Now move to the Major column, and type in “[Enter Major:]”
  8. Save the changes and run the query.
Creating dynamic HTML documents from MS Access tables

In this final section, we will create a dynamic HTML document based on one of the tables. Users will able to open this document with a Web browser and also make changes to the data stored in the Access Database.

  1. In order to create a data access page, select the Pages tab from the list of objects in the main database window, and click on New.
  2. Immediately, the New Data Access Page will open.
  3. Select Autopage: Columnar, scroll down to the lower half of the window, and select the Student table from the list of tables/queries.
  4. A new Data Access Page showing the Student information will open
  5. You can use the record navigator to view all the other records in the Student table.
  6. Now click on the disk icon (located in your main tool bar) to save this new page.
  7. Immediately, the Save as Data Access Page window will appear.
  8. Name you page as StudentRecords and click OK.
  9. In the main database window, select the StudentRecords page, right-click on it, and select Web Page Preview.
  10. You will now see an Internet Explorer window with your StudentRecords page.
  11. In the first record, change the major information, to show a different major.
  12. Click on the save icon located in your page (picture of a disk and a pencil) and close the page.
  13. In the database window, click on the Table objects and select the Student table.
  14. You will notice the change that you have made through the web page.

Hope you found this tutorial helpful. If you have any further questions, email me at .

1