Access and SQL Server. Editing normalised data with an un-normalised crosstab-like interface

I'm working on a demand management system which I have inherited in a somewhat unnormalised state. It is currently an Access database, which I'm working on upgrading to a SQL database with an Access Project front end.

There is a situation whereby projects are worked on by company employees(resources) assigned from various teams

Right now we have a table of the following nature representing how many resources are required from each team for any given period:

forecastid      | projectid | teamid | 2006-01 | 2006-02 | 2006-03 | 2006-04 | 2006-05 |...... | 2009-12
(autonumber) |     20      |   1       |               |    3          |    4         |    5         |                |       |
                       |     20     |   2        |    2         |    4          |   2.5       |               |                |       |
                       |     21     |   1        |               |    2          |    2         |               |                |       |

With the teamid, 2006-01... as a datasheet subform which can be easily edited by users. They can add/delete forecasts and edit any figures.

How I would like to get it to is a more normalised form now. Something like the below.

FORECASTS
*ForecastID (Primary Key)
+ProjectID
+TeamID

FORECASTDETAILS
*ForecastID
*Period (eg 200601, 200602)
Amount

* indicates primary keys
+ indicates alternate keys

Unfortunately, one of the mandates is that it is representable and editable in the original tablular format with the entire range of periods over the 3 years visible. For reporting needs, its not a problem to represent this in a read-only format, but making it editable is a problem.

I have a stored procedure which can generate the un-normalised format as a temporary table for editing (and presumably I will be able to write one which can take the un-normalised data to synchronise back to the backend again), but is this the best way to handle this, especially given record locking and synchronisation issues? Is there a better way to deal with it?

If its relevant, my background is basic to intermediate SQL Server development (with 15 years of DBA & development experience on other platforms). When it comes to MS Access my skills are quite advanced. There's possibly kludgy solutions I can think of using VBA, but maybe there's a better stored procedure option?

I am limited also to developing with Access as a front end.

Any thoughts?

Start Free Trial