Finding missing numbers in a sequence field

[Sandi]

Hello,

I have a table "tbl_talent_database" and one of the relevent fields (also primary key) is "TalentID". From all the TalentIDs, I would like to find the ID's that have not been used.

So say for example, My TalentID field might contain:

2
3
4
5
6
8



I want to build or create a query to identify 1 and 7 TalentID's have not been used.

I would like to create a query in design view so I would really appreciate if someone can help me out from there.


Thanks,

Sandi

[ajetrumpet]

is the fld an autonumber? if it is, missing numbers like that indicate deleted records, not unused numbers. If it's not, before you write a query like that, you need to determine HOW a number could be missing.

for instance, if you have a form to enter new records and one fld is autmatically set up to fill the pk field in using:

=dmax("field", "table") + 1

then the missing records would also indicate deletion. the important thing to do here first is to figure out IF your missing numbers really do reflect what you're looking for.

[Sandi]

I hope I make sense, there is very good reason I need to find the missing ID numbers is so I use every single number from 1.....to as many..The talent ID is the primary key for a profile of one person, once they move on to another level, i need to change the ID number to an ID number according to the website, so I want to be able to use the old ID at another point for someone els's profile.

And no the Talent ID field is not autonumber..just number field.