Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64

    Unanswered: Summing calculated data in a report

    I have written a simple database to hold details of reprographics and stationery orders for a school.
    Everything works fine, except I cannot seem to get a total for the orders for each department at the foot of a report.
    I have the following fields on the report, which are taken from a query:
    Date, Department, Member of Staff, Item, Number in Batch, Batch Price, Quantity Ordered. I then have a calculated field to work out the price, using the formula: =([Batch Price]/[Number in Batch])*[Quantity Ordered].
    Each Department (e.g. English, Science, Maths, Geography etc) has its own page on the report, and I want to place the total price of the items ordered in the page footer.
    I've been using the formula: =Sum(([Batch Price]/[Number in Batch])*[Quantity Ordered]) to calculate the total, but all I get is an Error# message.
    I'm at a loss as to what I'm doing wrong, and hope it's something simple and obvious.
    I'm using Access 2003.

    Thanks in advance for any advice!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The formula looks okay, but it won't work in the page footer. Try it in the report footer (it should also work in the group footer, which may actually be what you want). If you want page totals, one way:

    ACC2000: How to Create Page Totals on a Report
    Paul

  3. #3
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Wouldn't the formula need to be something more like this:

    =(Sum([Batch Price])/Sum([Number in Batch]))*Sum([Quantity Ordered])

    C

  4. #4
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64
    Quote Originally Posted by pbaldy View Post
    The formula looks okay, but it won't work in the page footer. Try it in the report footer (it should also work in the group footer, which may actually be what you want). If you want page totals, one way:

    ACC2000: How to Create Page Totals on a Report
    That's spot on - it was indeed the group footer I really wanted. Everything is working exactly as it should be now - many thanks.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help!
    Paul

Posting Permissions

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