How to calculate the sum of a column in an MS Access table for a given date (a single day, month or year)
- by cMinor
I have a table in Access in a custom format saved as dd/MM/yyyy hh:mm:ss tt , Also A form in VB.NET 2010, I get a specific day, month and year with no problem but the problem comes when I want to query the sum of a column named value depending on a specific month or day or year....
The table is like:
+-----+-----------+-------------------------+
| id | value | date |
+-----+-----------+-------------------------+
| id1 | 1499 | 01/01/2012 07:30:11 p.m.|
| id2 | 1509 | 11/02/2012 07:30:11 p.m.|
| id3 | 1611 | 21/10/2012 07:30:11 p.m.|
| id1 | 1115 | 11/10/2012 07:30:11 p.m.|
| id1 | 1499 | 17/05/2012 07:30:11 p.m.|
| id2 | 1709 | 11/06/2012 07:30:11 p.m.|
| id3 | 1911 | 30/07/2012 07:30:11 p.m.|
| id1 | 1015 | 01/08/2012 07:30:11 p.m.|
| id1 | 1000 | 11/05/2012 07:30:11 p.m.|
|+-----+-----------+------------------------+
So I Know the query
SELECT SUM(value) FROM mytable WHERE date in='01/05/2012 00:00:00' ...
How to tell the query I want the month of May so I would get 1499+1000= 2499
Or how to tell I want the year 2012 so I would get the sum of all the table
Which would be the correct syntax...
