access 字段内容作为一行

[icedut]

----------------------问题1-------------------------
tablea 字段
id siz quantity
tableb
id siz1 siz2 siz3
1 1 6 4
2 2 5 6

想把tableb中数据放导tablea中
id siz quantity
1 1 1
1 2 6
1 3 4
2 1 2
2 2 5
2 3 6
怎样能写一条语句把它放进去呢

其实我的列也不是很多
一次一次的插入也可以
不过觉得有点麻烦

[icedut]

----------问题2反过程怎么做---------------
tablea中
id siz quantity
1 1 1
1 2 6
1 3 4
2 1 2
2 2 5
2 3 6
查询结果
id siz1 siz2 siz3
1 1 6 4
2 2 5 6

有人说使用交叉表查询向导就OK了,交叉表查询向导我怎么没有看到阿

------------------------------------
access中是否能用case when then
我用后就包错了

[icedut]

我用交叉表不行啊

[yoki]

insert into tablea
SELECT id,sum(siz1), sum(siz2), sum(siz3)
FROM
(select id,
IIF(t2.siz = 1, c, null) AS siz1,
IIF(t2.siz = 2, c, null) AS siz2,
IIF(t2.siz = 3, c, null) AS siz3
FROM tableb t2
)t1
Group By t1.id

[yoki]

Access中不能用case ，用IIF代替

[yoki]

怎么个不行法？我试了可以呀

[icedut]

m_strSQL = "SELECT AddDT,Bar9,Color,sum(siz34), sum(siz36), sum(siz38) " _
& "From " _
& "(select AddDT,Bar9,Color, " _
& " IIF(t2.siz = 34, Quantity, null) AS siz34," _
& " IIF(t2.siz = 36, Quantity, null) AS siz36," _
& " IIF(t2.siz = 38, Quantity, null) As siz38 " _
& " FROM ShopAdd t2 )t1 " _
& " Group By t1.AddDT,t1.Bar9,t1.Color"
Set rs = g_dbName.OpenRecordset(m_strSQL)

按照你的方法，我的写法如上,错误提示“from子句错误”
null改成0 错误提示“标准表达式中数据类型”

vb6+dao+access2000
siz字段--文本
quantity--数字

[icedut]

m_strSQL = "SELECT AddDT,Bar9,Color,sum(siz34), sum(siz36), sum(siz38) " _
& "From " _
& "(select AddDT,Bar9,Color, " _
& " IIF(t2.siz = 34, Quantity, null) AS siz34," _
& " IIF(t2.siz = 36, Quantity, null) AS siz36," _
& " IIF(t2.siz = 38, Quantity, null) As siz38 " _
& " FROM ShopAdd t2 )t1 " _
& " Group By t1.AddDT,t1.Bar9,t1.Color"
Set rs = g_dbName.OpenRecordset(m_strSQL)

按照你的方法，我的写法如上,错误提示“from子句错误”
null改成0 错误提示“标准表达式中数据类型不匹配”

vb6+dao+access2000
siz字段--文本
quantity--数字

[icedut]

m_strSQL = "SELECT AddDT,Bar9,Color,sum(siz34), sum(siz36), sum(siz38) " _
& "From " _
& "(select AddDT,Bar9,Color, " _
& " IIF(t2.siz = '34', Quantity, null) AS siz34," _
& " IIF(t2.siz = '36', Quantity, null) AS siz36," _
& " IIF(t2.siz = '38', Quantity, null) As siz38 " _
& " FROM ShopAdd t2 )t1 " _
& " Group By t1.AddDT,t1.Bar9,t1.Color"
Set rs = g_dbName.OpenRecordset(m_strSQL)

Set DataModify.Recordset = rs
DataModify.Refresh
这样就好了
不过有个错误
发生在DataModify.Refresh ---data access error

[icedut]

楼上的
不行啊

[icedut]

: