Not In List Problem?

  • Thread starter Thread starter Randy Fritz
  • Start date Start date
R

Randy Fritz

Hello NG

I have a table for employee data and each employee is given a number
between 1000 and 1500. As an employee leaves we move them employee to a
different series of numbers to free up the numbers available. I want to try
to simplify the process of determining a new employees number by making a
list box of all available numbers. How do you right a query to SELECT
Numbers FROM Between 1000 and 1500 WHERE Numbers NOT IN ( SELECT Numbers
FROM EMPTABLE WHERE Numbers Between 1000 and 1500;);
TIAFAH
Randy
 
Never Mind I figured it out.

For those interested her is the query

SELECT Val([Employee Information Table]![Lo Bay Number])+1 AS [Numbers
Avail]
FROM [Employee Information Table]
GROUP BY Val([Employee Information Table]![Lo Bay Number])+1
HAVING ((((Val([Employee Information Table]![Lo Bay Number])+1) Between 1000
And 1500) And (Val([Employee Information Table]![Lo Bay Number])+1) Not In
(SELECT Val([Lo Bay Number]) AS [Numbers Used] FROM [Employee Information
Table] WHERE Val([Lo Bay Number]) Between 1000 and 1500;)));

Thanks for anyone who may have been trying to help
 
How to Find a Missing Sequence Number:

I am looking for a way to query up check numbers that are NOT
present in a list of sequential numbers.

In other words, I have a list containing: 1, 3, and 5 so
the results should be: 2 and 4.
===========================================
I would most likely just use Michel's Iotas table.
Here is Michel's simple method for building it:

********* Quote ************
Making the table Iotas:

Make a table Ds with field d, 10 records,
values for d = 0 to 9.

In a make table query,
bring Ds table 3 times so you have
Ds
Ds_1
Ds_2

In a free column type:

Iota: Ds.d + 10*Ds_1.d + 100*Ds_2.d

Generate the table Iotas and make Iota a primary key.
******** End Quote ******************

This will give you an Iotas table where Iota
goes from 0 to 999. If you need to go to 9999,
bring Ds one more time (Ds_3),

Iota: Ds.d + 10*Ds_1.d + 100*Ds_2 + 1000*Ds_3.d

If you bring Ds into to your make table query a 5th time
(remember there are no joins involved),

Iota: Ds.d + 10*Ds_1.d + 100*Ds_2 + 1000*Ds_3.d
+ 10000*Ds_4.d

you get Iota going from 0 to 99999.

(I think you probably see the pattern)

Once you have created an Iotas table that goes
from 0 to your highest expected number in the
list (table?), then LEFT JOIN the Iotas table
with your other table on the Iota field equal to the
number field in your other table returning the
Iota WHERE number field IS Null.

Assuming Iota only goes to your largest number,

SELECT Iotas.Iota
FROM Iotas LEFT JOIN tblOther
ON Iotas.Iota = tblOther.NumberField
WHERE tblOther.NumberField IS Null;

above query will return the numbers that are
missing in the sequence.

If you won't always know what the
highest number is, then first run a query
that selects all from Iotas up to the max
number in your other table, then left join
this query to your other table as above
(or just use this query stmt as a subquery
in place of Iotas in query above).
 
If you create a table with a single field named MyKey and fill it with all
possible consecutive numbers
then you can write a SQL statement like this to join it to your table named
MyTableData filled with data and return the "gaps" or available numbers or
missing numbers. (Whatever you decide call them.)

This query also uses a form to collect the Start and End range.
My table AllNumbers goes from 1 to 30,000.

SELECT DISTINCTROW [AllNumbers].[MyKey]
FROM [AllNumbers]
LEFT JOIN [MyTableData] ON [AllNumbers].[MyKey] = [MyTableData].[MyKey]
WHERE ((([AllNumbers].[MyKey]) Between [Forms]![Report Menu]![Start] And
[Forms]![Report Menu]![End]) AND (([MyTableData].[MyKey]) Is Null));




This gives the first missing number after each real value in a sequence.
If you use MIN then you should always get the first missing number.
SELECT T.[Envelope #] + 1 AS NextNumber
FROM [Family Data] T LEFT JOIN [Family Data] T1 ON T1.[Envelope #] =
T.[Envelope #] + 1
WHERE T1.[Envelope #] IS NULL
ORDER BY T.[Envelope #] + 1
 
Back
Top