Help me brainstorm some pivot table calculation ideas

[Datech]

I wasn't sure whether to put this in forms, reports, or programming, so I'm putting it in the main form.

I've built a query that I need to turn into a pivot table. The information is based on Resource Allocations that include the following information about an employee on a project:

• tblResourceAllocation ID
• LastName (lookup bound to unique ID from tblEmployeeData but displays Employee's last name in table)
• Project (lookup bound to unique ID from tblProjects but displays Project's SOW name)
• Funding Method
• Resource Start Date (the single date that the employee starts his project)
• Resource End Date (the single date that the employee rolls off the project)
• Percent Utilization (percentage amount of time employee works on project during the time period between start and end date, 0-100%)
• Technology Domain
• Off-Shore (yes/no)
• Last Name (actual last name from tblEmployeeData for display purposes)
• First Name (actual first name from tblEmployeeData)
• Employee Class (from tblEmployeeData)

The pivot table format is like this:

• Report Filter
  
  • Technology domain
  
  
• Rows (top level to bottom)
  
  • Project
  • Off-shore
  • Funding Method
  • Employee Class
  • Last Name
  
  
• Value
  
  • percent utilization
  
  
  
  
• Columns
  
  • this is what I need help with

What I want to display in the columns is the weeks or months in a year. The weeks display and the months display will be a separate pivot table, but the principles will apply to both. What I want to happen is for the pivot table to display the Percent Utilization in the weeks or month between the start date and end date. Right now if I put the start or end date in the columns, it will display all the weeks or months in a year based on what I choose, but only show the percent utilization for the week or month that the Start/End date correlates with.

I've used calculated fields in Excel to do something similar, but the dates weren't formatted in the same way. Therefore I'm kind of at a loss on how to proceed. That's why I want help brainstorming (or the answer, if it is obvious to you).

• Do I write code that calculates the difference between the start and end date, and autofill some hidden fields that represent each week/month in a year? (the Excel approach)
• Is there a way to make the columns automatically represent a necessary date range, then have calculated fields in the Values that know to display the Percent Utilization for the date range? (the MS Project approach)
• Other????

Thanks for reading!

[June7]

The % utlization value is not associated with a single week (or day or month). It is related to a range that could probably even start in the middle of a week and end in another month. The ranges are probably overlapping between projects. There is no discrete set of values that can be used as the column headers. I am not sure that pivoting this data is meaningful. Why not just a report that organizes the records by the heirarchy you describe and shows the % utilization?

However, you might get some ideas from https://www.accessforums.net/access/...1-a-25282.html

[Datech]

The % utlization value is not associated with a single week (or day or month). It is related to a range that could probably even start in the middle of a week and end in another month. The ranges are probably overlapping between projects. There is no discrete set of values that can be used as the column headers. I am not sure that pivoting this data is meaningful. Why not just a report that organizes the records by the heirarchy you describe and shows the % utilization?

However, you might get some ideas from https://www.accessforums.net/access/...1-a-25282.html

I understand those limitations, hence trying to brainstorm workarounds. Since the hierarchy ends at the employee, and the tblResourceAllocation is arranged by Employee to Project ties with %Util for each, the dates won't overlap for an individual employee on an individual job. A 50/50 util will overlap of course, but that's something that we want to see and tally.

The data is currently displayed in a pivot table in Excel, with the difference being a resource util is defined for each individual week in the Excel file. That allows us to show a weekly layout, as well as use calculated values for months. The preferred method of data input is now individual start and end dates, and everyone wants to move from Excel to Access for a variety of reasons, so I have to figure out some way to get this data displayed. I figured a Pivot table was a smarter implementation than creating a control for each and every week and a project manually

As always, thanks for the reply June7. I'll look through the link you gave me. Let me know if my comments cleared anything up or made more questions

[June7]

The only thing I can see at moment is a query that concatenates the start/end dates into a single string value and use that constructed field as the column header.

You will encounter issue with building a report because the column headers will be so dynamic.