How do I SUM it up?

[nonturbo]

I've got a report with 2 levels of grouping. First is account number, then period of the year. Most accounts have a row for each period: 1, 2, 3, and 4; but some accounts were opened or closed during the year, so some accounts might just have a few periods.

The "Account Number Header" contains the data thats visable on the form.

"Period Header" contains the data for each period, but since the report is for the year, this section is NOT visable.

The "Details" section is blank and NOT visable.

In Account Number Header, I have a few fields that are all set to currency. Most of the fields are totals of things for the year, such as sales. So for the sales I have a text box set to =Sum([Sales]) and Sales again in Period Header. This works. So far so good.

Problem #1: I'd like to have a field display something from just the fourth period in the Account Number Header. I can't seem to do this without unbinding the text box control and setting the value through some VBA. I'd also like to have a Running Sum total in the report footer for this field. Since I can't run Sum()'s on calculated controls, I need to stay away from VBA. ....Got it?

Can anyone verify if what I'm trying to do is even possible? Thanks in advance, if any part doesn't make any sense, just ask and I'll verify.

NT

 

[jbseryan]

NT,

I am having a very similar problem. I've got a calculated sub-total field in one of my report groupings that is set to FIXED decimal places (Access converts this value to TEXT), but I want to add the rounded subtotals in the report footer. Does not work. Have you had any luck since you posted on 1/17/03?

j

 

[randysmid]

Hi to both of you,
You can use the DSum function to get just about any calculated totals for anything within your database tables. Here is an example:

In this example, a YTD sales amount is needed on the report, regardless of the period being printed.

If this is what you need, you can use the DSum function inside your report footer (or anyplace you choose to place it). To do this, simply add a textbox to the location you want, then add the DSum statement as follows for the recordsource of the textbox:

=DSum("[Sales_amt]","tblSales","[Sales_date] between #01/01/2003# and #12/31/2003#&quot

Presumptions:
The field containing the sales amount is called Sales_amt.
The table containing the sales figures is called tblSales.
The field containing the sales date is called Sales_date.


HTH,
Randy Smith
California Teachers Association