Complex SQL to Simple SQL

Access developers often find SQL bewildering: While simple things are easy in SQL, as you move up to more complicated problems, SQL statements can quickly become intimidating. Peter Vogel looks at some strategies for solving tough problems with SQL.

WHEN working with complicated problems, you often find yourself working with complicated SQL. One of the difficulties that developers have when writing SQL is the steep learning curve. While simple things are easy in SQL, once you move beyond retrieving groups of records things get difficult very quickly. SQL can become complicated very quickly.

To succeed with SQL, the most important thing you need to learn isn’t new SQL commands but a new approach. Most Access developers use a “procedure-oriented” approach to problem solving: Do this, then do this, then do this. This approach works well when writing VBA code. Unfortunately, SQL is not procedural but is set-based. Many developers, when working with SQL, develop a procedural algorithm (“If I were doing this in VBA, I would…”) and then try to convert that procedural algorithm to a set-based algorithm. This only complicates the problem. The first step in handling complicated SQL is to simplify the way that you think about the problems you want to solve with SQL.

A set-based approach to solving problems begins by generating all the rows that contain the solution and then removing those rows that aren’t part of the solution (I’ll call this second phase “winnowing the results”). Generating all possible rows is easy in SQL: If you join two tables without a Join or corresponding On clause (or a Where clause), you’ll get every combination of every row in the two tables. That’s what this SQL statement does:

The classic example of the winnowing approach is generating a schedule

 

Read more in the pdf file  Simplifying Complex SQL

Your download file is called Vogel_Simplify_Complex_SQL.accdb

About Peter Vogel

After 10 years of editing Smart Access, Peter continues to develop with Access (though he also does a lot of .NET stuff). Peter is also still editing other people's article--he even self-published a book on writing user manuals ("rtfm*") with a blog here.
This entry was posted in Queries. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.