FIT 100

Lab 17: Get me to the Good Stuff!

Using the QBE (Query By Example) to view and manipulate data

Spring 2001

Reading to be done for Labs 17:

· Chapter 15 of Fluency with Information Technology: Foundations and Fundamentals of Information Technology

Introduction:

In today’s lab we will look at the logic layer: formulating queries that select, project and join up data from different tables. Access uses a GUI called the Query-By-Example so users can build queries visually. In the background, Access translates the visually represented query into SQL (Structured Query Language), the standardized language used to communicate with a database.

(See Lab 15 for a brief description of all three levels involved in a complete Relational Database Management System)

Logic Services

The processes by which a user can view, use, add and change data in the Data Storage level. A Query is the vehicle for this process. Queries are also called views because they are a user’s “view” of the data in the database.

Objectives:

1. To implement the abstract table operation concepts of Select, Project, Union, Difference and Product in Access.
2. To become familiar with graphical query method of QBE (Query-By-Example) that is used in Access.
3. To understand that Access provides the QBE to help the database user create SQL statements.
4. To create queries that view and/or manipulate data from one or more tables.

TO DO: 

1. Open Microsoft Access

Programs>Microsoft Access

2. Click the “Open an existing database” radio button and navigate to the database you created for Lab 15 and continued in Lab 16.

3. Open up your frmAdvisor form and go through the records displayed. Make sure you have at least 6 Advisor records. (Add a few if you do not). When you are done, close the form.

4. Do the same thing with the frmMajor form and make sure you have 7 or 8 Major records available. Close the form when you are done.

5. Open the frmStudent form. Add Student records, complete with Major and Advisor data, so that you have 10 records total.

When you are done, use the Close button that we added in Lab 16 to close the form.

Using the QBE

Now that we have some seed data in our database, we can request the display of data from different tables.

The important points to remember when creating a query are:

• Always include any tables that hold the data you want to see
• Always include any tables that are in the path to the data you want to see.
• When using a join operation, always indicate where the tables are related: Match the keys.

What goes where in the QBE:

Place the attributes (columns) of the table operation that you want to see displayed in the Field row. This is the SELECT clause of the SQL statement.

Place the tables that will be a part of the table operations here. This is the FROM clause of an SQL statement. You may have one or many tables included.

Check the Shows box if you want the attribute to display.

Use the Sort row to arrange the data in that field in ascending or descending order. This is the ORDER BY clause in SQL.

Indicate any constraints (criteria) of the table operation here. These are a part of the WHERE clause of the SQL.

Now, let’s query our database:

6. Go to the Queries window and double click on the Create query in Design View option.

7. You are asked which tables will be part of your query with the first window:

8. To decide which tables to include, you need to know what you are looking for. Here is your first query:

· Show me all students (just their first and last names) and the name of their Major

9. For this query, we need information from the Student table and the Major table. Select and add the Student table and the Major table. Then close the Show Table window.

Notice that because the Primary key for the Major table and the foreign key for the Student table have the same name, MajorID, Access has already created the join for us in the window.

If they didn’t have the same name, or they have the same name but are not associated then we have to create the join ourselves by dragging and dropping the attribute object from one table over to the related attribute object in the other.

Your TA will demonstrate.

10. To indicate which columns (attributes) you want to display in the query results, either double click the attribute name in the table, or drag the attribute name down to the field row of the QBE.

We have used the QBE to do a Join operation on two tables, Student and Major. This involved both the Select and Project table operation. The results of this join should be a display of all students, last name then first name, as well as their current major.

11. Click on the Datasheet view icon in the upper left hand or go to View>DataSheet View to display your query results

The number of rows displayed should be the same number as the records in your student table since you asked for all students and their major.

12. Go back to the Design View of the query. Can you figure out how the sort the records displayed alphabetically by last name?

13. Save your query. Call it vwStudentMajor since it shows us all students and their major. Close it when you are done.

14. Create another query in Design View.

• Show all students (first name and last name), their major and their advisor. Sort by major.
• Repeat steps 9 – 12.
• Remember: you need information from all three tables this time and you are displaying additional columns (fields, attributes)
• How many rows should be displayed? Think about what you are asking in your query.

15. Save your query results as vwStudentMajorAdvisor and close the query.

Now do the following queries and see if you can figure out how to display them as requested (Your TA will help you out). You do not have to save each query unless you want to keep it for future reference.

You shouldn’t have to double check to see if the query is correct, but if you aren’t sure of the join, look in your tables to see what information you should get.

16. Show all students with an ART major.

17. Show all advisors from the CSE department.

18. Show who is advises student number 4 (StudentID).

19. Show the major for a particular student in your student table (use the last name of one of your students as the criteria)

**BONUS**

20. Show all Advisors (Last Name, First Name and Department) and all the majors they are advising for. CAREFUL!!!!! This one can be tricky. You are only displaying the advisor names and the abbreviated major names. How will you get the correct information?

If there is time, your TA will show you how to create a report (a type of interface) to display the results of a query.

**Regardless of whether or not you are show how to do a report in lab, you will need to do one for your project. Use this database to practice and explore the Reports form further and to find out how to work the Label Wizard.**

How to keep your database size manageable:

***A quick note about file size:***

As you start working on your project, you will create forms. You will make mistakes and decide to delete forms and other objects from the database.

In other applications, this would usually mean your file gets a little smaller. It is NOT that way in Access.

Access “remembers” all the different forms and command buttons that you create-even after you delete them. If you don’t occasionally do a little “house cleaning”, your database file will grow immense!

To clean up your database and decrease the file size after you have been working in it for a while, compact and repair the database:

Tools>Database Utilities>Compact and Repair Database…

Do this to keep the database size manageable.