Search for number according to Validation Rule

[geek.shrek]

Hi,

I have a TableA with coloumn "ID" and "Name".
The "ID" have a validation rule "Between 1 AND 10".
Currently I have ID "1,3,4,8" in the database.

I would like to search which ID number that missing.
The result should be ID "2,5,6,7,9,10".

Does anyone have any idea how to query this


Thanks,

 

[MSAccessRookie]

I am not Sure SQL is supposed to do this BUT...

I am not Sure SQL is supposed to do this (I am sure that Visual Basic would be great here) BUT...

The following complicated query (substitute Table and Column names where applicable) gets you what you want.

Select * from
(
SELECT 1 AS It from Table7
UNION SELECT 2 AS It from Table7
UNION SELECT 3 AS It from Table7
UNION SELECT 4 AS It from Table7
UNION SELECT 5 AS It from Table7
UNION SELECT 6 AS It from Table7
UNION SELECT 7 AS It from Table7
UNION SELECT 8 AS It from Table7
UNION SELECT 9 AS It from Table7
UNION SELECT 10 AS It from Table7
)
Where It NOT IN
(Select ID From Table7)
;

 

[khawar]

Check out the attached sample

 

[MSAccessRookie]

Check out the attached sample

Great Idea. It also resolves the problem about what to do if there is a need for more than 10 IDs

 

[khawar]

If you have to compare more than 10 or say in thousands you dont have to write them in table just use numbers 0 to 9 and use cartesian products (refer to my other posts i have used cartesian product in many attachements)



Regards

Khawar