How to display data in a 'pivoted' way... and then update it

This is a bit of a complex one to explain: I'm looking for advice on form design and query structure. The response may stray into unbound forms and programmatic updates, so it's a bit of fun...

This is currently an Access 2007 development, but I'm in the process of migrating the back end to MySQL (no SQL Server in this shop).

I have a table holding forecast values for contracts by month (see simplified Forecast table in code). You can see it's structured with a new row for each PO & month being forecast. POs / months for which a forecast doesn't exist have no row (not a row with a null value in FCValue).

However my users want a datasheet type of view into this data, with every PO listed down the left, and FCMonth across the top, extending x months into the future, into which they can type or paste forecasts, which will update the base table. You can totally see where they are coming from. But I can't figure out how to provide this without creating a mess:

(1) Displaying all POs,whether forecasted or not, is simple: a LEFT JOIN on the master PO table will take care of that.

(2) The layout they describe is essentially a pivot. I can see how I could (research how to) dynamically create a pivot in a stored procedure, to create a dataset that could then be applied to a datasheet form. But I'm pretty darn certain it won't be updateable. The same would go for an Access pivot, which I understand is effectively a report control.

(3) The only solution I can see is to do the dynamic pivot mentioned above, throw it into a datasheet form, then use an ON UPDATE or ON CHANGE event (or similar) to trigger a stored procedure (or VB sub if still in Access), passing it the PK information and new value in the parameters.

(4) An alternative to (3) could be a batch update, triggered by the user hitting an 'Apply' button.

Anyone out there encountered this type of problem? Any better ideas?

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

CREATE TABLE Forecast(
  PONum INT NOT NULL
, FCForMonth INT NOT NULL
, FCValue double
, PRIMARY KEY(PONum, FCForMonth))
 
 
-- Users want the following:
 
PONum  Mth1   Mth2   Mth3   Mth4
1234     
2442
4680
7531