Is This Possible? - "Fuzzy Grouping"

Hello,



My problem is rather complicated and I am not sure if Access is even capable of addressing it. As I said, this is a bit tricky and I understand if no one is willing to tackle it, however, I would really appreciate it if someone could tell me it is impossible if that is the case. Thanks in advance.



I have attached a table to better explain my dilemma.




I would like to use the information in the “Category”, “Range Start” and “Range Stop” fields to generate new identifiers for each record in the table. The simplest criteria would be to assign the same novel identifier to two records if they have the same values in all of “Category”, “R. Start”, “R. Stop” (This is the case for the first two records.).

I am able to use this approach but would much rather use a more sophisticated set of criteria. Specifically, to be assigned the same ID two(or more) records must:

A) Belong to the same category.

B) Their ranges must overlap by more than (x)(Where x is some amount of overlap). E.g. Records 3 and 4 should be assigned the same ID because their categories are the same and their ranges overlap by 333 (13222-12889).

C) Finally, if A is satisfied and B is not then the then records could be assigned the same identifier if the difference between their ranges is less than some value (y). E.g. Records 5 and 6 should be grouped because A) is satisfied and their ranges are only 5 apart -> (119-300)…(305-700)



There is no limit to the number of records that may be assigned the same identifier, provided they satisfy criteria A+B or A+C.



Many thanks,



Matt