MS Access SQL Query Tips

Choose Function in Microsoft Access

The choose function is similar to the Decode function in Oracle. Assume the same setup as the Dynamic Order By Clause example but add a 3rd option for sorting by Address.

See below how the choose function expands your capabilities:

Select M_Employees.Name, M_Employees.Emp_Number,
M_Employees.Address
From M_Employees
Order by
Choose(Val(Forms!F_Emp!Sort_Option),M_Employees.Emp_Name,
M_Employees.Emp_Number, M_Employees.Address);

Note: Entry in Forms!F_Emp!Sort_Option must be an integer and it is best to force it to a number with the Val() function. You can have up to 29 options and you can use the Choose Function in each part of the query except the From clause.

The choose function can be used in each part of a query, except possible the from clause.

Select Top Query

Select Top n Records Predicate Example
Have you ever had the need to get the 3rd record from a table using a query only?

Well, in case you want to know how to do it here is the solution:
1) Create a query (Query1) to get the top three records. We are interested in field called Submit_Date from a table called M_Revisions:
Select Top 3 M_Revisions.Submit_Date from M_Revisions
Order by M_Revisions.Submit_Date;
2) Use Query1 as input to a new query:
Select Top 1 Query1.Submit_Date from Query1
Order by Query1.Submit_Date DESC;

Now you have the 3rd submit date. Note that sorting the Query1 records in descending order makes the 3rd record go to the top.

More ......