Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2010
    Posts
    9

    Unanswered: Summing by category

    Hi,

    I have a select query which displays the items ordered for a customer. I use this as the Record Source for the subform.

    Some of the items in the order table have the same category. for example 'Dolls" or 'Clothes'

    I would like to display in the subform, subtotals for Dolls, Clothes etc.

    So, grouping the price for all the Dolls
    and another field which groups all the Clothes.

    The challenging thing is the category description is dynamic. it could be Dolls, one week, then Dollies the next. So what I am saying is I can't use a fix criteria for the category field as it changes every so often.

    P.S Dolls and Clothes are merely examples not actually categories
    thanks
    B

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This query will give you the number of rows with the same item for each category:
    Code:
    SELECT Category, Count(Quantity) AS ItemCount
    FROM OrdersTable
    GROUP BY Category
    ORDER BY Category;
    While this one will return the total of items for each category
    Code:
    SELECT Category, Sum(Quantity) AS ItemTotal
    FROM OrdersTable
    GROUP BY Category
    ORDER BY Category;
    In both examples [Category] is the name of the column (field) identifying the category of an item while [Quantity] contains the number of items for one row (record). The ORDER BY parts are optional.
    Have a nice day!

Posting Permissions

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