Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2008
    Posts
    101

    Unanswered: Totals Help on report

    I have a query rnning on a report which has this code:
    SELECT workflow.Source, Count(workflow.Source) AS NoContactsBySource, Sum(IIf([Owner]>'',1,0)) AS Completed, Sum(IIf([Right Party Contact]=-1,1,0)) AS RPC, Sum(IIf([Mortgage Sale]=-1,1,0)) AS Application, [Completed]/[RPC] AS RPCcalls, [Application]/[NoContactsBySource] AS OPPpenetration, [Application]/[RPC] AS RPCtoappPenetration, Sum(IIf([Non Sales Call]=-1,1,0)) AS NonApp
    FROM workflow
    WHERE (((workflow.[Preferred Contact Date]) Between [Enter Start Date] And [Enter End Date]))
    GROUP BY workflow.Source;

    But im trying to get a total box for each column....im using the Sum([NoContactsBySource]) as a control source at the page footer on a text box but it keeps on saying #error, even though it should give a total of 8....
    any ideas?
    thanks

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try the report footer rather than the page footer (with an equals sign at the beginning).
    Paul

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ▲ That should do it
    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

  4. #4
    Join Date
    Sep 2008
    Posts
    101
    thanks both but how would i total percentages?

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Taking you literally, there are two ways:

    1. Make sure the percentage calculation is done in the query and then use Sum on that column.
    2. Use the same percentage formula on the other total text boxes in the report.
    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
    Sep 2008
    Posts
    101
    what i mean is im trying to tal the percentages in my report at the report footer but when i use the expression =sum([Mycolumnname])
    it brings back an error....only on the percentages it sums the figures correctly.
    thanks

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    What is [Mycolumname]? Is it a textbox on the report with a calculation in it or is it a textbox with a reference to the calculation column in the query?
    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

  8. #8
    Join Date
    Sep 2008
    Posts
    101
    its a textbox with a reference to the calculation column in the query.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Good. Then it should work. Are there any divide by zero errors in the query column? Any nulls? Are the values sitting on the right side of the cell or the left?

    You might try

    =Sum(CDbl([Mycolumnname]))

    But I'm not sure I have enough information yet
    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

  10. #10
    Join Date
    Sep 2008
    Posts
    101
    yeah there will be some divide by zero which in my colum results in shows #error but shouldnt be any nulls,values are centered?
    cheers

  11. #11
    Join Date
    Sep 2008
    Posts
    101
    its given me the message the expression is typed incorrectly or is too complex....
    any help?

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Pretty hard when you don't give us your expression!

    You must resolve those div/0 errors first.
    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

  13. #13
    Join Date
    Sep 2008
    Posts
    101
    this is my sql coding for the query which gives #error in the results
    SELECT workflow.Source, Count(workflow.Source) AS NoContactsBySource, Sum(IIf([Owner]>'',1,0)) AS Completed, Sum(IIf([Right Party Contact]=-1,1,0)) AS RPC, Sum(IIf([Mortgage Sale]=-1,1,0)) AS Application, [Completed]/[RPC] AS RPCcalls, [Application]/[NoContactsBySource] AS OPPpenetration, [Application]/[RPC] AS RPCtoappPenetration, Sum(IIf([Non Sales Call]=-1,1,0)) AS NonApp
    FROM workflow
    WHERE (((workflow.[Preferred Contact Date]) Between [Enter Start Date] And [Enter End Date]))
    GROUP BY workflow.Source;

    how would i change it to not show #error and display "0" instead?

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

    IIf(Divisor = 0, 0, YourCalculation)
    Paul

  15. #15
    Join Date
    Sep 2008
    Posts
    101
    Sorry guys i screwed up i was using the figures from the report as individual figures rather than using the totals of the colums and dividing them as a percent....
    thanks a lot for your guys

Posting Permissions

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