Table Question

[Lissa]

I have created a database that stores information about employee utilization in MS Access 2003. It's basic purpose is to forcast hours employees will work on various projects throught the year. I have created the following tables: project, month, employee, supervisor, and data . The data table has foreign keys (project id, month id, emp id, supervisor id), followed by an hours field, utilization % field and basically stores all details about which employees expecting to bill x hours to each project...

I have created several useful crosstab reports that displays months as column headings and employees and project grouped by supervisor.. then provides hours allocated to each project with hours summed by month. This crosstab query is based on a query of all tables. My problem is that users (supervisors) want to be able to view a form exactly like the crosstab report (landscapt type view), that will allow them to edit the the hours and calculate a total in real-time.

I have been racking my head trying to find a solution in MS Access...

I had created a pivot table showing the same information but it doesn't seem like you can edit a pivot table.

My next thought was to alter the table structure by adding a field for each month.. every year I would have to add 12 month fields since the months in the month table are entered as Jan-07, Feb-07, Mar-07...etc. But that can't be a good idea?? Right?... it would make data entry a pain and what would be the point of having the data in a database if the structure would just resemble an Excel spreadsheet... <sigh>

Does anyone have any suggestions on how I can solve this? Ideas.. point me to some resources to read... or at least know if what they are wanting is even possible? At the moment I am lost

 

[The_Doc_Man]

A computed QUERY (such as a cross-tab), like ALL queries with computations, cannot be edited in any computed field. Make no mistake, a cross-tab is a QUERY, not a table.

The function being described (editing the hours in a currently displayed cell on a multi-cell 2-dimensional array and having an instant total crop up) is exceedingly complex in Access. It is one of the cases where a spreadsheet is the better choice because that function is already programmed into Excel. But don't go tossing the Access stuff right away. There are hybrid ways to do things.

Consider making a spreadsheet with the properties you want. Let's say it has a couple of "fixed" columns and the raw monthly data in it. Perhaps the first fixed column is the project info, the first fixed row is the headers, and the last fixed column is the sum and the last fixed row is the monthly totals. This forms a virtual rectangle whose dimensions you know - 'cause you built it yourself. But make it empty.

NOW compute your monthly stuff. Do so in a summation query. Display the query for yourself to verify that the monthly stuff is right.

Now (look this up in Help and also search this forum)... Open an Excel application object and populate the spreadsheet from the summation query. When you close the query, it contains "real" data, the most up-to-date you can get, and (if you left the "special" rows and columns alone) it does all the row and column totals for you.

Give your bosses the spreadsheet. Let them play with it all they like. BUT before you go that way, determine way far ahead of time what they want you to do with the result. And be aware that bosses apparently have to check some parts of their brains at the door that says "Executive Suite." If they rose through the ranks, they quickly forget the lessons learned. If they came into the company directly, they never learned the lessons in the first place.

Now, here is where the headache. That "real-time" adjustment doesn't mean doodlum-squat. I've been there too often. You don't say if the shop is "all project" or "all support" or a mixed bag of support and development. There is a thing called "level of effort." You can say that a person is 50% support and 50% development. If you decide that a person needs to spend more time on developing a project to bring in the completion date, you have to take away the effort on support. If support becomes more important, that person's projects experience a date slippage. If that person is on the critical path of the project AND other folks also work there, stuff REALLY gets slipped. I.e. adjusting one thing adjusts something else, too.

A spreadsheet such as has been described doesn't really do that right. It is too blunt a tool. It is just an exercise in mental masturbation that a supervisor THINKS is productive. But the government has long ago realized that you have this condition with the acronym OBE - govspeak for "overcome by events" - in other words, sh|t happened. And playing with a schedule projection at that level is a sure way to invite OBE to visit the shop.

Adjustments such as this should ONLY be attempted with a tool specifically designed to take projects and levels of support into account so that when you squeeze the grape you can immediately see what pops. A spreadsheet won't do this. Access CAN do it but not very easily with the kind of display you are describing. If you ever really DID build such a tool, I can assure you that there would be a big commercial market for it.

 

[Lissa]

You made some good points about trying to come up with a possible hybrid solution! I'll try that. I was just unsure if trying to do everything in Access was worth it or not...

I agree that this is a really blunt tool for project/resource management...but this request is from supervisors who felt MS Project was too complicated/cumbersome to use...

I guess I forgot to state if we were a "mixed shop" or not... I guess you can say "mixed shop". They just want a tool they can use to determine/forcast each employee's utilization by project. So if 'Jane Smith' is forcasted to work 100 hours on Project X, 50 hours on Project Y and another 50 hours on Project Z.. if there are 176 billable hours in the month of May then she is being utilized over 100% and they need more people to help work one of the projects so she isn't utilized over 100%. They want to be able to make the hour adjustments at their weekly meetings using this tool. So that's why I had displayed all this info in a crosstab query... they just want to be able to edit the report... like a spreadsheet... but still want it in a database....

Anyhow -Thanks for taking the time to post!
Lissa