Microsoft Access 2000

Tutorial 3 - Querying a Database

A query is a question you ask about the data in your database.  Access answers the query by returning data in a datasheet view.  This datasheet will include all the fields specified in the query, and it will include all the records that match the criteria (if any) specified in the query.  Some important things to note about a query:

·         When you save a query, you’re saving the question (the query design), not the answer (the datasheet returned by the query).  Every time you run the query, Access returns a datasheet that shows the current answer.  So if the data records have changed since the last time you ran the query, the resulting datasheet will reflect those changes.

·         The datasheet view of a query looks exactly like the datasheet view of a table.  But there are some important differences:

o        The datasheet view of a query can contain fields from multiple tables.  A table’s datasheet view shows only that table’s fields.

o        The datasheet view of a query shows only the fields specified in the query design.  A table’s datasheet view shows all the fields.

o        The datasheet view of a query shows only the records that meet the query’s criteria.  A table’s datasheet view shows all the records.

o        The datasheet view of a query can contain calculated fields.  A table’s datasheet view can show only the actual fields in the table.

Query Capabilities

Query Creation - Design View

You may create queries using wizards or manually, in design view. When using design view you construct a query using query by example (QBE). (AC 3.03)

Tables are added to the query in design view, in the upper part of the window above the design grid.

Fields are selected from those tables and placed in the design grid to specify the columns of the query. You can also specify criteria to limit the records returned by the query, as well as a sort order to specify the order in which the records will be displayed in the datasheet.

Table Relationships – As discussed in a previous lesson, Access is a relational database management system.  This means we can relate two tables to each other, joining them via a common field.  This relationship allows us to build queries that include fields from both tables.

Three types of relationships:

·         One-to-Many Relationship - A one-to-many relationship exists between two tables when one record in the first table (the “primary” table) can match zero, one, or many records in the second table (the “related” table). This is probably the most common type of relationship you will encounter in your databases.  An example would be our Customers and Orders tables from a previous lesson.  One customer can have zero, one, or many orders, but each order can have only one customer.  That’s the essential characteristic of a one-to-many relationship.  By linking the two tables by their common CustomerNum field, we can create a query that includes fields from both the Orders table and the Customers table.  Each row in the resulting datasheet will represent one record from the Orders table; it will also include information from the Customers table for the customer who placed that order.  The common CustomerNum field is what allows Access to know which customer’s data to include on the line with each order.

·         One-to-One Relationship – A one-to-one relationship exists between two tables when one record in each table always corresponds to exactly one record in the other table.  For example, a table about employee personal data and a table about employee payroll data would have a one-to-one relationship because each would contain one record for each employee.  In most cases, the data contained in two tables with a one-to-one relationship can be combined into one table.  However, there might be reasons why you would want to keep them separate.  For example, if the content of one table is confidential or changes more often than the data contained in the other table, or if the data in different tables needs to be updated by different departments, then keeping the tables separate might make sense.

·         Many-to-Many Relationship – A many-to-many relationship exists between two tables when multiple records in each table correspond to multiple records in the other table.  For example, there would be a many-to-many relationship between a table about employees and a table about projects if each employee could be working on multiple projects, and each project would involve multiple employees.  Access cannot work directly with many-to-many relationships, so they must be changed to two one-to-many relationships by use of an intermediate “junction” table.  The primary key for this junction table will be a composite of the foreign keys from the other two tables.

Referential Integrity – One problem with maintaining data in multiple tables in a database is the occurrence of mismatches between tables.  For example, we could have a record in our Orders table with a CustomerNum of 100, but not have a record in the Customers table with that same CustomerNum.  If we then try to print out a list of orders, including the name and address of the customer for each order, Access would be unable to find the customer who placed that order.  The principle of referential integrity states that every non-null foreign key value in the related table must match an existing primary key value in the primary table.  If you tell Access to enforce referential integrity between two related tables, it will not allow these table mismatches to occur.  It does this by using the following rules:

