1. Registered User
Join Date
Feb 2003
Location
Charlottesville, VA
Posts
18

This is easy in excel, seemingly impossible in Access(XP, db=2000 fromat). Namely, need to list subtotals horizontally. I have a table (Charges: Date, shift, name, chargetype, amount). Need to list the charges, by name, with subtotals for each chargetype. Would look like:

Charge 1Date type amount
Charge 2 date type Amount
etc

Tom Bostwick SubtotalFood SubAdvances SubLoans etc
Charge1date type amount
Charge2date type amount
Charge3date type amount

Jeff Smith etc ect etc

I'm also limiting the report to a certain start and end date. Am I missing something, or is this really difficult?

- John Peterson

2. Registered User
Join Date
Dec 2002
Posts
60

## Re: Sutotals in reports

Do I understand this correctly: You want to list all the charges then at bottom of form/report you want subtotals for each charge type.

If so, try using IIf function. Put a running sum field for each charge type in the form/report detail section. Set Control Source to:
'=IIf([txtChargeType]="Type1",[txtAmount],0)'

Where [txtChargeType] is the field containing charge type
"Type1" is a charge type
[txtAmount] is field containing amoutn

You need to put the IIf in each running sum, changing Type1 to appropriate type.

At form/report footer, refereance appropriate running sub to obtain subtotals.

3. Registered User
Join Date
Feb 2003
Location
Charlottesville, VA
Posts
18

## Re: Sutotals in reports

Originally posted by bc301
Do I understand this correctly: You want to list all the charges then at bottom of form/report you want subtotals for each charge type.

If so, try using IIf function. Put a running sum field for each charge type in the form/report detail section. Set Control Source to:
'=IIf([txtChargeType]="Type1",[txtAmount],0)'

Where [txtChargeType] is the field containing charge type
"Type1" is a charge type
[txtAmount] is field containing amoutn

You need to put the IIf in each running sum, changing Type1 to appropriate type.

At form/report footer, refereance appropriate running sub to obtain subtotals.
-----------------------------------------
Thanks BC, appreciate the response! Hadn't thought of IIF in detail section - I worry that the performance penalty might be severe. Actually, I need to put the subs in the header section for each name, followed by list of each charge below. The report is filtered to a date range...
I tried dsum, but the syntax is tortured for multiple criteria.
I'm working on sourcing from a crosstab query, just need to get the subtotals squared away...

- John Peterson

4. Registered User
Join Date
Feb 2003
Location
Charlottesville, VA
Posts
18

## Ok Finally got it, using mutiple queries

Thanks again for the post!
However, you can't sum on calculated fields (such as iif's)

Finally got it by making up a separate query for each subarea, then feeding these into a master query.

Basically, Access is very weak on columned and tabular reporting. Yuch!

5. Registered User
Join Date
Feb 2003
Location
Charlottesville, VA
Posts
18

## Re: Ok Finally got it, using mutiple queries

Originally posted by JohnPeterson
Thanks again for the post!
However, you can't sum on calculated fields (such as iif's)

Finally got it by making up a separate query for each subarea, then feeding these into a master query.

Basically, Access is very weak on columned and tabular reporting. Yuch!
------
Oops - actually solved this one using crosstab query, then solved a later nested (3-in-3) column challenge using multiple queries...

-jtp

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•