masoud_sedighy
Registered User.
- Local time
- Today, 13:25
- Joined
- Dec 10, 2011
- Messages
- 132
I have a table and its name is “ztbl_Line_Trend” and its data is like below:
Date_List DISCIPLINE Done_Doc
9/21/2009 Building 2
9/22/2009 Building 1
9/23/2009 Building 3
9/21/2009 CIVIL 3
9/22/2009 CIVIL 1
9/23/2009 CIVIL 1
1/13/2010 Building 4
1/14/2010 Building 1
1/15/2010 Building 5
1/13/2010 CIVIL 4
1/14/2010 CIVIL 3
1/15/2010 CIVIL 2
Now I would like to make a query that summarize data by “year- month” nd “DISCIPLINE” and then calculate summary for [Done_Doc] field and also calculate cumulative [Done_Doc] for each “year-month” according to below:
Year_Month DISCIPLINE SumOfDone_Doc cummulative_Done_Doc
2009-09 Building 6 6
2009-09 CIVIL 5 5
2010-01 Building 10 16
2010-01 CIVIL 9 14
I have tried to do it, until below query, and it had not problem, but I have problem for creating cumulative column in query.
SELECT Format([date_list],"yyyy-mm") AS AllocateMonth, ztbl_Line_Trend.DISCIPLINE, Sum(ztbl_Line_Trend.Done_Doc) AS SumOfDone_Doc
FROM ztbl_Line_Trend
GROUP BY Format([date_list],"yyyy-mm"), ztbl_Line_Trend.DISCIPLINE
For creating “cummulative_Done_Doc” column, I tried to create it with a sub-query but I get “error”:
cummulative_Done_Doc: (SELECT SUM(T1.[Done_Doce]) from ztbl_Line_Trend AS T1 WHERE (YEAR([T1.Date_list])<= YEAR([ztbl_Line_Trend.Date_list])) AND (MONTH([T1.Date_list])<= MONTH([ztbl_Line_Trend.Date_list])))
Error: You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)
Date_List DISCIPLINE Done_Doc
9/21/2009 Building 2
9/22/2009 Building 1
9/23/2009 Building 3
9/21/2009 CIVIL 3
9/22/2009 CIVIL 1
9/23/2009 CIVIL 1
1/13/2010 Building 4
1/14/2010 Building 1
1/15/2010 Building 5
1/13/2010 CIVIL 4
1/14/2010 CIVIL 3
1/15/2010 CIVIL 2
Now I would like to make a query that summarize data by “year- month” nd “DISCIPLINE” and then calculate summary for [Done_Doc] field and also calculate cumulative [Done_Doc] for each “year-month” according to below:
Year_Month DISCIPLINE SumOfDone_Doc cummulative_Done_Doc
2009-09 Building 6 6
2009-09 CIVIL 5 5
2010-01 Building 10 16
2010-01 CIVIL 9 14
I have tried to do it, until below query, and it had not problem, but I have problem for creating cumulative column in query.
SELECT Format([date_list],"yyyy-mm") AS AllocateMonth, ztbl_Line_Trend.DISCIPLINE, Sum(ztbl_Line_Trend.Done_Doc) AS SumOfDone_Doc
FROM ztbl_Line_Trend
GROUP BY Format([date_list],"yyyy-mm"), ztbl_Line_Trend.DISCIPLINE
For creating “cummulative_Done_Doc” column, I tried to create it with a sub-query but I get “error”:
cummulative_Done_Doc: (SELECT SUM(T1.[Done_Doce]) from ztbl_Line_Trend AS T1 WHERE (YEAR([T1.Date_list])<= YEAR([ztbl_Line_Trend.Date_list])) AND (MONTH([T1.Date_list])<= MONTH([ztbl_Line_Trend.Date_list])))
Error: You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)