Field setup for series of codes?

[gully]

Hi: I'm setting up a relatively simple database to keep track of particular codes assigned to specific people. All that is necessary is the usual contact information plus a list of these codes (5 or 6 alphanumeric characters). Each contact may have tens to thousands of these unique codes assigned to them, usually in blocks of 5, 25, 50 or 100.

For example, Jon Smith may be assigned the following code ranges: AA100-AA124, ABC150-ABC199, and 200GH-249GH. Once entered, I will need to be able to run a search for codes within those ranges, like AA115, ABC165, or 215GH and pull up Jon Smith's record.

Does this make sense? It seems like it should be a relatively simple matter but I have no idea how to implement this. Any assistance will be greatly appreciated - thanks!

Edited January 5, 2006 by Guest

[comment]

There should be a record for each assigned range, so you should have two files: PEOPLE and RANGES. The RANGES file needs basically only 3 fields: Low, High and PersonID (to relate back to the person to whom this range has been assigned to).

To find a code, you would search the RANGES file for records where:

Low ≤ code

AND

High ≥ code

Although this scheme might work quite well with text strings (if some limitations are observed), a safer approach would be to translate the codes into numeric values, and search these calculation fields instead.