FIT 100

Lab 18: Get me to the Good Stuff!

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

Autumn 2001

Reading for Lab:

· Review Chapter 16 of FIT

Introduction:

Today’s lab looks at an additional component of the Data Services 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)

Data Services

The main unit of data services (data storage) is a Table. Data storage allows for persistent data (the data stays even when the power goes off). A table is a collection of data about a specific topic.

Tables organize that data in columns (called attributes) and rows (called records). One of the attributes (columns) of the table is almost always a number data type that is used as a primary key. A primary key is used to uniquely identify the data in a particular row. Primary key values must be unique and not null.

Data Services also includes the storage engine and the query engine which deal with the saving, accessing and manipulation of the data. A Query is used to access and manipulate the data. 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. Download the Lab15.mdb from Dante to the Desktop.

2. Open Microsoft Access

3. Click the “Open an existing database” radio button and navigate to the database you created for Lab 15 and continued in Lab 17: Lab15.mdb

4. 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.

5. 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.

6. 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 17 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.

What goes where in the QBE:

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

Place the attributes (columns) of the table operation that you want to see displayed in the Field row. This fills in the SELECT clause of the SQL statement (if you have checked the Show box).

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

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 Show box if you want the attribute to display.

Now, let’s query our database:

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

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

9. To decide which tables to include, you need to know what you want to find. Here is your first query:

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

10.

Notice the Primary key for the Major table and the foreign key for the Student table are already associated in our Relationships window, so Access has created the join for us in the query window.

If the relationships had not already been established, you would drag and drop the attributes to the appropriate field in the other table and force a join.

Your Instructor will demonstrate.

For this query, we need information from the tblStudent table and the tblMajor table. Select and add these two tables. Then close the Show Table window.

11. 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.

12. 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.

13. Go back to the Design View of the query. Can you figure out how to sort the records and display them alphabetically by last name?

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

15. 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.

16. 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. Save each query 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.

17. Show all students with an ART major.

18. Show all advisors for students with an INFO major.

19. Show who advises student number 4 (StudentID).

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

21. Your TA will be checking your queries before you leave. Make sure you show one query from steps 17 through 20.

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, etc 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. ESubmit will only accept files up to 1 MB in size, so you must keep your database under control for Project 4.