locked
MS Access use SUM function RRS feed

  • Question

  • User-1249732592 posted

    I want to use sum function  in access database (not MS SQL) to add array of items in a particular date.

    For Eg.

    The Access Database format like

    Date Prod. Name Value
    22-8-2010 10:05 AM   Pen 10
    22-8-2010 10:10 AM   Pen 12
    22-8-2010 10:15 AM   Pencil 23
    22-8-2010 10:20 AM   Pen 23
    22-8-2010 10:25 AM   Pencil 34
    22-8-2010 10:30 AM   Pen 34
    22-8-2010 10:35 AM   Pencil 34
    22-8-2010 10:40 AM   Pen 4
    22-8-2010 10:45 AM   Pen 524
    22-8-2010 10:50 AM   Pencil 24
    22-8-2010 10:55 AM   Pen 234
    22-8-2010 11:00 AM   Pen 245
    23-8-2010 10:05 AM   Pen 42
    23-8-2010 10:10 AM   Pencil 23
    23-8-2010 10:15 AM   Pen 24
    23-8-2010 10:20 AM   Pencil 23
    23-8-2010 10:25 AM   Pen 2
    23-8-2010 10:30 AM   Pencil 2
    23-8-2010 10:35 AM   Pen 2
    23-8-2010 10:40 AM   Pen 32
    23-8-2010 10:45 AM   Pencil 32
    23-8-2010 10:50 AM   Pen 2
    23-8-2010 10:55 AM   Pen 23
    23-8-2010 11:00 AM   Pen 234

     

    and I want the output Like:

     

    22-08-2010    Pen Sum of Values
    22-08-2010    Pencil Sum of Values
    23-08-2010    Pen Sum of Values
    23-08-2010    Pencil Sum of Values

    Is it possible using Query or write any program in asp.net c#.

    Thursday, August 19, 2010 3:08 AM

Answers

  • User-1199946673 posted
    SELECT DateValue([date]), [prod_name], Sum([value]) FROM products GROUP BY DateValue([date]), [prod_name]


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 19, 2010 3:32 AM

All replies

  • User-1802908944 posted

    need to use group by :

    QUERY SHOULD BY LIKE


    SELECT CREATEDATE  as ProdDate, ProdName, Sum(value)  as TotalValue FROM PVAReport  group by CREATEDATE, ProdName



    Thursday, August 19, 2010 3:30 AM
  • User-1199946673 posted
    SELECT DateValue([date]), [prod_name], Sum([value]) FROM products GROUP BY DateValue([date]), [prod_name]


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 19, 2010 3:32 AM
  • User-1199946673 posted

    need to use group by :

    QUERY SHOULD BY LIKE


    SELECT  CONVERT(NVARCHAR, CREATEDATE,105)  as ProdDate, ProdName, Sum(value)  as TotalValue FROM PVAReport  group by CONVERT(NVARCHAR, CREATEDATE,105), ProdName

     

    Please read carefully:

    "I want to use sum function  in access database (not MS SQL)"

    Thursday, August 19, 2010 3:34 AM
  • User-1199946673 posted

    need to use group by :

    QUERY SHOULD BY LIKE


    SELECT CREATEDATE  as ProdDate, ProdName, Sum(value)  as TotalValue FROM PVAReport  group by CREATEDATE, ProdName

     

    If you would look more carefull, the CREATEDATE field contains both a date and time, but the group by needs to de done by the datepart only. This can be done using the DateValue function, as I already posted. So no need to change your post once again!!!

    Thursday, August 19, 2010 5:43 AM