Normalization and Denormalization

Article in pdf format here

Helen Feddema approaches the same problem as Garry (Flexible Normalization and Denormalization of Data) , but this time manages her data to provide the users with the output that met their needs.

A reader asked me how he could convert a table with more than 100 questionnaire fields to a more manageable format, with the fields converted to records in a table to make it easier to tabulate the data. Effectively, this is the reverse of Garry’s problem where he converted multiple records into one: I’m converting a single record into multiple records. To make matters more interesting, my reader wanted to be able to save each survey’s results in a separate table, which forces me to re-create the table and query with each processing run.

In the Helen.mdb file that comes with this article, you’ll find the tblSurvey table (part of which is shown in Figure 1) that has the raw data from the questionnaires. It has 44 fields (cut down from the original table, which had more than 100 fields). There’s a Text field, ID, which is the key field, and the other fields are either Boolean or Text, with the Text fields taking a numeric value from 1 to 5.

To switch the fields to records, I first created a table (with the prefix zstbl to indicate that it’s a system table) with just three fields: SurveyID, a Long Integer field indexed Yes (Duplicates OK), Question and Answer

The next step is to delete the existing table for this Survey (if it exists) and re-create it, ready to accept the new data (the surveys were generated each day so I used the current date to name each version of the table):


Figure 1. The table with raw survey data in numerous fields.


Figure 2.  A report based on a totals query giving the number of each answer for each question. (both text fields). This table is copied to create a results table that’s filled from code.

The CreateResultsTable function fills a results table with records containing field names and values from the original tblSurvey and creates a totals query based on it (qtotAnswers) that totals the number of Yes, No, and 1 through 5 answers for each question. For convenience, the function can be run from the mcrCreateResultsTable macro, or (for consistency with Garry’s database) the frmCreateResultsTable form. This query is the record source for a simple report, which is shown in Figure 2.

I begin by defining the variables that I’ll be needing:

Now that the table is created, I can fill it with data. As in Garry’s example, I read each record and then process each field creating a record as I go:

Reporting on the data

However, the table was only part of the solution. I also needed to create a query that would total the results and serve as the basis for a report. I first deleted any existing copy of the query and then generated a new one:

The final step is to update the report with the current date (stored in the Tag property for display in the txtTitle textbox) and ask the user if they want to view it:

Finally, exit the function, with an error handler to take care of any errors:

The CreateAndTestQuery function listed below is handy for creating (and re-creating, as needed) a query in code. I use it to re-create the totals query qtotAnswers, based on the newly created results table (see Figure 2).

You can find a sample database with all of the code in the accompanying download file. In addition to the Microsoft DAO 3.6 Object Library, my sample database also uses the Scripting Runtime Library.

 

Your download file is called   Feddema_Normalization.accdb

About Helen Feddema

Helen is the editor of Access Watch, a more or less biweekly ezine. She lives in the mid-Hudson area of New York state, with two cats and three computers.
This entry was posted in Reports and Graphs. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.