DSUM by ID in update query (1 Viewer)

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!!

You don't need to store calculated values since you can calculate them in queries as you need them.

Using DSum() this way is very inefficient and depending on the number of rows in your table could be very slow. The DSum() needs to run a separate query for EVERY row. So, if you have 100 rows to update, Access will run 101 queries. If you have a thousand, it will run 1001 queries.

If you elect to ignore the advice to not store the calculated value, run make table queries that create the sums. Then run update queries to take the sums from the temp tables.

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.

Yes, the DSum() runs a separate query for each and every row. How else could it come up with the correct value for a particular row. A totals query does essentially the same thing but it does it all in a single query with multiple rows of output so there is significantly less overhead.

In some cases storing calculated values is just wrong but in others, it is a matter of efficiency. If the source data doesn't change, there is little jeopardy in storing the calculated value and that sounds like the case here.

Let me give you an example of where it is outright wrong and when it becomes OK or even preferable. Take our favorite order entry system. The order header will contain the tax rate but will not normally contain the sum of the detail items or the tax amount. However, once the order has been filed and some time has elapsed, the Order will be archived. At that point it can no longer be changed and so it makes sense to add the total amount and the tax amount to the order header to minimize calculations in the historical reporting. So typically for transactional systems, sums are calculated as needed because the underlying data can change but for data warehouses, they are stored because historical data cannot be changed.

You almost never need temp tables (except in the case we were discussing where you need to calculate sums and save them because Access won't let you do an update with aggregated data in the query) because Access treats queries and tables interchangeably for the most part. When you have complicated queries that are broken into several steps, you can nest them. I did some for some really heavy financial calculations for a bank that were 7 levels deep. The query engine analyzes the whole set of queries and optimizes them if it can.

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