MS Access use SUM function

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#.

Answer 1

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

Answer 2

User-1199946673 posted

SELECT DateValue([date]), [prod_name], Sum([value]) FROM products GROUP BY DateValue([date]), [prod_name]

 

Answer 3

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)"

Answer 4

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!!!