In a table, I have quarterly dates and sales figures for each date. I want to add the last twelve months of sales figures for each date. I've tried DSUM, but it puts the sum of the whole field into the record. Any ideas what I could try? Thanks.
In a table, I have quarterly dates and sales figures for each date. I want to add the last twelve months of sales figures for each date. I've tried DSUM, but it puts the sum of the whole field into the record. Any ideas what I could try? Thanks.
can you clarify - you have quarterly date but want to sum the last 12 months - do you mean the last 4 quarters? or are the monthly figures in another table? And what does 'the sum of the whole field' mean?
Yes, I have quarterly data like this and
sales
3/31/10 $10
6/30/10 $5
9/30/10 $15
12/30/10 $20
3/31/11 $15
So, I want to sum the last 4 quarters of $ amounts in a third field so the output would look like this:
sales last 4 qtr sales
12/30/10 $20 $50
3/31/11 $15 $55
last 4 quarters in that same table.
try something like
SELECT *, dsum("[Sales]","[myTable]","[Qdate] between #" & dateadd("m",-11,[qdate]) & "# AND #" & [qdate] & "#") AS AnnualSales
FROM myTable
ORDER BY [QDate]
or this which should be faster
SELECT *, (SELECT Sum([Sales] FROM [myTable] S WHERE [Qdate] between dateadd("m",-11,mytable.[qdate]) AND mytable.[qdate]) AS AnnualSales
FROM myTable
ORDER BY [QDate]
Thanks so much!