|
- TRANSFORM Sum(Amount) AS Amount之总计
- SELECT Account_No2, Account_Name, Description, Crt_DateFrom, Crt_DateTo, Sum(Amount) AS [Sub-Total], CCur(Avg(Round([Amount],0))) AS Monthly
- FROM (
- SELECT a.Account_No2, a.Account_Name, c.Description, c.Crt_DateFrom, c.Crt_DateTo, format(Trn_Date,'yyyy-mm-00') as CMonthID,(c.Amount)
- FROM Table_Account a INNER JOIN Table_Contract c ON a.Account_No = c.Account_No
- WHERE (((c.Trn_Date)>#1/1/2008#))
- UNION all
- SELECT 'total' as Account_No2, null as Account_Name, null as Description, null as Crt_DateFrom , null as Crt_DateTo,
- format(Trn_Date,'yyyy-mm-00') as CMonthID, Sum(c.Amount)
- FROM Table_Account a INNER JOIN Table_Contract c ON a.Account_No = c.Account_No
- WHERE c.Trn_Date>#1/1/2008#
- GROUP BY format(Trn_Date,'yyyy-mm-00')
- )
- GROUP BY Account_No2, Account_Name, Description, Crt_DateFrom, Crt_DateTo
- ORDER BY CMonthID DESC
- PIVOT CMonthID;
复制代码
******************
* 一切皆有可能 *
******************
.
ACMAIN - Access论坛回贴准则(个人).
.
同一贴子不回复第二次
.
QQ群 48866293 / 12035577 / 7440532 / 13666209
http://forum.csdn.net/BList/OtherDatabase .
http://www.accessbbs.cn/bbs/index.php .
http://www.accessoft.com/bbs/index.asp .
http://www.access-programmers.co.uk/forums .
http://www.office-cn.net .
.
http://www.office-cn.net/home/space.php?uid=141646 . |
评分
-
查看全部评分
|