INF: How to Find the Next Available Unused Value in a Table

Article ID: Q128880
Creation Date: 12-APR-1995
Revision Date: 01-DEC-1996

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article describes an SQL Select statement you can use to return the next available unused value in a table. For example, if you have deleted numeric values in one of your tables and you want to fill in the unused numeric values, this SQL Select statement helps you find those values.

MORE INFORMATION

To create the SQL Select statement, do the following:

Open the sample database Northwind.mdb (or NWIND.MDB in version
```
    2.0).
```
Create a new query and on the View menu, click SQL.

Type the following SQL statement in the Select Query window:

NOTE: If you are using Microsoft Access 2.0, replace [EmployeeID] with [Employee ID] in the following SQL statement.

      SELECT TOP 1 [Employees].[EmployeeId] + 1 AS [Next Free Number]
      FROM [Employees]
      LEFT JOIN [Employees] as [Employees_1] on ([Employees].[EmployeeId]
      + 1) = [Employees_1].[EmployeeId]
      WHERE ([Employees_1].[EmployeeId] is Null)
      ORDER BY [Employees].[EmployeeId]

This SQL statement creates a self join on the Employees table. To get the expression in the join, you must edit the join in a SQL query window. Using the Top 1 expression (which is unique to Microsoft Access SQL), you can return the next available number.

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.