·         Access will not let you choose to enforce referential integrity between two tables if the two tables already violate it.  So if our Orders table already has a record for customer number 100, but our Customers table does not, we would get an error message if we told Access to enforce referential integrity.

·         In a relationship with referential integrity currently in force, Access will not let you add a record to the related table if there is no matching record in the primary table.  Likewise, Access will not let you change the foreign key value in a related table record to one with no match in the primary table.

·         In a relationship with referential integrity currently in force, Access will not let you delete a record from the primary table if the related table contains matching records.  (If you have told Access to use cascading deletes, it will delete the matching records from the related table.)  Likewise, Access will not let you change the primary key value in a primary table record if there are matching records in the related table.  (If you have told Access to use cascading updates, it will change the matching records’ foreign key values to match the new primary key value.)

Cascade Updates - When cascade updates is on, changes to the common field in the primary table will automatically be reflected in the related table.

Cascade Deletes – When cascade deletes is on, a record deleted from the primary table will cause all matching records in the related table to be deleted.

To define relationships in an Access database you use the relationship window. You may display the relationship window by clicking the relationships button on the database toolbar. (AC 3.09)  (Note that you can link common fields between two tables in a query design window; this will allow you to create queries using fields from both tables, but it does not create a relationship.  You must use the relationship window for that.)

To create a relationship, add both tables to the relationship window and click on the common field in the primary table and drag the mouse to the common field in the related table. This will bring up a dialog box in which you can further specify the relationship between the two tables (e.g., select referential integrity and cascade rules).

Sorting Data in a Query

You may sort data in a query on as many as 10 fields. The left most sort field in the query definition is the primary sort and is independent of the primary key for the table. You may sort ascending or descending by specifying the sort method in the design grid of the query.

Filtering Data

If you want to view a subset of the records displayed in a query’s datasheet view, you can filter out the records you don’t want to see.  The filter criteria you use here are not added to the query design grid when you save the query, but you can reapply the filter the next time you run the query.  To remove a filter and redisplay all records in the datasheet, click the Remove Filter button.  Access provides two datasheet-view filtering methods:

·         Filter by Selection – With a datasheet open, select one instance of the value you want included in the view, and then click the Filter by Selection button.  Only those records with that specific value (in that same field) will remain in the datasheet view.

·         Filter by Form – With a datasheet open, click the Filter By Form button; this brings up a blank view of the datasheet (one row with no data displayed).  Specify the value(s) you want to match in one or more fields, and then click the Apply Filter button.

Defining Query Criteria

You may type values or comparisons into the criteria fields for selected columns in your query’s design grid in order to return only specific matching records when you run your query.  (AC 3.20 shows the possible comparison operators that may be used.)  These criteria are retained as part of the query design when you save the query.

You can specify criteria in more than one column of the query design grid.  If you specify multiple criteria on the same row of the grid, Access will use a “logical and,” meaning the query will return only those records that meet all the criteria specified on that row.  If you specify multiple criteria on different rows of the grid, Access will use a “logical or,” meaning the query will return those records that meet one (but not necessarily all) of the criteria.

Dates specified as criteria are enclosed by #'s (ex #1/2/01#)

Performing Calculations

You may use the expression builder to define calculations based on fields in a table, with the results of the calculation returned as part of the datasheet view when you run the query. (AC 3.32)  Such a calculated field is given its own column in the datasheet view, just like a field taken directly from a table.

Aggregate Functions

On the Total row of the query design grid, you can specify aggregate functions to calculate statistical data on specified fields.  If the Total row is not displayed in the design grid, click the Totals button (Greek letter sigma).  You can choose among statistical functions such as Min, Max, Sum, Count, Average, Standard Deviation, and more.  If you specify a field as Group By on this Total row of the design grid, Access will group records with equal values in that field and show the statistical data for each group of records in the other fields.