DatabaseChannel.com

The Access 2007 Pure SQL book contains 30 chapters with hundreds of solutions to frequent database problems and scenarios.

Free Access Articles and Tutorials

(along with a free Access database to work with. Download it here)

Use a subquery with NOT EXISTS to find unrelated records

What are cascade deletes, how to use them, and what they mean

Use a subquery to find above average priced products

Left Outer Joins or simply left joins

Inner Joins

Update records conditionally using the switch function

The GROUP BY clause with one aggregated field, and WHERE

Nested iif() functions vs the switch() function

Update records in a table using criteria from another table

The structure of a crosstab query in detail

How to use the Count() function with the DISTINCT predicate

Use the instr() function to find the position of any character in a string

Create a query with three parameter fields, one of them an expression

Delete records in a table based on criteria in a different table using a subquery

How to use the datepart() and format() functions to extract quarters from a date field

The Count() and Count(*) functions in comparison

The issue of uniqueness of values in crosstab queries

The iif() and switch() functions with two simple conditions

Use calculated fields with the iif() function

How to use the UNION operator with dissimilar fields

Define the order of the parameter prompts in a parameter query

Use the space() function to insert any number of spaces before or after a field

The GROUP BY clause with WHERE and HAVING

How to use the datepart() or format() functions to extract the week from a date field

Create and populate a table on the fly

How to use the TOP predicate with date data and the ORDER BY clause

How to use the inequality predicate with number data

Create a one to many relationship using pure SQL

Find duplicate records in a table based on the values of one field

How to Use the BETWEEN operator with dates

Understanding Referential Integrity

Use an index constraint to avoid null values in a field

The UNION ALL operator to include duplicate records

Use SELECT with INTO to create a backup table with a subset of data from the original table

How to Use the IN operator with Numeric data

Create a table defining the primary key as autonumber

Find duplicate records in a table based on the values of multiple fields

SELECT with WHERE, GROUP BY, HAVING and ORDER BY

Create multi-field indexes to avoid duplicate records

Use the ! and [] wildcard characters to create exclusion patterns

Looking for nulls using the IsNull() function

Looking for nulls using the iif() function

How to use the TOP predicate with numeric data and the ORDER BY clause

Use the AND operator corectly

Compare fields using the CBool() function

How to use calculated fields with multiple condtions

Use the left() function with concatenated fields to retrieve part of a field

Use ORDER BY to sort multiple columns

How to use the greater than predicate with number and date data concurrently

How to Use DISTINCT on Multiple Columns

The ? wildcard in combination with *

Combine the ORDER BY and TOP clauses to control the result set

How to Use the IN Operator with Dates and Subqueries

How to concatenate fields and separate them with spaces

Convert text date data to date data type

Use LIKE to find blank spaces in fields

How to use BETWEEN with the year() function

Use LIKE to test for the presence of numbers in a field

How to Use DISTINCT on one column