I have a query/table with 2 columns:
WeekNr and Invoiced
What I need is to create another query/table with 3 columns:
WeekNr, Invoiced and CumulativeInvoiced.
The first 2 columns are the same from the original table and the 3rd will have the Invoiced values but cumulated.
For examle, if the 1st table has data like:
The 2nd table must have:
Actually, it is a query not a table that I'm using.
Here is the code adapted as you said:
DSum("[Invoiced]","reqWeekSalesByMonthByCountry","[weeknr] <=" & [weeknr]) AS CumulativeInvoiced
I have this code in another query but when I run it I have an error tag for all fields for the CumulativeInvoiced column.
I cannot figer out what is happening.
Also, on reqWeekSalesByMonthByCountry query I'm using 2 parameters: pMonth and pCountry.
Teddy, you are right, I need this data only in a report.
Supposing that I already have reqWeekSalesByMonthByCountry with data I told you, how can I add the cumulative invoiced directly into the report?
This post is just for my curiosity as i know my query works
as ive tested it a few different ways the only way i can reproduce your behaviour is to set the weeknr to text is this the case?
if so this should work
SELECT [reqWeekSalesByMonthByCountry].[weeknr], [reqWeekSalesByMonthByCountry].[Invoiced], DSum("[Invoiced]","reqWeekSalesByMonthByCountry","CINT([weeknr]) <=" &CINT( [weeknr])) AS CumulativeInvoiced
GROUP BY [reqWeekSalesByMonthByCountry].[weeknr], [reqWeekSalesByMonthByCountry].[Invoiced];
but again if it is a report then teddy's method is best
but if you need to reproduce this behaviour for a form use the method above