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.
NO. DISHES ITEM NAME MONTH
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.
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.