Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2006
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Ordering by calculated field (2000)

    My application deals with Customers. Customers have invoices. Invoices have line items. I wish to write a report which will tell me how much business I have done with each of my customers. Easy. I construct my query to extract the necessary information, then group my report by customer, and in, my group footer I use =Sum([GrandTotal]) to tell me how much business I have done with that particular customer. The problem comes when I need to ORDER the report by Sum([GrandTotal]). I can order by Customer, of course - alphabetically. But I cannot order by that SUM calculation because it is being done at report time and does not exist as such in the database. I know this can be done - I have seen it done in someone else's application - but I cannot for the life of me remember how to do it, and would be obliged for any guidance available.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ordering by calculated field (2000)

    Welcome to Woody's Lounge!

    You will have to include the group totals in the record source of the report. Create a totals query that returns customers and the corresponding totals.

    One option is to create a main report based on that query, and to use a subreport for the detail information.

    Another option is to create a query based on the table or query you now use and on this new query, joined on the customer id field, and use that as record source for the report.

  3. #3
    New Lounger
    Join Date
    Jun 2006
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ordering by calculated field (2000)

    Thank you very much indeed. I understand. It works. Much obliged.

Posting Permissions

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