De-normalizing data for the user interface

Have you ever wanted to display and edit data in a form that looks like a Cross-Tab query?  A great example of this might be to allow users to enter their hours spent on various projects (rows) for each day (columns) in the current week or month.

Last month, during my monthly Access User Groups meeting I talked about de-normalizing data for the user interface.  The idea for this presentation came from a question on Experts Exchange where the poster (OP) asked for some help convincing his manager to allow him to store the data in a normalized fashion.  The problem was that his manager wanted the data displayed in a format similar to what they had been using on a paper form.

But his data entry alternative was a continuous (or perhaps datasheet) form which simply had a separate row for each entry.

One of the solutions provided suggested using an ActiveX grid control, but with those not working in 64Bit Office, I tend to shy away from them.  Another option, the one I subscribe to, suggested creating a de-normalized temporary table from the normalized data and binding a form to that temporary table.  This is not a trivial task, but really is not that difficult and makes entering data much more intuitive than the “normalized” option presented above.  And updating the data in the normalized table either as changes are made to the temp table or when the user closes the temp table.  I’ve created a sample database (downloadable from here) and a video (available AccessUserGroups.org/ET) which describe how to do this.

One thought on “De-normalizing data for the user interface

  1. This is a really great concept and guide. I am always looking to improve my forms. It looks like you are still using Access 2010 in the video, is there any reason for this in particular?

Leave a Reply

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

© 2024 Developing Solutions | ScrollMe by AccessPress Themes