Finding a record that doesnt exist !

  • Thread starter Thread starter Mota
  • Start date Start date
M

Mota

Hi;
I have a table named DrugsTBL having a unique field named dID.Not all
natural numbers are used for drugs and some numbers are reserved for new
drugs.For example there is no record having did=29 or did=133.This numbers
are reserved for feauture use.
Now what i want to know is the first did that is free and in fact doesnt
exist in DrugsTBL.(i.e did=29).How can i find this number?
Thank you in advance for ur help.
 
Mota said:
I have a table named DrugsTBL having a unique field named dID.Not all
natural numbers are used for drugs and some numbers are reserved for new
drugs.For example there is no record having did=29 or did=133.This numbers
are reserved for feauture use.
Now what i want to know is the first did that is free and in fact doesnt
exist in DrugsTBL.(i.e did=29).How can i find this number?
Click to expand...

Here's a query you can use to find the lowest number not in
the sequence:

SELECT Min(DrugsTBL.did+1) AS FreeDID
FROM DrugsTBL LEFT JOIN DrugsTBL AS T2
ON DrugsTBL.did+1 = T2.did
WHERE T2.did Is Null;
 
Marsh

What a great solution. Works on Sql 2000 as written but chokes on A2K with
a "Join Expression Not Supported" error. However Wrapping the
DrugsTBL.did+1 in a Eval() function got it going.

SELECT Min(DrugsTBL.did+1) AS FreeDID
FROM DrugsTBL LEFT JOIN DrugsTBL AS T2
ON Eval(DrugsTBL.did+1) = T2.did
WHERE T2.did Is Null;

I haven't any idea what the Access Sql Interpreter didn't like, but the
Eval() made it go away. Hopefully I will remember to remember this solution
when and if I ever need to do something similar.

Ron W
 
Now that is really weird?!

I checked that query in both A97 and AXP and it worked fine
without modification in both versions.
 
Thank you for ur attention.

Ron Weiner said:
Marsh

What a great solution. Works on Sql 2000 as written but chokes on A2K with
a "Join Expression Not Supported" error. However Wrapping the
DrugsTBL.did+1 in a Eval() function got it going.

SELECT Min(DrugsTBL.did+1) AS FreeDID
FROM DrugsTBL LEFT JOIN DrugsTBL AS T2
ON Eval(DrugsTBL.did+1) = T2.did
WHERE T2.did Is Null;

I haven't any idea what the Access Sql Interpreter didn't like, but the
Eval() made it go away. Hopefully I will remember to remember this solution
when and if I ever need to do something similar.

Ron W
Click to expand...
 
Anyway,Thank you for ur help.

Marshall Barton said:
Now that is really weird?!

I checked that query in both A97 and AXP and it worked fine
without modification in both versions.
--
Marsh
MVP [MS Access]



Ron said:
What a great solution. Works on Sql 2000 as written but chokes on A2K with
a "Join Expression Not Supported" error. However Wrapping the
DrugsTBL.did+1 in a Eval() function got it going.

SELECT Min(DrugsTBL.did+1) AS FreeDID
FROM DrugsTBL LEFT JOIN DrugsTBL AS T2
ON Eval(DrugsTBL.did+1) = T2.did
WHERE T2.did Is Null;

I haven't any idea what the Access Sql Interpreter didn't like, but the
Eval() made it go away. Hopefully I will remember to remember this solution
when and if I ever need to do something similar.
Click to expand...
Click to expand...
 
Back
Top