How can I create a query so that it will display unused/available numbers

Example:

Table:
Computer Numbers:
1
2
3
5
7
9

Query:
Available/Unused Numbers:
4
6
8

If you have a table of all possible numbers, or can get it via a query, you can use an unmatched query. The query Wizard should walk you through setting it up.

I would personally add a field to your computer table, with a lookup table that has the choice to choose inuse or not, then in the criteria filter it to notinuse, not sure if it fits but hope it helps.


comp table
1 inuse
2 inuse
3 inuse
4 notinuse
5. inuse
6. notinuse
7. inuse
8. notinuse
9. inuse

Comp status table
1 inuse
2 notinuse

query
filtered to pull notinuse
would return what you're looking for.
4
6
8

Updating records is not the best choice as it can more easily lead to errors in the data. Better to have a history table with dates to indicate the effective date of a status. Much less chance for errors in the data when your only adding records instead of changing existing records.

In case you're interested, here is a query that will display the next available value (substitute the appropriate highlighted text):

SELECT Min(T1.MyField) + 1 AS NextFreeValue
FROM MyTable AS T1
WHERE NOT EXISTS
(SELECT T2.MyField
FROM MyTable AS T2
WHERE T2.MyField=T1.MyField + 1
)
;

From the sample data that you posted, this query would return 4.