Is there a way to pull ranges with a query?

[calihiker]

Hi, I have a table that has a keycode field which is 4 characters long and is alphanumeric. The keycodes in the table are the ones that are already used and I have to find out what the longest contiguous range of unused keycodes is. Keycodes look like this for example "A1W9" and the sequence for each position would start with 0 to 9 then A to Z. Is this possible in a query to pull all ranges of unused contiguous keycodes greater than 100? I haven't searched for ranges before so any help would be very appreciated. Thanks!

Andre

 

[dalenz]

Hi Calihiker,

Part of the problem will be that because you have not used then they are not in the table.

Some quick thoughts:

In a group by query based on your table:

field Select:left([keycode],1)
sort acsending

This will select only the ist digit/alpha of your keycode and show if you have used a-z 0-9

I guess if you are looking for blocks of one hundred then its doubtful this will produce anything.

Select:left([keycode],2)would give you a longer list to search thru but may have more possibilities.

I will give this some more thought.

Dale

 

[calihiker]

Thanks for the response. I have already created both those queries and that has shown where the distribution is, but as you say, it shows no specific ranges.. I'm trying right now to assign values to each character in each position, but it is very tedious and slow going...
Any other suggestion would be great!

Andre

 

[dalenz]

Andre,

I was just geeting back to you to say scrub that post.

Quick thoughts thought too quickly.

Dale

 

[dalenz]

Andre,

OK, another thought.

Looking at a 2 character combination:

00
01
02
|
ZX
ZY
ZZ

A crosstab query with:

the row grouped on the ist character
the column the 2nd character
the value the character

should produce:
0 1 2 - X Y Z
0 00 01 02 0X 0Y 0Z
1
2
|
X
Y
Y Y0 Y1 Y2 YX YY YZ

Which should highlight unused values.

If there are 3 blanks together in a row that should be 108 free spaces.

This could be run selecting differing combinations of your keycode.

Dale

 

[dalenz]

Andre,

I have created a table with all of the keycodes, 1,679,616.

If you send me your email I can send it to you and you can run an unmatched, ie is null, of your table against it.

caniwi@ihug.co.nz

Cheers,

Dale