Running YTD totals

[Guest]

Hi all

I have a query which displays running MTD totals, I want to add a column to
this for the running YTD total too. I think I need to use DSUM for this but
can not get the syntax correct. I have tried finding a solution on the
newsgroups and used an example:

YTDTotal: DSum("[qry CB YTD].[CBEuros]","[qry CB YTD]","[ReportingDate]<=" &
[reportingdate])

but then I read a posting which said this will only work if the
ReportingDate is unique, which it isn't. It is grouped by each country for
MTD totals. It is also not a date field, stored 2005-01, 2005-02 etc for the
sorting order to be correct on a report.

Can anyone help with this?

Thanks in advance...
Sue

 

[Larry Linson]

"Totals in Queries" can be difficult. "Totals in Reports", which is where
they are most often viewed, is relatively easy. There is, in fact, a Running
Sum property on Text Boxes... what you want to do _may_ be as simple as
setting that property to Yes.

Larry Linson
Microsoft Access MVP

 

[Guest]

Thanks Larry, this helps to get one of the group totals - although I did have
my report sorted in descending order so it showed the latest months figures
first and then the running sum total is wrong. I am struggling to show each
countries YTD totals per month. Please see example data below, this is just
for the latest month, all months in the year so far are produced on the
report below this one in the same layout. The forecast figure is calculated
using other fields (YTD/total of audits * forecast audits). I think I maybe
need two queries/reports to be able to show all data but I am not sure how to
achieve it?

Summary - June 2005
Market Month YTD Forecast
UK € 84,000 € 520,000 € 950,000
Germany € 346,479 € 78,000 € 130,000
Ireland € 153,967 € 350,000 € 650,000
Finland € 0 € 0 € 0
Belgium € 18,000 € 120,000 € 175,000
Austria € 269,506 € 122,000 € 180,000
France € 231,023 € 80,000 € 150,500
Spain € 12,000 € 12,000 € 180,000
Total: € 1,114,975 € 1,282,000 € 2,415,500

Thanks in advance for any help.
Sue

"Totals in Queries" can be difficult. "Totals in Reports", which is where
they are most often viewed, is relatively easy. There is, in fact, a Running
Sum property on Text Boxes... what you want to do _may_ be as simple as
setting that property to Yes.

Larry Linson
Microsoft Access MVP

Hi all

I have a query which displays running MTD totals, I want to add a column
to
this for the running YTD total too. I think I need to use DSUM for this
but
can not get the syntax correct. I have tried finding a solution on the
newsgroups and used an example:

YTDTotal: DSum("[qry CB YTD].[CBEuros]","[qry CB YTD]","[ReportingDate]<="
&
[reportingdate])

but then I read a posting which said this will only work if the
ReportingDate is unique, which it isn't. It is grouped by each country for
MTD totals. It is also not a date field, stored 2005-01, 2005-02 etc for
the
sorting order to be correct on a report.

Can anyone help with this?

Thanks in advance...
Sue