DSum?

[hockeylvr]

I am attempting to create an Access query to sum volumes across months for products based on the effective month entered in my table. I used DSum in my query grid but it is obviously the wrong thing to do as it is generating thousands of rows and locking up my system. I am looking for some alternatives. I use the DSum to calculate my volume field in my form for each record which works, then tried to carry that code into the query grid.

SELECT tblProjectDetails.projectid, tblProjectDetails.product, tblProjectDetails.cutinmonthnmbr, DSum("[volume]","qryMonthlyEngineVolumes","[product] = '" & [tblProjectDetails].[product] & " ' " & " And [year]= '" & [tblProjectDetails].[cutinyear] & " ' " & " And [MonthID] Between " & [tblProjectDetails].[cutinmonthnmbr] & " And 12") AS engvolume, tblProjectDetails.fcstloc, tblProjectDetails.cutinyear
FROM qryMonthlyEngineVolumes INNER JOIN tblProjectDetails ON qryMonthlyEngineVolumes.product = tblProjectDetails.product
WHERE (((tblProjectDetails.fcstloc) Is Not Null));

Are there better alternatives to this? I basically need to see the results as "this project's total volume from its' cutinmonthnmbr to December (12) is "this number" "

Would appreciate any ideas,

Toni

 

[Remou]

Perhaps something like:

SELECT tblProjectDetails.projectid, 
tblProjectDetails.product,
tblProjectDetails.cutinmonthnmbr, 
(Select Sum([volume]) From qryMonthlyEngineVolumes B 
Where B.[product] = [tblProjectDetails].[product] 
And B.[year]= [tblProjectDetails].[cutinyear] 
And B.[MonthID] 
Between [tblProjectDetails].[cutinmonthnmbr] 
And 12) AS engvolume, 
tblProjectDetails.fcstloc, 
tblProjectDetails.cutinyear
FROM tblProjectDetails
WHERE (((tblProjectDetails.fcstloc) Is Not Null));

 

[hockeylvr]

That's just what I needed! Thanks so much - I've been trying to figure this out for weeks! It's works and it actually makes sense to me!

Toni