Display field horizontally yet updateable (1 Viewer)

[kvar]

I'm wondering if there are any other ways, besides a crosstab query or pivot table, that I can have one of my fields as a "column header" and still be able to update data?

At the moment I'm contemplating a form where users can update their budgeted hours for each employee. In my table I have a field for week ending date then of course name, department, pay rate, etc.
There are queries to calculate how much they have budgeted by the week, month, FY. And there is also a table for Actual costs. It has similar fields but the data is imported from payroll reports every week.
So basically the queries ultimately figure out how far off budget each department is.

I'm still trying to figure out if this should be a form or a linked excel file, or something else but here's what I need:
Each department head needs to be able to access a file (form/spreadsheet) At the top of this there would be a summary by month. For prior months it would show the calculated Actual and Budgeted amounts and the variance. Summing up the total cost variance for each month to show how far off you are for the year. And fields showing what you've budgeted for future months. That's all relatively simple.

Here's where I'm stuck: Below that handy summary, I need something that basically displays looking like a pivot table. With columns for department, name, rate and number of hours. And the week ending date would be across the top. I can do that with a crosstab or pivot table, but then they can't update the number of hours.
Let's say so far for the year you are under budget, you can add some hours for some of your employees, doll out some OT, and spend the money. So when they enter a new number of hours for someone the top part needs to recalculate to show where they are budget wise now.

And honestly, I'm stumped as to where to even start!
ANY and ALL ideas are greatly appreciated! Apologies for the long post.

 

[pbaldy]

ChrisO has an updatable crosstab example here, see if it helps any:

http://www.baldyweb.com/ChrisOSamples.htm

 

[kvar]

Thank you, while I play around with that for a minute, see if this gives you any ideas.
What if I use a spreadsheet, design it like a template. In the VBA OnOpen, have it go to Access and copy a PivotTable that displays like I need, then have it Paste Special - Values into Excel. This way it's not actually a PivotTable and you can change the numbers. I can lock the cells that I don't want them changing, basically everything but where the hours go.

Also OnOpen I could have the VBA copy the values for my query that calculates the variances and paste those into specific cells based on however I format it.

I could use formulas in those cells so that when a user changes someones hours the values in the summary at the top will change dynamically.
After they've made all their changes have a button to "Submit Changes".

And that's where I'm stuck on this idea. How would I tell it where to put those hours in my Access Budget table? Somehow I think it would need to only copy values from cells that have been changed, to speed things up, but then what?
Maybe something like a DLookup? Look at the week ending date above the changed cell, the persons name to the left of it then find the record in the table that contains both those values and update the hours field with the new value.

Is that making any sense?

 

[pbaldy]

I think I see where you're headed, but I'm not sure how workable it would be. I've pushed to Excel many times, but never tried to use it the way you're describing. I've used forms in various views that weren't editable (like based on a crosstab) and still "edited" by double-clicking on a textbox to bring up an input box or dialog form. I can tell what they're trying to edit based on the record or textbox, apply the data and requery the form. That may be what Chris did.