dsum function in Update Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to update a field in one table based upon the sum of another field
in another table where several data elements are equal to each other. When I
select to see the results of this query in tabular form it shows me one row
equaling zero in a column called 'Open-ToBTest-Fin'.

Here's the code I have so far...

UPDATE TM_TEST_Item_Summary_Stage_5 SET
TM_TEST_Item_Summary_Stage_5.[Open-ToBTest-Fin] =
DSum("[TM_TEST_Item_Summary_Stage_3].[Count]","TM_TEST_Item_Summary_Stage_3","[Summary_Date]=&[Summary_Date]
And [Summary_Iteration]=&[Summary_Iteration] And
'Open-ToBTest-Fin'=&[Item_Status] AND [Division]=&[Division] And
[Location]=&[Location] And [Business_Cycle]=&[Business_Cycle]")
WHERE (((TM_TEST_Item_Summary_Stage_5.Summary_Date)=[Summary Date:]) AND
((TM_TEST_Item_Summary_Stage_5.Summary_Iteration)=[Summary Iteration:]));
 
It's very difficult to fix this without know the datatypes of your fields
and other stuff. I would expect something like the following might work. I
have added hard carriage returns for readability. I assumed Division and
Location were TEXT , Summary_Date was Date, and Summary_Iteration and
Business_Cycle were numeric.

UPDATE TM_TEST_Item_Summary_Stage_5
SET [Open-ToBTest-Fin] =
DSum("[Count]","TM_TEST_Item_Summary_Stage_3",
"[Summary_Date]=#" & [Summary_Date] &
"# And [Summary_Iteration]=" & [Summary_Iteration] &
" And [Open-ToBTest-Fin]= " & [Item_Status] &
" AND [Division]=""" & [Division] &
""" And [Location]=""" & [Location] &
""" And [Business_Cycle]=" & [Business_Cycle])
WHERE (((TM_TEST_Item_Summary_Stage_5.Summary_Date)=[Summary Date:]) AND
((TM_TEST_Item_Summary_Stage_5.Summary_Iteration)=[Summary Iteration:]));
 
It is considered poor design to store calculation results in a table. You
can always calculate at form or report time.

Eric
 
Back
Top