Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    80

    Unanswered: Subtotals in reports (from queries)

    I'm creating a query that determines the quantities of individual menu items ordered over a certain period of time. The fields in the MealOrder table are MenuItemID and Quantity. In the report that will result from this query, I hope to have each individual food item with a different subtotal. For example, I might have:

    (For the month of December)
    There were 18 spaghetti bolognaise dishes ordered.
    There were 64 chicken nugget dishes ordered.

    ...as opposed to:

    (For the month of December)
    There were 82 menu items ordered.

    How would I do this using a relatively simple calculation (as in, not lines and lines and lines of code)?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    All depends on where your Menu Item Names are, but to do this by MenuItemID the SQL would be along the lines of:

    Select MenuItemID, sum([Quantity]) AS Total
    FROM MealOrder
    Group By MenuItemID

    Actually, the more complicated bit is likely to be the grouping by month.

    Also, reread you post - if you are talking about a report then keep whatever query you currently have and use it as the report record source. Create a MenuItemID group header, put in two text boxes side by side. Enter MenuItemID as the control source for one and =Sum(IIf([MenuItemID] Is Not Null,Quantity,0)) for the other. There's actually many, many ways of geting totals in a report. This way creates a list of all Menu Items and their corresponding number of orders. Other people could well suggest alternatives.
    To group by month, place a month header above the Menu ID one.
    Last edited by pootle flump; 05-13-04 at 13:50.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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