Show numbers not used

[Seth Schrock]

I have a database that logs some information from another system that is entered manually by the user. Currently I have a subform showing the list of IDs used. Is there a simple way to show a list of numbers not used (so they are available) without having to create a table with every number from 1 to 1200? So for example, say I have the following IDs used:

1
2
3
5
8
9
10

I would want the following IDs to appear as not used:

4
6
7
11
...

Maybe something like

Between 1 And 1200 And Not In [Used IDs]

I know that wouldn't work, but that is kind of what I'm hoping for.

[zmbd]

I use a form of this to check for missing lab sequeces.
It's my template that I just change the access generics as needed.
it works well... I don't usually use the "TOP" predicate as I want all missing returned.

SELECT TOP 2000 somenumb+1 AS Expr1
FROM table104 AS T1
WHERE (((
   Exists (
      SELECT * 
      FROM table104 AS T2 
      WHERE T2!somenumb = T1!somenumb + 1))=False))
ORDER BY T1.somenumb;

Of course I'm just using the generics here for names.
This will return the missing numbers in sequence plus the next in line (so if you last highest entry was "15" this will show the missing entries and "16")

Why table104... that was my 104th attempt at this!
This took me close to 6 months to figure out, then my SQL-DBA put that EXISTS word on the table and smiled smuggly at me and walked away... and a week's worth of reading later... I was begining to hate SQL.

before that I was just using VBA to loop thru the records; however, that was so slow in the big backend that I would run the report in the evening before I left work... ROTFL... I've come a long way since then.

I'm sure there's a way to make this generate more numbers past the next entry

[Rabbit]

Doesn't that only work if you have at most 1 missing in between numbers? What if you're missing more than 1 consecutively. For example, you have 1 and 4 so you would want to return 2 and 3.

[jforbes]

This is a fun structure that uses a recursive Common Table Expression, which is a very mysterious and powerful device of TSQL. Unfortunately, you will need to be on a MS SQL backend to use it. If you are, you would need to replace LineItem with your table name and LineItemID with the column you want inspect for missing numbers.

;WITH CTE_MissingID (MissingID) AS
(
   SELECT n1.LineItemID + 1
   FROM LineItem n1
   WHERE NOT EXISTS (SELECT NULL FROM LineItem n2 WHERE n2.LineItemID = n1.LineItemID + 1)
   AND n1.LineItemID < (SELECT MAX(LineItemID) FROM LineItem)
   UNION ALL
   SELECT cte.MissingID + 1
   FROM CTE_MissingID cte
   WHERE NOT EXISTS (SELECT NULL FROM LineItem n WHERE n.LineItemID = cte.MissingID + 1)
)
SELECT MissingID
FROM CTE_MissingID
ORDER BY MissingID

[zmbd]

Rabbit,
You're correct.. I'll have to go back and double check some records... guess I never bothered to check this as I run it recursively until only one record is returned and the report only needed to show where the breaks occured...
hmmm.
Thought I had that.
Guess one could use a 0-9 number table and use a query for expansion to generate the number list and then join against that to return the missing entry numbers?
This way one doesn't have a huge number table in the database, just one in memory.


jforbes,
That would be a fine solution if a sql server was mentioned in the original post.
What about somehting for MS Access?

[NeoPa]

ZMBD:
What about somehting for MS Access?

I'm sure if JForbes had anything appropriate for Jet SQL he would have shared it already. Technically, Access is very useful as a FE for SQL Server so, while it won't work in all circumstances, it is still of use as an Access answer.

