Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    47

    Unanswered: How to do calculations in the background? Report help!

    Hi,

    I have created a time recording system, the user enters data into a continuous form. There is a a field in the record source of the form which has a formula to calculate the total for each record.
    A grand-total is for all records is calculated.

    I have a problem with doing calculations on a report.

    I have a report which shows all the clients, their DOB and a couple other fields.
    I need to show each clients grand-total so far next to their name. The problem is that each client can have a lot of records calculate from.

    I am looking for something similar to the sales reports in the NorthWind exemplar database. I need a report where a list all of clients is produced, and in each column there is: Client Name, File Number and Grand-total.

    Can someone please help?

    Thanks

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    In reports you are basically adding totals of existing fields in that report. If you want the underlying criteria to be invisible, why not include Grand Total in the query? That way, the query does the calculation and in the report it's just another field.

  3. #3
    Join Date
    Jul 2011
    Posts
    47
    Quote Originally Posted by chris07tibgs View Post
    In reports you are basically adding totals of existing fields in that report. If you want the underlying criteria to be invisible, why not include Grand Total in the query? That way, the query does the calculation and in the report it's just another field.
    Hi chris07tibgs,

    Thanks for the reply,

    The problem is that the total for each record is calculated in the query, the totals are not already calculated.

    I have a "Total: Sum(Iff())" formula in query as a field which calculates a total for the record. There can be anywhere between 10-500 or even more Total fields to add up after they value for their total has been calculated.
    This is why I'm having a problem, I need to preform a calculation the Total field in the query to get a grand-total. I need to sum all the Totals for a particular Client to get a grand total.


    Any Ideas?
    Last edited by rasghar; 11-14-11 at 08:56.

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by rasghar View Post
    Hi chris07tibgs,

    Thanks for the reply,

    The problem is that the total for each record is calculated in the query, the totals are not already calculated.

    I have a "Total: Sum(Iff())" formula in query as a field which calculates a total for the record. There can be anywhere between 10-500 or even more Total fields to add up after they value for their total has been calculated.
    This is why I'm having a problem, I need to preform a calculation the Total field in the query to get a grand-total. I need to sum all the Totals for a particular Client to get a grand total.


    Any Ideas?
    place a field named: =Sum([FieldName]) for the field you want to total.

    You can place this field in either the report detail, report footer, or a Sorting and Grouping footer.

  5. #5
    Join Date
    Jul 2011
    Posts
    47
    Thanks for the help, it worked perfectly!

    Really appreciate it!

Posting Permissions

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