By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,304 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

Count Month/Year across Multiple Projects

P: 68
Hi, I am struggling with pulling data for multiple projects and then standardizing the data so it can be used in modeling and comparisons.
Overview: We are building a modeling system that allows the user to select multiple projects. The system then pulls the project schedule duration and actual labor hours of employees for each project. The system will use the average of the projects as the base model to be applied to other projects. Since the projects selected donít all start on the same day we want to turn dates (Month/Year) into a count so that we can compare month 1 in all projects. In the simplest terms I have 3 Columns of data:
1. Identifies the Project
2. Identifies the Year & Month
3. Provides the labor hours for the Project, Year & Month
What I am trying to do is turn the Year & Month into a count for each project. Example: Project 1 has duration of 41 Months and Project 2 has duration of 33 Months and Project 3 has duration of 60 months. When we count the months for each project I will be able to provide Average hours for month 1 based on the first month the project has labor hours.
Is there a good way to accomplish this?
Oct 24 '13 #1

✓ answered by time2hike

Rabbit, I found that when I multiplied by 4 the next year in the same project I would be back at 1 instead of 13.

When I multiplied the year by 12 and added the month then subtracted the Min result and added 1 I got the results I was looking for. Example: 2010*12+5 = 24124-24125+1 = 1. When you get into 2011 the calculation still works 2011*12+5 = 24137-24125+1 = 13.

Thank you for your assistance!

Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,366
We would need to see sample data and results along with the table metadata.
Oct 24 '13 #2

P: 68
Rabbit, I have attached an excel worksheet that contains the Raw Data (columns A-D); the Raw Data with the field I am trying to calculate highlighted (columns F-J); and a sample pivot table/chart that is the result I am trying to get to.

This is the first step in the chart that the users want. I am not sure if I can provide the final results in Access or if I will need to take the data to Excel to get the results I need, but I know the whole project hinges on being able to compare data in the first month of one project with data in the first month of another project.

Thank you for your willingness to look at this with me.
Attached Files
File Type: xlsx SampleLaborModelData.xlsx (28.1 KB, 196 views)
Oct 24 '13 #3

Rabbit
Expert Mod 10K+
P: 12,366
Sorry but our firewall blocks file downloads. Please post a sample of data within the thread itself.
Oct 24 '13 #4

P: 68
OK, I start with this data:
Expand|Select|Wrap|Line Numbers
  1. Project    Year    Month    Hours
  2. P39    2010    5    2
  3. P39    2010    6    5
  4. P39    2010    7    15
  5. P39    2010    8    33
  6. P39    2010    9    14
  7. P39    2010    10    19.5
  8. P39    2010    11    29
  9. P39    2010    12    33
  10. P39    2011    1    25.5
  11. P39    2011    2    45
  12. P39    2011    3    73.5
  13. P39    2011    4    66.5
  14. P39    2011    5    55
  15. P39    2011    6    46
  16. P39    2011    7    39
  17. P39    2011    8    65
  18. P39    2011    9    40
  19. P39    2011    10    38
  20. P39    2011    11    40
  21. P39    2011    12    75
  22. P39    2012    1    131
  23. P39    2012    2    117.5
  24. P39    2012    3    101
  25. P39    2012    4    112.5
  26. P39    2012    5    137
  27. P39    2012    6    109.5
  28. P39    2012    7    160.5
  29. P39    2012    8    94
  30. P39    2012    9    100
  31. P39    2012    10    82.5
  32. P39    2012    11    66.5
  33. P39    2012    12    26
  34. P39    2013    1    15
  35. P39    2013    2    19
  36. P39    2013    3    19.75
  37. P39    2013    4    10
  38. P39    2013    5    8.5
  39. P39    2013    6    11
  40. P39    2013    7    7
  41. P39    2013    8    2
  42. P39    2013    9    2
  43. P42    2011    9    22
  44. P42    2011    10    43
  45. P42    2011    11    33
  46. P42    2011    12    20
  47. P42    2012    1    45.5
  48. P42    2012    2    55.5
  49. P42    2012    3    54
  50. P42    2012    4    78
  51. P42    2012    5    47
  52. P42    2012    6    59.5
  53. P42    2012    7    132
  54. P42    2012    8    90
  55. P42    2012    9    46
  56. P42    2012    10    67
  57. P42    2012    11    74.5
  58. P42    2012    12    44.5
  59. P42    2013    1    73
  60. P42    2013    2    60
  61. P42    2013    3    58.5
  62. P42    2013    4    64.5
  63. P42    2013    5    65
  64. P42    2013    6    57.5
  65. P42    2013    7    124.5
  66. P42    2013    8    67.5
  67. P42    2013    9    66.5
  68. P44    2012    3    17
  69. P44    2012    4    12
  70. P44    2012    6    14
  71. P44    2012    7    24.5
  72. P44    2012    8    13
  73. P44    2012    9    133
  74. P44    2012    10    122
  75. P44    2012    11    103.5
  76. P44    2012    12    136.5
  77. P44    2013    1    42.5
  78. P44    2013    2    77
  79. P44    2013    3    47
  80. P44    2013    4    26.5
  81. P44    2013    5    30.5
  82. P44    2013    6    33
  83. P44    2013    7    65
  84. P44    2013    8    54.5
  85. P44    2013    9    72
  86.  
I want to add a field that tell me the 1st, 2nd, 3rd,etc. month for each project. The data would look like this:
Expand|Select|Wrap|Line Numbers
  1. Project    Year    Month    Mcount    Hours
  2. P39    2010    5    1    2
  3. P39    2010    6    2    5
  4. P39    2010    7    3    15
  5. P39    2010    8    4    33
  6. P39    2010    9    5    14
  7. P39    2010    10    6    19.5
  8. P39    2010    11    7    29
  9. P39    2010    12    8    33
  10. P39    2011    1    9    25.5
  11. P39    2011    2    10    45
  12. P39    2011    3    11    73.5
  13. P39    2011    4    12    66.5
  14. P39    2011    5    13    55
  15. P39    2011    6    14    46
  16. P39    2011    7    15    39
  17. P39    2011    8    16    65
  18. P39    2011    9    17    40
  19. P39    2011    10    18    38
  20. P39    2011    11    19    40
  21. P39    2011    12    20    75
  22. P39    2012    1    21    131
  23. P39    2012    2    22    117.5
  24. P39    2012    3    23    101
  25. P39    2012    4    24    112.5
  26. P39    2012    5    25    137
  27. P39    2012    6    26    109.5
  28. P39    2012    7    27    160.5
  29. P39    2012    8    28    94
  30. P39    2012    9    29    100
  31. P39    2012    10    30    82.5
  32. P39    2012    11    31    66.5
  33. P39    2012    12    32    26
  34. P39    2013    1    33    15
  35. P39    2013    2    34    19
  36. P39    2013    3    35    19.75
  37. P39    2013    4    36    10
  38. P39    2013    5    37    8.5
  39. P39    2013    6    38    11
  40. P39    2013    7    39    7
  41. P39    2013    8    40    2
  42. P39    2013    9    41    2
  43. P42    2011    9    1    22
  44. P42    2011    10    2    43
  45. P42    2011    11    3    33
  46. P42    2011    12    4    20
  47. P42    2012    1    5    45.5
  48. P42    2012    2    6    55.5
  49. P42    2012    3    7    54
  50. P42    2012    4    8    78
  51. P42    2012    5    9    47
  52. P42    2012    6    10    59.5
  53. P42    2012    7    11    132
  54. P42    2012    8    12    90
  55. P42    2012    9    13    46
  56. P42    2012    10    14    67
  57. P42    2012    11    15    74.5
  58. P42    2012    12    16    44.5
  59. P42    2013    1    17    73
  60. P42    2013    2    18    60
  61. P42    2013    3    19    58.5
  62. P42    2013    4    20    64.5
  63. P42    2013    5    21    65
  64. P42    2013    6    22    57.5
  65. P42    2013    7    23    124.5
  66. P42    2013    8    24    67.5
  67. P42    2013    9    25    66.5
  68. P44    2012    3    1    17
  69. P44    2012    4    2    12
  70. P44    2012    6    3    14
  71. P44    2012    7    4    24.5
  72. P44    2012    8    5    13
  73. P44    2012    9    6    133
  74. P44    2012    10    7    122
  75. P44    2012    11    8    103.5
  76. P44    2012    12    9    136.5
  77. P44    2013    1    10    42.5
  78. P44    2013    2    11    77
  79. P44    2013    3    12    47
  80. P44    2013    4    13    26.5
  81. P44    2013    5    14    30.5
  82. P44    2013    6    15    33
  83. P44    2013    7    16    65
  84. P44    2013    8    17    54.5
  85. P44    2013    9    18    72
  86.  
Once I have determined the 1st, 2nd, 3rd, etc. month for each project I can work with the data in this format.
Expand|Select|Wrap|Line Numbers
  1. Sum of Hours    Column Labels                                    
  2. Row Labels    1    2    3    4    5    6    7    8    9    10
  3. P39    2    5    15    33    14    19.5    29    33    25.5    45
  4. P42    22    43    33    20    45.5    55.5    54    78    47    59.5
  5. P44    17    12    14    24.5    13    133    122    103.5    136.5    42.5
  6. Grand Total    41    60    62    77.5    72.5    208    205    214.5    209    147
  7.  
The data does not line up when I add it this way but hopefully you get the idea of what I am trying to do. Again, thank you for your willingness to help.
Oct 24 '13 #5

Rabbit
Expert Mod 10K+
P: 12,366
If you take the year and multiply by 4 then add the month, that gives you an incremental count of months from year 0. So for row one that's 2010 * 4 + 5 = 8045. For row 2 that's 2010 * 4 + 6 = 8046.

Then for each row, you subtract the minimum count of months per project and add 1. That gives you your mcount field. The minimum count of months for project P39 is 8045. So for row 1 that's 8045 - 8045 + 1 = 1. For row 2 that's 8046 - 8045 + 1 = 2.

For the final result, you do a crosstab query using that new mcount field.
Oct 24 '13 #6

P: 68
Rabbit, I found that when I multiplied by 4 the next year in the same project I would be back at 1 instead of 13.

When I multiplied the year by 12 and added the month then subtracted the Min result and added 1 I got the results I was looking for. Example: 2010*12+5 = 24124-24125+1 = 1. When you get into 2011 the calculation still works 2011*12+5 = 24137-24125+1 = 13.

Thank you for your assistance!
Oct 24 '13 #7

Rabbit
Expert Mod 10K+
P: 12,366
Sorry, it should have been 12. I have no idea why I said 4. 12 months to a year, I must have been thinking of quarters or something.

Glad you got it working. Good luck with the rest of your project.
Oct 25 '13 #8

Post your reply

Sign in to post your reply or Sign up for a free account.