This is a part one of two-part on how you can make your queries much more powerful. In first part, we look at whether we’re in or not. Part two can be found here.

The IN clause is a great tool to have in your arsenal, it can allow your forms to be editable or filter forms using .

Are you IN or are you out? (Not IN)

What is the IN clause?

The IN clause is a member of the SQL language syntax and can be used in your WHERE clause to specify which data to include or exclude. (There are other uses too, as discussed here).

Two Syntax Alternatives

You can use the IN clause by specifying the criteria to include in your query. For example, let’s say you want a list of all orders in IL, WI and MN. You could do it this way:

SELECT *
FROM tblOrders
INNER JOIN tblCustomers
ON tblOrders.CustomerID = tblCustomers.CustomerID
WHERE tblCustomer.CustomerState = 'IL'
OR tblCustomer.CustomerState = 'WI'
OR tblCustomer.CustomerState = 'MN';

The problem with the above statement is adding a ton of states makes for a long SQL Statement. Instead you can use the IN clause as a shortcut for the equivalent expression above:

SELECT *
FROM tblOrders
INNER JOIN tblCustomers
ON tblOrders.CustomerID = tblCustomers.CustomerID
WHERE tblCustomers.CustomerState IN('IL','WI','MN');

Power Up

But what if you don’t want orders from those states? You can combine IN with NOT like this:
SELECT *
FROM tblOrders
INNER JOIN tblCustomers
ON tblOrders.CustomerID = tblCustomers.CustomerID
WHERE CustomerState NOT IN('IL','WI','MN');

Super Charge

The above examples are great ways to use the IN clause, but you can Super Charge your code by using a select statement instead of listing values like this:
SELECT *
FROM tblOrders
WHERE CustomerID IN(
SELECT CustomerID
FROM tblCustomers
WHERE CustomerState IN('IL','WI','MN')
);

Notice how I dropped the INNER JOIN from my SELECT statement and instead I’m using the IN clause to retrieve all orders for those customers who belong in the states in question.

Use IN to query on tables not in the select statement

There are many situations where you need to avoid a JOIN on your select, here are two:

  • You need users to edit data in your form and using a JOIN will make it read only.
  • One or more tables are not stored together. For example: the Order table might be on SQL Server and the Customer table stored locally in Access. Such a join would be slow and not optimal.

Use IN to filter your form

One of my favorite techniques is to filter a form using the WHERE clause of the DoCmd.OpenForm statement. For example, the other day I was building a dashboard for a banking client that included a count of delinquent loans in the system. I made the number clickable, allowing users to launch the frmLoans form with the list of delinquent loans with the following line:

DoCmd.OpenForm _
FormName:="frmLoans", _
WhereCondition:="LoanID IN(SELECT LoanID FROM vw_DelinquentLoans)"

Note that the data is contained in a SQL Server view that’s linked in Access front-end. Instead of joining the view which may cause sub-optimal performance, the IN clause ensure that the list of LoanIDs is evaluated once.

Use IN to avoid duplicates in your queries that has a GROUP BY clause

Many times you will have a query that contains a GROUP BY clause for which you need unique records, but you are getting multiple records due to joins with other tables. Consider using the IN clause instead of joining on one or more tables.

So are you IN or are you OUT?

Stay tuned for the part two!