MS Access: Any Ideas on How to Create an Excel-Like Form?

Objective: Convert an overgrown Excel sheet into an Access database, but maintain a front-end that is familiar and easy to use.

There are several aspects to this, but the one I'm stuck on is one of the input forms. I'm not going to clutter this question with the back-end implementation that I have already tried because I'm open to changing it. Currently, an Excel spreadsheet is used to input employee hour allocations to various tasks. It looks something like the following.

Employee    | Task   | 10/03/10 | 10/10/10 | 10/17/10 | 10/24/10 | ... | 12/26/11
---------------------------------------------------------------------------------
Doe, John   | Code   |      16  |      16  |       20 |       20 | ... |       40
---------------------------------------------------------------------------------
Smith, Jane | Code   |      32  |      32  |       16 |       32 | ... |       32开发者_运维百科
---------------------------------------------------------------------------------
Doe, John   | Test   |      24  |      24  |       20 |       20 | ... |        0
---------------------------------------------------------------------------------
Smith, Jane | Test   |       0  |       0  |       16 |        0 | ... |        0
---------------------------------------------------------------------------------
Smith, Jane | QA     |       8  |       8  |        8 |        8 | ... |        8      
---------------------------------------------------------------------------------
               TOTAL |      80  |      80  |       80 |       80 | ... |       80

Note that there are fifteen months of data on the sheet and that employee allocations are entered for each week of those fifteen months. Currently, at the end of the fifteen months, a new sheet is created, but the database should maintain this data for historical purposes.

Does anyone have any ideas on how to create an editable form/datasheet that has the same look and feel? If not, how about an alternative solution that still provides the user a quick glance at all fifteen months and allows easy editing of the data? What would the back-end tables look like for your proposed solution?

---

This is a classic de-normalization problem.

To produce an editable spread-sheet like view of your database you'll need a table with 66 columns (the two identifying columns and 64 weekly integer columns). The question is whether you want the permanent storage of the data to use this table, or to use a normalized table with four columns (the two identifiers, the week-starting date, and the integer hours value).

I would give serious consideration to storing the data in the normalized form, then converting (using a temporary table) into the denormalized form, allowing the user to print/edit the data, and then converting back to normal form.

Using this technique you get the following benefits:

1. The ability to support rolling windows into the data (with 66 columns, you will see a specified 15 month period, and no other). With a rolling window you can show them last month and the next 14 months, or whatever.

2. It will be substantially easier to do things like check peoples total hours per month, or compare the hours spent in testing vs QA for an arbitrary range of dates.

Of course, you have to write the code to translate between normal and denormal form, but that should be pretty straightforward.