This is my attempt at writing an Access SQL statement but it's not quite working for me and I am not sure how to ask the query correctly to get my intended result.
I need the statement to select from my table the column named DEPT and also return the sum of the headcount value from rows which meet the criteria "Entitlement Since" (name of a column within my table which is date based) is greater than 30 months ago from todays date.
Thanks, Amber.
SELECT Table.[Dept], SUM(Tablel.Headcount) AS CurrentMonth
FROM Table
WHERE Tablel.[Entitlement Since]< DateAdd("m",-29,Date());
I need the statement to select from my table the column named DEPT and also return the sum of the headcount value from rows which meet the criteria "Entitlement Since" (which is a date) is greater than 30 months ago from todays date.
Here's an example of the Table (1) and the required return Query results (2)
(1)
DEPT Entitlement Since HeadCount
D1 1/09/2007 1
D1 1/09/2007 1
D2 1/09/2007 1
D2 1/01/2010 1
D2 1/01/2010 1
(2) There are 3 rows which fit the criteria "Entitlement Since" date value is more than 30 months ago from the current month. These rows are to be selected and sumed according to the Dept - D1 and D2.
DEPT Now
D1 2
D2 1