Access 中如何实现行数据之间的计算

chench

最近做一个东东，涉及到行之间关系计算，这在excel中比较容易实现，但是数据量太大。 要实现的结果如附件所示。
前四行不需要动，后两行是我想添加的，里面有公式。呵呵，帮帮忙看如何实现。

zhutony

没明白楼主的意思
是要在access中添加类是附件中的两行数据吗？

chench

恩，要添加最后的两行，这两行的数据是按照公式算出来的。

Scarlett_88

呵呵，你的公式有乱码呢，我将你的公式往后拖
红色部分数据源放到Sheet2表中，来一个长的SQL语句如下：
select * from [sheet2]
union all
select "PXadj",5,0 as c1,iif(a5<>a6,b5,c1) as c2,iif(a6<>a7,b6,c2) as c3,iif(a7<>a8,b7,c3) as c4,iif(a8<>a9,b8,c4) as c5,iif(a9<>a10,b9,c5) as c6,iif(a10<>a11,b10,c6) as c7,iif(a11<>a12,b11,c7),null from
(
select sum(M5_2008) as a5,sum(M6_2008) as a6,sum(M7_2008) as a7,sum(M8_2008) as a8,sum(M9_2008) as a9,sum(M10_2008) as a10,sum(M11_2008) as a11,sum(M12_2008) as a12,sum(M5) as b5,sum(M6) as b6,sum(M7) as b7,sum(M8) as b8,sum(M9) as b9,sum(M10) as b10,sum(M11) as b11 from
(
select M5_2008,M6_2008, M7_2008, M8_2008, M9_2008, M10_2008, M11_2008, M12_2008,0 as M5,0 as M6,0 as M7,0 as M8,0 as M9,0 as M10,0 as M11 from [Sheet2$] where row="GBD" union all select 0,0,0,0,0,0,0,0,M5_2008,M6_2008, M7_2008, M8_2008, M9_2008, M10_2008, M11_2008 from [Sheet2$] where row="PX"
)
)
union all
select "policy year",6,0 as d1,iif(c1<>c2,d1+1,d1) as d2,iif(c2<>c3,d2+1,d2) as d3,iif(c3<>c4,d3+1,d3) as d4,iif(c4<>c5,d4+1,d4) as d5,iif(c5<>c6,d5+1,d5) as d6,iif(c6<>c7,d6+1,d6) as d7,iif(c7<>c8,d7+1,d7),null
from
(
select "PXadj",5,0 as c1,iif(a5<>a6,b5,c1) as c2,iif(a6<>a7,b6,c2) as c3,iif(a7<>a8,b7,c3) as c4,iif(a8<>a9,b8,c4) as c5,iif(a9<>a10,b9,c5) as c6,iif(a10<>a11,b10,c6) as c7,iif(a11<>a12,b11,c7) as c8 from
(
select sum(M5_2008) as a5,sum(M6_2008) as a6,sum(M7_2008) as a7,sum(M8_2008) as a8,sum(M9_2008) as a9,sum(M10_2008) as a10,sum(M11_2008) as a11,sum(M12_2008) as a12,sum(M5) as b5,sum(M6) as b6,sum(M7) as b7,sum(M8) as b8,sum(M9) as b9,sum(M10) as b10,sum(M11) as b11 from
(
select M5_2008,M6_2008, M7_2008, M8_2008, M9_2008, M10_2008, M11_2008, M12_2008,0 as M5,0 as M6,0 as M7,0 as M8,0 as M9,0 as M10,0 as M11 from [Sheet2$] where row="GBD" union all select 0,0,0,0,0,0,0,0,M5_2008,M6_2008, M7_2008, M8_2008, M9_2008, M10_2008, M11_2008 from [Sheet2$] where row="PX"
)
)
)

chench

非常感谢Scarlett_88版主！！！，

版主的解法是基于a5=sum(M5_2008)...来判断的，对于有多个相同格式的数据，我还是不能处理。可能我最初的表述并没有反映我最终想要的结果，我重新整理了一下我的示例。希望能再次等到版主的支持！
"Org"sheet 是最初的表，“cal”sheet是中间计算PY和PXAdj的表，“final”sheet是我最终想要的表
再次感谢！！！

[ 本帖最后由 chench 于 2009-9-8 17:24 编辑 ]

litong999

非常感谢版主..........

p58535860

版主真厉害啊，

chench

版主何时再出现啊？？？？