cascading queries

Some of my Access Database using readers may not know that a query can use another query for its source. Many of us get into the mindset that a query only reads from a table.

So, if I create a new query and choose Design View, the next screen that pops up is called the Show Table dialogue box. But look closer. It is not only showing Tables. There are also tabs for Queries and Both.

When is this useful?

One of the best examples is a library book borrowing database. Answer questions like this. Who was the first person to borrow the book, “How to Create a Query”?

Here are the tables of a simple version of this concept.

tblBooks
---------
lngBookID
strBookName

tblCustomers
-------------
lngCustomerID
strCustomerName

tblBorrowing
------------
lngBorrowID
lngBookID
lngCustomerID
dtmDateDue

The first thought may be to create a “Totals” query that asks for the Min function of the date field. Like this.

This does not work directly, as it returns the first time that each customer borrowed that book. We just want the very first customer who borrowed the book. We can visually see that it was David Martin, but we want the query to determine this by itself.

One solution to this problem follows.

Remove the Customer Name from the first query. Since it is not needed anymore, remove the Customer table as well. The query now returns just one record; that of the first time the book was borrowed. Save that query as qryMinQueryBook, or similar.

Now, start a new query. Add in the query you just created, as well as the Customer table and the Borrowing table. Link the 2 date fields.

This will produce a single record; that of David Martin.

One term that is used for this method is “Cascading Queries”. I have personally used this technique to multiple layers, and horizontally had more than 1 query as well.

In my next article I will show how simple cascading queries like the one above can been done in a single query.