In a table called "Steps", I have the following fields: ID, B4Me, Dsc,
-ID field contains a unique ID for each record -B4Me contains the ID of some other record in the table that MUST appear in a result set, BEFORE this record. B4Me may be null. This is called the "B4Me" order. -Records will be entered in random order, so Natural order cannot be relied upon as a substitute for B4Me I need an SQL statement that will return all records with a user supplied search word (such as "oil", or "glycol" or "micron") in the Dsc field, which could return a variable number of records, but what ever the number of records returned, the *order* must be such that each record is preceded by the record who's ID matches the B4Me value of a given record. Important Notes: - The SQL may return one or more records with a blank B4Me. In which case, Natural order can be used for those records, and they are all considered "First" or rather, "Before everything else". Once all the records with a blank B4Me are listed, the remaining must be in the B4Me order. - It is possible, though unlikely, that following the B4Me order will result in a circular reference. (Two or more records that reference each other, or the last record in a large result set, references the first (or any other in the result set) record in the B4Me field). In these cases, the SQL must either list all records until a circular reference is detected, then don't list the "offending" record, or display an error message, but it must not hang FB and get an "out of memory" error. Thanks in advance for any help you can provide.
