Re: Calculate Year To date Totals
Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance
You are right: if you filter for one month, the approach I suggested will
not give the YTD figures.
Couple of alternatives:
1. A DSum() expression in the Control Source of the text box:
Example:
=DSum("Amount", "Table1", "([Location] = """ & [Location] &
""") AND ([InvoiceDate] Between " &
Format(DateSerial(Year([InvoiceDate]),1,1), "\#mm\/dd\/yyyy\#") & " And " &
Format(DateSerial(Year([InvoiceDate]), Month([InvoiceDate])+1,0),
"\#mm\/dd\/yyyy\#") & ")")
2. A subquery in the report's recordsource.
If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dave C" <DaveC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3AA8E402-BB49-4227-8108-00006F79DE70@xxxxxxxxxxxxxxxx
> Allen,
> Thanks for your quick reponse. I've had a look at what you suggested and
> it
> goes some way to achieving what I need. Your suggestion seems to require
> each
> successive month totals to be included on the report in order to achieve
> the
> aggregated annual total, but what I really need is a summary report
> including
> the totals for a single month (for each location), together with the full
> year to date totals. Ideally they should be printed on a single row. See
> below for a rough example of what I'm trying to achieve.
>
> Month Location Interest(Month) Tax(Month) Interest(YTD)
> Tax(YTD)
> August
> A 100.00 20.00
> 1000.00 200.00
> B 50.00 10.00
> 500.00 100.00
> -----------------------------------------------------------------------------------------------
> Total 150.00 30.00
> 1500.00 300.00
>
> ------ end of report ------
>
> I'm not sure of this can be achieved within the report itself or if it
> needs
> to commence within the underlying query, as I need to limit the visible
> data
> on the report to a single month (in the above case, August), per location,
> but at the same time provide the year to date totals for the individual
> locations as well.
>
> Thanks & Regards,
>
> David
>
>
>
>
> "Allen Browne" wrote:
>
>> In report design view, open the Sorting And Grouping dialog.
>> Choose the date field, and in the lower pane of the dialog set:
>> Group Header: Yes
>> Group On: Year
>> This gives you a group header to put the year in.
>>
>> On the 2nd row of the dialog, choose the date field again.
>> This time, set:
>> Group Footer: Yes
>> Group On: Month
>>
>> In this Group Footer section, add another text box with these properties:
>> Control Source: =Sum([Amount])
>> Running Sum: Over Group
>> replacing "Amount" with the name of the field you want to sum YTD.
>>
>> The text box will accumulate the totals over the group. Since the parent
>> group is the year, it will reset each year.
>>
>> "Dave C" <DaveC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:F9D5C98D-D7D6-4B21-905A-0A559B3ACC52@xxxxxxxxxxxxxxxx
>> >I have a table that contains the monthly values (one record for the
>> >monthly
>> > totals of each account) from which I need to produce a report that
>> > shows
>> > not
>> > only the value for each of the various financial fields for a
>> > particular
>> > month, but an accumulated Year To Date (calendar year) value for each
>> > of
>> > the
>> > various fields.
>> >
>> > eg: The report for August would show the values for August plus
>> > additional
>> > columns showing the sum of the monthly values January - August.
>> >
>> > The month values aren't a problem but I'm struggling with how to
>> > calculate
>> > the Year to Date totals.
.
Relevant Pages
- Re: Calculate Year To date Totals
... Your suggestion seems to require each ... successive month totals to be included on the report in order to achieve the ... but what I really need is a summary report including ... > replacing "Amount" with the name of the field you want to sum YTD. ...
(microsoft.public.access.reports) - Re: Need Help Summing Sales and Donations in Report Footer
... group a Totals Query on that many fields -- it boggles my mind, ... into the report, as in the form, change the textbox for tbl_Income.bank, ... chance of making a useful suggestion. ...
(microsoft.public.access.reports) - Re: How to count a field
... Can you please tell me whether you have attempted to follow my suggestion, and whether this produces the result you require for the Inpatient count. ... is [strIn patient] combo box with values of 'yes' for in patient and 'no' for outpatient. ... On the report, report footer unbound box I wrote ... what I would like is 2 totals one of in patient and the other of out patients. ...
(microsoft.public.access.reports) - Re: German paratroopers
... Losses in the 8th Brigade are fairly completely reported. ... killed and wounded for a total of 113 casualties, ... battalion to report losses on D-Day in that brigade. ... These postwar totals are 17 percent higher than those reported after ...
(soc.history.war.world-war-ii) - Re: subreport/report total question
... it gives me the exact same totals as ... the job footer should do). ... In the subreport, move your DayTotal text box into the Report Footer ... Page Header ...
(microsoft.public.access.reports) |
|
