Going from Horizontal to Vertical

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Nov-2001 >

Going from Horizontal to Vertical

 

David Cornelius

Not every database is normalized or in a format that supports easy reporting. David Cornelius explains how to transform non-first normal form records into a format that you can report on. By using this technique, reporting becomes simpler and less cumbersome for the user.

THE ease with which novice developers can design and build applications in Microsoft Access is one of the great features of the product. This usability of the product has contributed greatly toward the success of Access as an end-user development tool. However, this ease of use is one of the downfalls of Access development—especially by novice developers.

Most users have taken classes in Access development and have been shown the basic concepts of table design for simple applications. The majority of users will be designing applications that do what their users need.

But sometimes, the application will need to display data in a way that will reveal unforeseen consequences of inefficient database table design. I was tasked with performing modifications to a database that one of the members of our user community had created after taking some introductory training courses in Access development. The system was performing up to expectations, but some minor tweaking needed to be performed. I was to create a new report to display specific information on accounts with upcoming due dates. That’s where the deficiencies in the database design were revealed.

Table layout

At first glance, the report didn’t appear to be all that complicated. But under further investigation, an inefficiency in the table design reared its ugly head. The table was laid out with one record containing multiple occurrences of various dates. The key fields in the table were:

Pool_ID

Monthly_Rpt_Due_Date_January

Monthly_Rpt_Due_Date_February

…data omitted..

Monthly_Rpt_Due_Date_November

Monthly_Rpt_Due_Date_December

 

Typical records looked like this:

 

1 01/01/01 02/01/01 03/01/01 04/01/01 ..no more data…

2 …no data… 08/01/01 10/01/01 11/01/01 12/01/01

3 …no data… 09/01/01 …no data…

 

Other report date fields were contained on the table but aren’t shown in this example. The problem arose when trying to design a report that would display only those records that had upcoming report due dates. If a select query were used on a structure like this one, it would need numerous OR statements and would require multiple queries to work through all of the date fields in the table.

This problem could have been avoided. The dates should have been laid out in a table that had one record for each date like this:

Pool_ID

Monthly_Rpt_Due_Date

Typical records would have looked like this:

1 01/01/01

1 02/01/01

1 03/01/01

1 04/01/01

1 05/01/01

2 06/01/01

2 07/01/01

2 08/01/01

3 09/01/01

This table structure lends itself very well to examining each specific date without any complicated select query using numerous ORs. The Select query just rejects records whose date in the Monthly_Apt_Due_Date field doesn’t meet the date criteria. The remaining records are selected for display in the query or on the report.

Effectively, I was normalizing the data, moving it from a “pre-normal” form to first normal form. Tables in first normal form have no repeated fields, unlike the design in my problem table. Another way to look at this design problem is that you’re moving from a horizontal design (with the date values laid out along the rows) to a vertical design (with the date values stacked on top of each other).

A major redesign of the existing application or table structure wasn’t possible due to time and cost constraints. So developing a solution that would ...

 

read more here in Going from Horizontal to Vertical