|
本帖最后由 marco 于 2018-9-22 16:59 编辑
SELECT 收支明细记录表.ID, 收支明细记录表.日期,收支明细记录表.账户名称, 收支明细记录表.收入, 收支明细记录表.支出,
Nz([余额])+Nz([支出])-Nz([收入]) AS 上期数,
Val(DSum("nz([收入])-nz(支出)","账户明细表","Format$([日期],'yyyymmdd') & format([id],'000')<=" & Format$([日期],"yyyymmdd") & format([id],"000"))) AS 余额,
Nz([账户余额])+Nz([支出])-Nz([收入]) AS 账户上期数,
Val(DSum("nz([收入])-nz(支出)","账户明细表","账户名称='" & [账户名称] & "' And Format$([日期],'yyyymmdd') & format([id],'000')<=" & Format$([日期],"yyyymmdd") & format([id],"000"))) AS 账户余额,
收支明细记录表.摘要, 收支明细记录表.单据号, 收支明细记录表.收支明细, 收支类别明细.简称, 收支类别明细.收支类别, 收支类别明细.序号,
Format([日期],"yyyy-mm") AS 月份, Format$([日期],"yyyymmdd") & format([id],"000") AS 余额Id,
收支明细记录表.附件张数
FROM 收支类别明细 INNER JOIN 收支明细记录表 ON 收支类别明细.明细 = 收支明细记录表.收支明细
WHERE (((收支明细记录表.日期) Between [Forms]![主表]![开始日期] And [Forms]![主表]![结束日期]) AND ((收支明细记录表.账户名称) Like [Forms]![主表]![账户名称]))
ORDER BY 收支明细记录表.日期, id |
|