Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Tejpal, A.D.
  New Posts New Posts RSS Feed - Table_NormalizeMethods
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Please consider donating $1 per sample downloaded, (find out why here)


 Post Reply Post Reply
Message Reverse Sort Order
A.D. Tejpal View Drop Down
Microsoft MVP
Microsoft MVP

Joined: 30 Jun 2008
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote A.D. Tejpal Quote  Post ReplyReply Direct Link To This Post Topic: Table_NormalizeMethods
    Posted: 14 May 2012 at 12:45am

Explanatory Notes:

    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).
    2 - Union Query.
    3 - Query using Choose() function (It makes use of Cartesian join with single field driver table, populated with sequential numbers).
    4 - Recordset Method (Looping through records).

    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.

References: DAO 3.6


Back to Top
Sponsored Links

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.