Personally, with the stricture that it mustn't rely on a table of items to select from, I have no SQL solution I can offer. One could, of course, populate a table with the required values using VBA code quite easily. Unfortunately, this logic couldn't be used in a query except after the table is populated :-(

[zmbd]

NeoPa,
J's would work as a pass-thru (^_^)

Seth,
My original code might work for something else?
If you only need the next lowest number in the series that is available then add the TOP 1 predicate, if you need the list then, despite my best efforts, I have to agree with NeoPa that either we have to go back to VBA or use a some sort of table for SQL in Access; however, we need not use that huge table, a table with 0 thru 9 can be manipulated to generate the expansion via a query. This small table should meet with your request in OP.

(ok, inspired by A.Browne's scheduler that used the huge table... and both NeoPa's and Rabbit's work with these types of tables... the following occurs to me, if we Cartesian product a table with ten elements against itself, that's 10^2 records... however, we want numbers, so if we use the digits and 10^(n-1) sum them and then use the same table against itself enough times in theory we could the correct number of records without having a real table holding 10^3+ records, just ten records...

So the expansion SQL would be:

SELECT [tbl_expansiondigits_1]![Digit]
   +[tbl_expansiondigits_2]![Digit]*10
   +[tbl_expansiondigits_3]![Digit]*100
   +[tbl_expansiondigits_4]![Digit]*1000 
   AS Expand
FROM tbl_expansiondigits AS tbl_expansiondigits_1
   , tbl_expansiondigits AS tbl_expansiondigits_2
   , tbl_expansiondigits AS tbl_expansiondigits_3
   , tbl_expansiondigits AS tbl_expansiondigits_4
WHERE ((([tbl_expansiondigits_1]![Digit]
   +[tbl_expansiondigits_2]![Digit]*10
   +[tbl_expansiondigits_3]![Digit]*100
   +[tbl_expansiondigits_4]![Digit]*1000)<=1200));

So like NeoPa pointed out you need the seed table which I named tbl_expansiondi gits filled with the numbers 0 thru 9

Made this right in the Query-GUI-Editor :shrug:
Take the WHERE clause out and you goto 9999.

So now we only need to return the numbers in the expansion query that have no matching numbers in the recordset

Using the basic query wizard for non-matching fields (I know... lazy coding to use the wizard (^_^) however, the SQL wizard isn't too horrible for this ):

Say from a table named data using the primary key.

SELECT qry_12Kexpansion.Expand
FROM qry_12Kexpansion 
   LEFT JOIN tbl_data 
   ON qry_12Kexpansion.[Expand] 
      = tbl_data.[data_pk]
WHERE (((tbl_data.data_pk) Is Null));

(of course, you could sub the SQL from the expansion query into the find non-matching query... however, just for ease of following I didn't do that here. Instead I named the first query qry_12Kexpansio n and stored it to qrydefs)

So if you had, [tbl_data]![PK]= {1,2,3,5,6,7,12 ,100,1200}
Then the above should return [qry_12Kexpansio n]![Expand]={0,4,8,9,10,11 ,13,14,15...98, 99,101,102,102. ..1198,1199}

To rid yourself of the zero

WHERE (((qry_12Kexpansion.Expand)>0)
    AND ((tbl_data.data_pk) Is Null));

I'll have to try this against the lab data Monday. My tiny set here ran so fast against the 100 entry table of test data that it's not a real test

and that should satisfy the OP as we return all of the unused record ID's using a small stored table with a query workaround for what would normally need the huge table like A.Browne's

I bet this isn't original... just at work the Internet is so locked down as of late I am surprised I can even get the company site to load (and half the time is doesn't load either because some IT will link to something outside the proxy limits!)

OK, my contacts are blurring and the littlest kids need to be chased back into bed as they have school tomorrow, my oldest has already crashed for the night.

[twinnyfo]

Seth,

I am curious as to where the number 1200 comes from? Are there 1200 IDs listed somehere so that you know what they are? Or is 1200 just a number of users. My thought is if you have this list of 1200 somewhere, the solution might be pretty simple....

[Seth Schrock]

We have about 100 employees and 11 branches. Branch 1 generally gets IDs 1-199 (most employees at this branch). Branch 2 gets IDs 200 - 299. Branch 11 has IDs 1100 - 1199. Round figures makes it 1200.

I've been really busy, but I hope to try the suggestions later today.

[twinnyfo]

First, I am not sure this is a "good" suggestion, but maybe you should have a table with 1200 records, listing all possible IDs, indicating the Branch for which that ID is reserved, then the specifics on who the ID is issued to.

This puts everything in one place, but when there are unused IDs, then you have a table with a bunch of records and nothing in it.

But, this would give ultra easy access to the solution--just query any blank IDs for the given branch.

But.... then you have to create that table and add the 1200 records.

[Seth Schrock]

I was aware of that option, and while it is the best in terms of ease on the database side, it is the worst in terms of use (in my opinion). That is why my OP says "without having to create a table with every number from 1 to 1200?" But yes, this would be the simplest to code.

[twinnyfo]

Seth,

Either way, let us know how you crack this nut!

[Seth Schrock]

Bingo. Thanks Z. I get all the values available.

I don't mind having a table of just the 0 through 9. I just didn't want to have the 0 through 1200.

Thanks everyone for your help.

[zmbd]

Yea...
You might keep in mind that first code block too.
I noted in post Post #9 that you assign in blocks and that SQL might be helpful for finding the next lowest number at each breakpoint as Rabbit pointed out.

[twinnyfo]

Hey, Z,

Re-running through your solution, and I know you say it is not original to your self, but it actually is quite brilliant. Great job--and now I have another tool in my kit, just in case I ever need it!