Query_SplitToMultiRowsReMergeSorted
Explanatory Notes

    This sample db demonstrates splitting of comma (,) or star (*) separated string elements into multiple rows followed by re-merging of these elements duly sorted, back into strings. It is a virtually codeless solution, but for use of two wrapper functions related to built in Split() function.

    For splitting the strings into multiple rows, a single field table (T_Ref) populated with numbers 0 to 10 is used in Cartesian join with the table holding source data. The sorted output is appended to table T_Jobs_Split, which in turn is used to generate the crosstab query followed by final select query, re-merging the sorted elements back into strings.

    Note: 
    (a) If the maximum number of elements in one string is likely to exceed 10, additional numbers can be added to table T_Ref. At the same time, pivot clause of crosstab query  and calculated field of final select query (based upon crosstab query) will need o be expanded to accommodate additional columns.
    (b) Use of number reference table (as compared to master job table) in Cartesian join with source table for generating multiple rows has the advantage that it eliminates the risk of inconsistent results arising out of partial match using Like operator. Moreover, it does away with the need of preparing such a table and keeping it up-to-date, apart from ensuring that there is no inadvertent discrepancy in its contents.
    (c) Table T_Jobs_Split has been used as it is a speedier alternative for generating rank numbers patient wise, using the self generated autonumbers. These rank numbers are used for generating columns via crosstab query. (Use of subquery based directly upon job values, dispensing with the interim table, is also found to work. However, it is comparatively slow).

Version: Access 2000 file format.

Reference: DAO 3.6