Print Page | Close Window


Printed From: Roger's Access Library
Category: Other Download Libraries
Forum Name: Tejpal, A.D.
Forum Description: A.D. Tejpal's Library
Printed Date: 25 Jun 2022 at 5:03pm
Software Version: Web Wiz Forums 12.03 -

Topic: Table_NormalizeMethods
Posted By: A.D. Tejpal
Subject: Table_NormalizeMethods
Date 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

Download File >>: -

Print Page | Close Window

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