Lab 3.2

Queries – joining tables

First, let’s reflect what we learned from last lab – dealing with one table at a time – and learn a few more ways to specify criteria.

Queries with conditions

Operator / Description / Example / Question
Between ...And... / The same as >= v1 and <= v2 / Between #1/1/2005# And #12/31/2005# / List UPN and name of drugs priced between $1.25 and $1.50
In / Include values within the specified list / In (“CO”, “MN”) / List all drugs that belong to pregnancy category “A” and “X”
Is / Is Null
Is Not Null / All drugs that have interactions (meaning, the field is not null)
Like / Include a pattern to be matched partially by a text/memo/date field / Like “*195?” – born in the 1950’s
Like “L*” – names starting with “l” / All drugs that include -in- or
-on- somewhere in their names
See Table 3.4 on page 203 for more details

Queries with aggregation (or total queries)

What is the average price of all drugs that are available at 4Corners?
(Renaming still works)

The total count of drugs that have some kind of interactions

  • How does count work for fields that might have null values?
  • How to specify a condition in a Total query?
  • How to use an expression in a Total query?

Queries that join tables

Let’s be sure that no information is lost during normalization: Customer – Household (addr) - HealthPlan
(In another word, we want the decomposition process to be a lossless one.)

Let’s then try to list employees (names) and the training classes (name) that they participate: Employee – Class (?)

  • Messed up: too many records and everybody took the same classes
  • Read the Relationships diagram and add the EmployeeTraining class: everything turns out perfect
  • Press the add-tables button (the one with + and a table icon on it)

Queries with outer joins

Let’s list employees and the training classes that they participate; leave the class info blank for those who have not taken any classes yet: Employee – EmployeeTraining

  • First, let’s use an inner join as we did above: 32 records, with only those who have taken at least a course
  • Now, let’s modify the join type  to include every single employee: 43 records

Queries that use join, aggregation, and conditions

Let’s first get customer names, UPN and price of drugs prescribed: Customer – Drug or Customer – Prescription – Drug?

Let’s add a total cost (calculated) field, which displays the value of price * refills, for refills > 2 only

Let’s then calculate Due Amount for each household

  • add the Household table, and add the HouseId field
  • remove customer name fields, and the UPN field
  • click on the sigma () button
  • select Sum for the Due field, Group by for HouseId, and switch to the datasheet view

How to add the balance to each household?

How to display only the combined due amounts which are $50 or higher?

By default, the results are sorted by the group by field in ascending order. Sort the results by Due amount in descending order.

Additional Exercises for Lab 3.2

Clinic Customer Query: listing clinics and customers who visit those clinics

-What tables to include? We need to know

  • Clinic name
  • Clinic phone
  • Customer first and last name
  • Customer phone

-How to rename columns to (Clinic, Clinic Phone, Customer, Customer Phone)? For Customer column, customer names should be displayed as
Lastname, Firstname

-How to sort the results, by Clinic first, and by Customer within each clinic?

-How to display just one row for each customer?

[Total queries]

-How to summarize number of prescriptions for each clinic?

-How to summarize number of prescriptions for each customer from each clinic?

Drug and Prescription: how to check whether or not all drugs have been included in at least one prescription?

Employee and refill: how to check the total number of employees who had been involved in at least one refill?

Parameter query: how to list the number of refills for all prescriptions that include a certain drug?

 The UPN of the drug is the parameter

How to calculate Years of Service for each employee? (See pages 244-6 for details.)