DSUM by ID in update query

[TBFL]

I am relatively new to Access programming and this is my first post, so please excuse any rookie mistakes.

I am trying to calculate rolling total activity within a user specified date range for each of several pieces of equipment.

I have a table "MonthlyActivity" listing equipment ID, monthly activity, and units of activity. Here are the first three rows..
Combined ID Month_Yr Monthly Throughput Units Monthly Throughput
1A Jan 2011 2837.25 gal/mn
2A Jan 2011 5108.33 gal/mn
3A Jan 2011 9034.00 gal/mn
...
I have another table InputDates in which a user input starting and ending month are stored.
ID Start Month End Month
1 Feb 2011 Dec 2011

Finally, I have a third table SourceData listing equipment by ID and annual throughput.
Combined ID Annual Throughput 2011 Annual Throughput Annual Units
1A 56192 34047 gal/yr
2A 56192 61300 gal/yr
3A 56192 108408 gal/yr

I want to update the annual throughput field in the SourceData table by summing the monthly activity in MonthlyActivity over the specified date range.
I have the summation within the date range working, but have not been able to get a different total activity for each piece of equipment. Here is the SQL statement I am working with:

UPDATE InputDates, SourceData, MonthlyActivity
SET SourceData.[Annual Throughput] = DSum("[Monthly Throughput]","[MonthlyActivity]","[Combined ID] = '2A' AND [Month_Yr] >= #" & [InputDates]![Start Month] & "# AND [Month_Yr] <= #" & [InputDates]![End Month] & "#");

This was a test to see if I could do it with one piece of equipment only. It sums the activity for '2A' but puts it in every row in "SourceData".
Can anyone tell me how to change this to get the activity for each piece of equipment separately? Total for 1A in the 1A row, total for 2A in the 2A row, etc.

I have tried this:
UPDATE InputDates, SourceData, MonthlyActivity SET SourceData.[Annual Throughput] = DSum("[Monthly Throughput]","[MonthlyActivity]","[Combined ID] = [SourceData]![Combined ID] AND [Month_Yr] >= #" & [InputDates]![Start Month] & "# AND [Month_Yr] <= #" & [InputDates]![End Month] & "#");

I get an error "Microsoft Office Access didn't update 108 field(s) due to a type conversion failure"..etc.

I'm aware that I could do this with a make table query to do the summation grouped by ID followed by an update query on that new table, but as I have to do this with several different pairs of tables I thought I would look for a way to do it in fewer steps.
Any help would be greatly appreciated.
Thanks in advance!!

 

[TBFL]

Pat ,
Thanks very much for your response. I appreciate the advice, and will probably go ahead with the method using the temp tables, but want to understand the issue better. Does DSum() run a separate query for each row in the update table? (as opposed to the table being summed)? I have about 8 tables to update, each of which has between 10 and 20 rows. The tables being summed, on the other hand, have 100-1000 rows.

Regarding the advice not to store calculated values - I've read this in other posts, but can you explain what the drawback to storing them is? In this case the value I'm updating used to be an input data set (annual activity) which is now being updated with the sums of the monthly activity. There are existing queries that use this field, so storing the calculated value allows me to use 15 or so existing queries without revising them. If I did want to calculate the as I need them, wouldn't I still need to store the sums in a temporary table in order to use them in expressions in other queries?
Thanks again for your help. I appreciate your patience with a beginner.

 

[TBFL]

Thanks very much Pat. That all makes sense. I really appreciate you taking the time to explain it.