Finding lowest integer in an Access Database

[pjholliday]

I am using a MS Access Database. Can anyone suggest some SQL to find the lowest unused number from one a column, say 'Quantity' for example? Thanks

[Lost User]

pjholliday wrote: Can anyone suggest some SQL to find the lowest unused number What do you mean by unused number? Do you mean that the number that is the lowest value in the table. If so, try the following statement: SELECT * tblTest ORDER BY tblTest.Quantity The above statement will sort the column Quantity from the lowest value to the highest value (suppose from 0 to 100). Then the first record of your database will be the lowest value.

---

A thousand mile of journey, begin with the first step. APO-CEDC Save Children Norway-Cambodia Office

[pjholliday]

What I mean by unused number is for example I have the following numbers in my column 1 2 3 4 6 7 8 I want it to return the value 5 because that is the lowest value that does not appear in the column. Is it possible to write a query to do that?

[Edbert P]

It is, if you have another table with integer values. The SQL will be like this:

SELECT MIN(tblInteger.Number)
FROM tblInteger
WHERE tblInteger.Number NOT IN
(
SELECT Numbers
FROM tblData
)

tblData will be the table you described while tblInteger will have integer numbers you use. So far that's the only way I can think of, maybe others can help? Edbert P. Sydney, Australia.