New Sample: TableNormalizationByPureSQL

by A.D. Tejpal

This sample db demonstrates pure query based solution for normalization of data held by a non-normalized table (T_Source) and posting the converted contents to destination table (T_Normalized).


Steps:

1 - Create the empty destination table T_Normalized with a structure identical to that of table T_Source, but without the non-normalized fields (e.g. Red, Green, Blue, Yellow in this sample).

2 - Add two new fields to the newly created table T_Normalized. One meant for holding the names of non-normalized fields and the other for holding corresponding values. In the current sample, these two fields are named Color and Stock respectively.

3 - Create an auxiliary table named T_SourceFieldsConverted having a single field meant for holding names of non-normalized fields. In the current sample, this field is named Color. Populate this table with the names of non-normalized fields (i.e. Red, Green, Blue, Yellow in this sample).

4 - Execution of append query Q_AppNormalized will populate destination table T_Normalized with normalized data, duly converted from source table T_Source. This query is based upon Cartesian join between tables T_SourceFieldsConverted and T_Source.

Version: Access 2000 file format.
 
You can find the sample here: http://www.rogersaccesslibrary.com/forum/tablenormalizationbypuresql_topic554.html
 
.