Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Location
    Charlottesville, VA
    Posts
    18

    Unanswered: Sutotals in reports

    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:

    John Adams SubtotalFood SubAdvances SubLoans SubUniforms
    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?

    Thanks in advance!

    - John Peterson

  2. #2
    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. #3
    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. #4
    Join Date
    Feb 2003
    Location
    Charlottesville, VA
    Posts
    18

    Talking 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. #5
    Join Date
    Feb 2003
    Location
    Charlottesville, VA
    Posts
    18

    Wink 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
  •