The QBE Window: Training Wheels for Access Developers

"To alcohol!  The cause of–and solution to–all of life's problems."

Homer Simpson could just as easily have been talking about the Query-By-Example (QBE) window in Microsoft Access.  How so?

The Query-By-Example Window

The QBE window is Access's visual query designer.  It's the window that appears when you right-click on a query definition and choose the "Design" option:

The Good

The QBE window offers a graphical way to build queries.  For users that are new to relational databases, it offers an intuitive way to join tables without having to learn SQL syntax.  For experienced developers, it's faster than typing out an SQL statement by hand.

The QBE window is a great feature.

The Bad

The problem with the QBE window is that it hides some of the most powerful features of a relational database: namely, the different ways one can relate data among tables.  And, by hiding these features, the QBE window stunts your growth as a database developer.

While the QBE window supports inner and outer equality-based joins, it does not support other key features:

• UNION queries
• Greater than / Less than joins
• Joins based on calculated values

Example, Please

What if you want to identify pairs of records in the same table where the value of one record is exactly half the value of another record?

One way to do this is to join the table to itself.  We alias the tables to improve readability.  We join on the ID field and the Amount field.  But instead of using the familiar equal sign, we set a condition to exclude matching ID values.  We then compare the values of the Amount field to other records in the same table but with double the Amount.

Here's what the query looks like:

SELECT A.ID AS ID_A
 , B.ID AS ID_B
 , A.Amount AS Amt_A, B.Amount AS Amt_B
FROM Table1 AS A 
   INNER JOIN Table1 AS B 
   ON (A.Amount = (B.Amount / 2)) AND (A.ID <> B.ID);

And these are the results it returns:

But watch what happens when we try to switch to Design View with the above query.  Access complains:

Bottom Line

If all of your query development is taking place inside the QBE window, then you are not reaching your full potential as a database developer.

External References