Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004

    Unanswered: Urgent - Calculating total fields in query - not working, how to fix?


    I made a post last night, and Mike offered a solution to my problem which was, unfortunately, the method I had already been using.

    I have a query that should run and display a report, of the total number of dishes (of a certain menu item) that were ordered during a user-specified period of time (using a parameter date range query in the DateOrdered field).

    At the moment, my query is set up to display data in the following way, assuming that we are talking about the number of pavlova dishes ordered throughout the year 2004.

    4 Pavlova January
    17 Pavlova February
    4 Pavlova March

    My Query is set up, in Design view, as follows:

    QUANTITY (Sorted by Sum)
    MENUITEMID (Group By)
    NAME (Group By)
    CATEGORY (Group By)
    MONTH (calculated field)
    YEARFROM (calculated field)
    DATEORDERED (not visible; date range parameter query)

    What I need is a way of not only showing the above information in my report, but also telling the user that throughout 2004, 25 pavlova dishes were ordered in total; and the same for all other individual menu items.

    How to do this?

    Any/all help is appreciated.



  2. #2
    Join Date
    May 2004
    You could use a subreport within your report for the specific MenuItems. You would then need to use the =Count(*) expression to get the totals. The Northwind.mdb that comes with Access would have good examples on how to do this sort of thing.

  3. #3
    Join Date
    Mar 2004
    Here is what you do...

    iif([table].[field] = 'pavlova',1,0) as pavTotal

    Then on the report make a text box with =sum(pavTotal)

    Note this only works if you are not filtering the records out in the query OR report.

    if you ARE filtering this stuff out but you need a total...

    for example if the report shows all dishes ordered today BUT you want a total of ALL dishes you ever made. then you need to do a DCount function.

    here it is:

    =DCount("[field]","[table]","[field] = 'value'")
    that in a textbox

    also works in queries... ud need to write it as an expressin without the = at the start and add 'As NAME' on the end

    NOTE: if you are building a query using the access wizard expressions are formated differently ( instaid of having an 'AS something' at the end you need to write 'something: expression')

  4. #4
    Join Date
    Dec 2003
    Dallas, TX

    Red face

    Hi graceadair,

    Did you check the sample DB I posted yesterday to help you out with totals in your report?


Posting Permissions

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