Creat a temporary table

[John Smith]

I have a table name NumberInUser with number from 1 to X

I have another table with OwnerNames. The OwnerName have a range of number
they can use.

Owner 1 = 1 to 49
Owner 2 = 50 to 69
Owner 3 = 70 to 89

I'm looking for a way to retrieve number that are not use in the table
NumberInUse.

For exemple, if the number 1 to 10, 15,17,19 are in use whene the Owner1 is
selected, the result should be 11-14,16,18,20-49

Anyone have an idea?

Thanks

JS

 

[Phil Freihofner]

Assuming the following table structures:

OwnerNames
(1) OwnerName
(2) MinNumber
(3) MaxNumber

NumberInUser
(1) Number

This query will give you the numbers you DON'T want to use:
SELECT Number FROM NumberInUser
WHERE Number >= [MinNumber] AND Number <= [MaxNumber]

[MinNumber] and [MaxNumber] can be supplied as parameters
to a query or as values you drop in if you are creating a
SQL String programmatically.

Now suppose you have a table of possible numbers, very
simply, a table with EVERY number from 1 to 89, structured
as follows:

PossibleNumbers
(1) Number

A query like this should do the trick:

SELECT PossibleNumbers.Number FROM PossibleNumbers
WHERE PossibleNumbers.Number >= [MinNumber]
AND PossibleNumbers.Number <= [MaxNumber]
AND PossibleNumbers.Number NOT IN
(SELECT NumberInUser.Number FROM NumberInUser
WHERE Number >= [MinNumber] AND Number <= [MaxNumber])

Note the section in parenthesis is the first query. I'm
pretty sure that AccessSQL supports the "WHERE ... NOT
IN ..." syntax, but not certain.

If you don't create the PossibleNumbers table, I think
you'll have to create the values by testing each possible
number via a FOR LOOP and placing your results in a
temporary table.

Good luck,
Phil Freihofner