For the method that you are currently using, you'd need additional match fields in your relationship to match by year and month in addition to item number.
An alternative method would be to use ExecuteSQL as you can set up a WHERE clause that specifies Item number, year and month.
A script could perform a find on table 2 and return the value as a script result if you chose to use a script to update your field.
if you're using a calculated field, then you are using variables in it (year and month). Are those variables data fields or global fields? The approach would be similar for either. Create a relationship based on all three fields (id, year, month) or if you only need it only for display purposes, add a filter to your portal for year and month.
Similar to David Moyer, you can create a field in the parent table (Table1 in this case) and call it 'january'. Have it auto-enter with the number 1 (first month). Make sure every record in the Table1 data has a 1 in it for that field.
Then create a second table occurrence of Table2, and call it Table2_january. Its relationship should be item#(Table2_january) = item# (Table1), and then add a second relationship that is month(Table2_january) = January(Table1_january). [Assuming that month is stored as a number field.].
Then your calculation for adding up January only from the Table1 perspective will be Sum(Table2_january::units_sold).
This is counting on the fact that not every record in Table2 is going to have a 1 in the month field. But some will. When both item# and month# match, it adds.
There's obviously holes in this, but I kept it pretty simple so you could get started. As others have stated, this doesn't take year into consideration, but you could handle that. You could have a sale_date field that holds the entire date, then have separate fields to just calculate the year ( Year(sale_date) ) and month, ( Month(sale_date) ), and then use them accordingly to make sure your relationship is true. The way this initial stab at it would work, is it would add up all January sales, regardless of year, which probably isn't what you need
It works great, thank you for your help!
Glad I could help someone. Far more often, I come to this community seeking help.