Query_SplitToMultiRowsReMergeSorted
by AD Tejpal
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.
Version: Access 2000 file format.
Reference: DAO 3.6
You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=459
.
No comments:
Post a Comment