Table_NormalizeMethods |
Post Reply |
Author | |
A.D. Tejpal
Microsoft MVP Joined: 30 Jun 2008 Status: Offline Points: 192 |
Post Options
Thanks(0)
Posted: 14 May 2012 at 12:45am |
Table_NormalizeMethods This sample db demonstrates various methods for un-pivoting repeating columns of a table into rows. Following alternatives are covered: 1 - Progressive Append (One column at a time). For each of the above methods, two alternative styles are shown: (a) Appending the output to an existing table or (b) Making a new table. SQL for various queries is built through generic code in VBA module. For academic interest, query based method using DLookUp() function has also been included in VBA module. It makes use of Cartesian join with single field driver table, populated with names of pivot columns. Such a query can be built easily, without needing any VBA. However, the performance is comparatively slow, making it unsuitable for large data size. Tests conducted on Access 2003 desktop (Win XP) show that method 1 (progressive append) is most efficient while method 4 (recordset) is the slowest. For large data sets, execution time for recordset method can be as much as 9 times that of progressive append method. The other two methods: (3) - Union query and (4) - Cartesian join query using Choose function, though much faster than recordset method, are found significantly slower than progressive append method. Moreover, in make table mode involving large data sets, these two methods tend to attract system warning 3035 (System Resources Exceeded). Making a new table with normalized output is found comparatively faster than appending the output to an existing table. One of the contributory factors appears to be the fact that deletion of records in a table is much slower than deletion of the table itself. For facilitating comparative testing of various methods in a convenient manner, source data can be enlarged by clicking a command button. For each click, data size gets doubled by padding up with dummy data (self append). Execution time in each case can be recorded in the log table (depending upon the status of toggle button for logging). The log table can be displayed or hidden as desired by user. As and when it is felt necessary to revert back to original sample data, it can be done with the click of a command button. Some of the test results with different data sizes, as ascertained during development of this sample db, are included in table T_Log. Version: Access 2000 file format. Download File >>: uploads/37/Table_NormalizeMethods.zip
|
|
Sponsored Links | Want a good read? Try The Summer of His Life (available on Amazon!) |
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |