Calculating available resources on a monthly basis



I have two tables, one called Initiatives and one called Available resources.

The Initiatives table basically contains projects, each record having a series of fields among which Resource 1 man-days and Resource 2 man-days to indicate the workload to achieve the project, and a field Target delivery month that contains the wished-for delivery month.

The Available resources table contains 12 records, one per month, listing the available man-days for Resource 1 and Resource 2.

I am looking for a way to sum up the Resource X man-days records from Initiatives per month, so that I know my total month workload, and compare this to the values of the available man-days in my Available resources table.

I am somehow stuck on how to best achieve this - I am stuck at finding a way to “sum up numbers per month” but obviously without using the “Group by” feature because I need the resulting value in a usable field somewhere.

Any help much appreciated!


I’m not exactly sure what you should do but it feels like your base isn’t structured correctly. I’m pretty sure you should have a table called Resources containing Resource 1 and Resource 2 (and any other resources that you may require). This is because your resources seem to be entities that you wish to store and calculate data on.

It may be the case that you still need your Monthly Availability (Available resources) table. Or it might end up being more logical to have the monthly availability in columns within the Resources table. Or you might need both, with monthly columns in the Resources table linking to the relevant records in the Monthly Availability table.

It is hard to know exactly what you should do with the information provided, but issues like this often come down to structural problems with the base.