show numbers not in the list

[Guest]

I have a numeric field that is my primary key. It is employee number (3
digits) and when someone quits we delete them from the database. I need to
know what numbers are free for new employees. Is there a way to do this?

Thanks!

 

[Rick B]

Just FYI, it is customary to retain these people but mark them as inactive.
What if they come back? What if you want to run reports for this year? Are
their numbers used in any other parts of your database? In other words, do
you enter their numbers in an invoice as the salesperson?

If it were me, I'd mark them inactive instead (maybe even put in the
termination date). And leave them out there for at least a year.

Just my opnion.

 

[Guest]

I wish that was the case Ricky, but this is something that cannot be changed.
(long story) I am going to export the expired user to another table or
database for record keeping.

 

[Guest]

hey I just thought of something. What if I had table with all possible
numbers, 001 thru 100 in a table. Can I use a qry to compare the two and
list what numbers are not selected?

 

[Guest]

ok, figured it out. I made a table with numbers ranging 001 to 1000. I
named the field the same as the field in my employee id field. (don't know
if this really mattered) I then used the "find duplicates query wizard". It
asked me to select the two tables I wanted to compare with. It was really
simple after that.

 

[Guest]

ah rats. Got a problem. I have a command button to open the form that reads
from this query and I get the error "The table is already opened
exclusively...". How can I work around this? I need to have the table
opened while viewing these numbers.

 

[LGC]

The following query will return the first unused number in field1:

SELECT TOP 1 Table1.Field1+1 AS FirstUnused
FROM Table1
WHERE Table1.Field1 Not In (SELECT Table1.Field1 - 1
FROM Table1);

LGC

 

[Guest]

"LGC" wrote:
Question:
Can this query be adapted to return all unused numbers between 1 and the
last number in the table?