Results 1 to 12 of 12
  1. #1
    Join Date
    May 2006
    Posts
    29

    Unanswered: get sum at the bottom of report detail section

    Hi, In my report detail section, I have two textboxes, type and count, that link to the two fields of the report recordsource. I want to display the total count underneath the last record.
    However, if I add a textbox underneath the "count" textbox, and set the control source to be =sum([Count]), the total count prints at the bottom of each record, instead of the very last record.

    Is there anyway the total count will only display after the last record?

    Thanks.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Have you tried the report footer section?
    Paul

  3. #3
    Join Date
    May 2006
    Posts
    386
    I am sure if you try Pbaldy's suggestion it should work. I have been trying same thing just few minuts ago and it works. But I am still struggling with the grand total of all these sub totals.
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by Emal
    I am sure if you try Pbaldy's suggestion it should work. I have been trying same thing just few minuts ago and it works. But I am still struggling with the grand total of all these sub totals.
    Hi Emal,

    Do you have several totals for several groups that you need the Grand Totals for? I mean like in mine, there are many different Jobs and I have reports that list many jobs of my choosing, and each job has it totals at the bottom and then at the very end there is a sum of ALL of those totals. Sounds like what you're talking about?
    Just so you know, I use my JobID field and created a Footer for it. That footer creates a Sum for each Job that results in my search. It's called the JobID footer. Then the Grand Totals are placed in the ReportFooter which comes at the end of the report.
    Can you take it from there and get it going? If not let me know and I'll try to get if for you.

    have a nice one,
    BUD

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    There is nothing difficult here.

    Detail section is for EACH RECORD.
    Group Footer/Header is for statistics of EACH GROUP
    Report Header/Footer is for statistics of the WHOLE REPORT

    A =Sum(Fieldname) in the report header/footer will give you a total of Fieldname for the whole report. The same expression in a group section's footer would give you a total for each group. Using aggregate functions such as Sum doesn't really belong in the Detail section.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    May 2006
    Posts
    29
    It works when I put an additional textbox that the control source = sum([Count]) in Report Footer. However, if the TYPE is null, the COUNT is zero. I also need the count of NULL TYPE. Do you have an idea how I can get it?

    Thanks a lot for all your helps!

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try

    =Sum(IIf(IsNull(TYPE), 1, 0))
    Paul

  8. #8
    Join Date
    May 2006
    Posts
    29
    It says the expression "=Sum(IIf(IsNull(TYPE), 1, 0))" is invalid.

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post a sample db?
    Paul

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    However, if the TYPE is null, the COUNT is zero. I also need the count of NULL TYPE. Do you have an idea how I can get it?
    Use an expression for TYPE that prohibits nulls in the report's underlying query:

    EG

    TypeA: NZ(Type,"N/A")
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    May 2006
    Posts
    29
    Thanks, StarTrekker!

    I solved the problem by updating the behind query. I basically have two select statements:
    select ...
    from (
    select...
    from...) a.

    In the inner(2nd sql), I add a "count(*) as total" and then add group by at the end. Then I add "total" in the outer select statement. It gives me the # of NULL!! But the sql statement looks ugly...

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome

    All SQL statements look ugly to me ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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