Indexing Hook Numbers

Indexing Hook Numbers

Indexing Hook Numbers for Maximum User Efficiency
Modified 01/03/2006 04:37 PM Hit Counter

The Existing Database Structure. (This is a description of long-standing practices of the lock shop.)

There are two different kinds of keys in the HOOK field, regular and other.

Regular hook numbers

	Normally used only for door keys
	Are integers greater than zero
	Correspond to key cabinet physical hooks
	Numbers are assigned by the lock shop
	May have an alphabetic suffix
	Examples: 39, 10065, 6271A

Other keys

	Used for other than door keys
	May contain letters and/or numbers
	Do not correspond to physical hooks
	Designation assigned by manufacturer
	Examples: 47, H1848, C201A

Special Cases (no key specified)

	.= indicates a passage set on the door, no key required from either side
	.- indicates no door (there used to be a door there, but it's been removed)
	.# indicates no door (there used to be a door there, now it's permanently closed)
	There may be some others, but I don't remember them at this time (hey, it's been 2½ years since I retired.)

To differentiate between the regular and others, and to aid in sorting, all "other" keys are preceded by a dot (.) and followed by an asterisk (*). Examples: Yale #47 (an electrical box key) is designated in the database as .47* ; elevator key H1848 is represented as .H1848*

Problem 1: Undesirable sorting order in the location database

Hook is a character field in a database containing location data (building, room, master, was [previous hook], dept., description, etc.

Unacceptable Sorting Result
.47*
.H1848*
1899
19
190
1900
1901
2

The normal method of sorting (left to right, character by character) for data entered in random order produces this.

This bad result is because there's no respect for the value of the number; 2, 19 and 190 should clearly come before 1899.

This result could be corrected by adding leading zeros, but this is unacceptable because:

	Key cabinet hooks have no leading zeros, and existing keys don't either..
	Existing data would have to be modified (adding four or five zeros before all hooks lower than 100, three before all less than 1000, etc.
	New entries would have to be properly "leading-zeroed" to prevent wildly-inaccurate sorting.
	Many more keystrokes and probability of errors with each entry.

Desired Result
.47*
.H1848*
2
19
190
1899
1900
1901

When I found this problem (in the early 1990s) I decided to index on the value of the hook number.

dBase and FoxPro both had a function called VALUE(), which converted a character string to its numerical equivalent.

The indexing command was something like INDEX ON VALUE(HOOK)TO [index name], which mostly worked but gave undesired results for the manufactures' numbers which can contain alpha characters. After experimentation I added the dot to make the value of manufacturers' numbers less than 1 to move them to the beginning of the sort.

My final indexing command was something that probably shouldn't have worked (because it produced a mixed [character and numeric] result) but did exactly what was desired. It used an IIF command (the interactive version of IF) which says "if x is true, do this, otherwise that".

It went something like: INDEX ON IIF( VALUE(HOOK<1), HOOK, VALUE(HOOK))

which means "IF THE VALUE OF HOOK IS LESS THAN 1, INDEX ON HOOK [the standard left to right character method], OTHERWISE INDEX ON THE NUMERIC VALUE OF HOOK." This placed all non-hook numbers in the front of the index in proper order, the rest fell into their proper order further back. This method also places each record in its proper place regardless of leading zeros, which is desirable.

I don't know if Access can provide such a complex function, but I strongly recommend using the numeric value of hook when setting the order, and if necessary letting the non-hook numbers fall to beginning of the sort in any order. One possibility: create a new field, calculated to be IF(VALUE(HOOK)<1,HOOK,VALUE(HOOK)), and sort on that field (I don't know Access, so the VALUE function is probably something different, but that's the idea.)

Problem 2. The Hook database isn't indexed by physical key description. Hook database contains fields of hook, keyway, pinning, mast. It is normally sorted by hook, but there has to be another method of sorting based on keyway+pinning. This is used when you have an unmarked key and wish to find out its hook number, or when looking for a new pinning not currently existing in the system.

My method was INDEX ON TRIM(KEYWAY)+" "+HOOK TO [index name]. This allowed me to input keyway (1-4 characters), space, pinning and find what I needed, but I think that skipping the fanciness and doing a simple Access equivalent of Index on keyway+pinning would work just fine, requiring Darwish to pad out short keyways with spaces when necessary.

If you have any questions, please don't hesitate to email me at ; I'm certainly willing to help you with this. I understand how confusing locksmithing can be, and I'm willing to interpret between computerese and locksmithese.

Ralph