DSum help or alternative solution. 1

[BruceJackson]

I am trying to use the DSum function in a query but having limited success.

I want to calculate the total Cost of Sales (COS) for the most recent 3 completed periods.

The query I am working with shows the COS for each period. Although the period is a text field, I have a corresponding month end date in each record.

I can calculate the total of COS for the most recent 3 periods but can not get it to do the calculation seperately on each period within the query. All records show the same value based on the most recent period date.

Last 3 Periods Total: DSum("[Z COS MC DATE]![COS]","[Z COS MC DATE]","[Z COS MC DATE]![ME DATE] > Now()-98 &quot

Also, not sure how consistent the calculation will be going forward using now()-98. This bit works now (today) but I am sure will not work on a selection of future dates. Is there any way that I can tie this up for consistent accuracy?

I hope this explains my problem clearly and thanks in advance for any help you can give.

Regards,

Bruce

 

[DanChard]

Try adding:

"group by [period]"

to your query, where 'period' is the name of the column which stores the period. So the SQL behind your query will probably look something like the following:

select DSum("[Z COS MC DATE]![COS]","[Z COS MC DATE]","[Z COS MC DATE]![ME DATE] > Now()-98 &quot
from [table name]
group by [period]

Hope this is of some help. I don't know anything about dsum but it does sound like you're after the group by clause.