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:

Select Team.Name, Team_1.Name

From Team, Team As Team_1

The classic example of the winnowing approach is generating a schedule

Read more in the pdf file  Simplifying Complex SQL