find unused numbers 1

[dinger2121]

Hello,
I have been asked to provide a list of unused sku numbers for a project. There are 999,999 possible skus, with about 3000-5000 currently being used. I have a list of the ones that a being used in a table. The problem is taht they are completely random in order so there is no real easy way to get them. I have to provide a range of numbers to be used with a new project. I was hoping to be able to provide some kind of list with the best ranges to offer.

If anyone has any thoughts on the best way to do this, I would really appreciate some help.
I hope what I need is clear.

Thanks

 

[dhookom]

You can get all the un-used numbers by creating a table of numbers from 0 to 9:
[tt]
tbl09
============
Num
[/tt]

Then create a query to get all the numbers:

SELECT [tbl09].[Num]+([tbl09_1].[Num]*10)+([tbl09_2].[Num]*100)+([tbl09_3].[Num]*1000)+([tbl09_4].[Num]*10000)+([tbl09_5].[Num]*100000) AS Numbers
FROM tbl09, tbl09 AS tbl09_1, tbl09 AS tbl09_2, tbl09 AS tbl09_3, tbl09 AS tbl09_4, tbl09 AS tbl09_5
ORDER BY [tbl09].[Num]+([tbl09_1].[Num]*10)+([tbl09_2].[Num]*100)+([tbl09_3].[Num]*1000)+([tbl09_4].[Num]*10000)+([tbl09_5].[Num]*100000);

You can use this query with your existing skus and the type of join that includes all records from the query. Display the Numbers column where there is no matching SKU.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dinger2121]

I've already created another table with all skus possible, and populated a second column with all used skus. My question is how can I easily isolate the ranges of unused skus so that I can offer some good ranges to use?

Thanks

 

[dhookom]

You have never defined "good ranges". Are we supposed to know what defines a "good range"?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dinger2121]

Sorry, a good range would be a range of skus that are not used. If I used sku 112233 and then then next used is 112243, then the good range would be 112234 - 112242.

 

[dhookom]

I would write code the loop through all the records in your existing table in numeric order to look for gaps. The code could append records to a second table with the min and max values from the gaps. You could then query the second table and find the largest differences between the min and max values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SkipVought]

Duane,

Thanks for this tip! ==> [highlight]*[/highlight]

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}