大侠 求助 ACC数据使用没有问题，SQL数据库 出现#1248错误

[斯拓克]

Dim FindStr As String
FindStr = "select 产品编码,期初数量 as a,0 as c,0 as e from 产品目录 "
FindStr = FindStr & " Union Select 产品编码,入库数量-出库数量,0,0 from 产品出入库明细"
FindStr = FindStr & " Union Select 产品编码,0,入库数量,出库数量 from 产品出入库明细"
FindStr = "Select 产品编码,sum(a) as 期初数量,sum(c) as 入库数量,sum(e) as 出库数量,(sum(a)+sum(c)-sum(e)) as 期末数量 from (" & FindStr & ") Group by 产品编码"
FindStr = "Select a.产品编码,产品名称,分类,期初数量,入库数量,出库数量,期末数量 from 产品目录 a Left Join (" & FindStr & ") b on a.产品编码=b.产品编码 ORDER BY 分类 DESC"
Call openRs(FindStr)

[砸死牛顿的苹果]

缺失表别名,access可能比较不严格
from (" & FindStr & ") Group by 产品编码
==>
from (" & FindStr & ") t Group by 产品编码

[斯拓克]

现在出现#1052
Select a.产品编码,产品名称,分类,期初数量,入库数量,出库数量,期末数量 from 产品目录 a Left Join (
SELECT 产品编码, sum( a ) AS 期初数量, sum( c ) AS 入库数量, sum( e ) AS 出库数量, (
sum( a ) + sum( c ) - sum( e ) ) AS 期末数量
FROM (SELECT 产品编码, 期初数量 AS a, 0 AS c, 0 AS e
FROM 产品目录
UNION SELECT 产品编码, 入库数量 - 出库数量, 0, 0
FROM 产品出入库明细
UNION SELECT 产品编码, 0, 入库数量, 出库数量
FROM 产品出入库明细
)t GROUP BY 产品编码) b on a.产品编码=b.产品编码 ORDER BY 分类 DESC

[斯拓克]

大侠帮帮忙现在出现1052错误
这句没有问题
FindStr =”Select 产品编码,sum(a) as 期初数量,sum(c) as 入库数量,sum(e) as 出库数量,(sum(a)+sum(c)-sum(e)) as 期末数量 from (select 产品编码,期初数量 as a,0 as c,0 as e from 产品目录 Union Select 产品编码,入库数量-出库数量,0,0 from 产品出入库明细 Union Select 产品编码,0,入库数量,出库数量 from 产品出入库明细) t Group by 产品编码“

最后一句
FindStr = "Select a.产品编码,产品名称,分类,期初数量,入库数量,出库数量,期末数量 from 产品目录 a Left Join (" & FindStr & ") b on a.产品编码=b.产品编码 ORDER BY 分类 DESC"

全码
Select a.产品编码,期初数量,入库数量,出库数量,期末数量 from 产品目录 a Left Join (Select 产品编码,sum(a) as 期初数量,sum(c) as 入库数量,sum(e) as 出库数量,(sum(a)+sum(c)-sum(e)) as 期末数量 from (select 产品编码,期初数量 as a,0 as c,0 as e from 产品目录 Union Select 产品编码,入库数量-出库数量,0,0 from 产品出入库明细 Union Select 产品编码,0,入库数量,出库数量 from 产品出入库明细) t Group by 产品编码) b on a.产品编码=b.产品编码

[斯拓克]

引用 楼主 斯拓克 的回复:

Dim FindStr As String
FindStr = "select 产品编码,期初数量 as a,0 as c,0 as e from 产品目录 "
FindStr = FindStr & " Union Select 产品编码,入库数量-出库数量,0,0 from 产品出入库明细"
FindStr = FindStr & " Union Select 产品编码,0,入库数量,出库数量 from 产品出入库明细"
FindStr = "Select 产品编码,sum(a) as 期初数量,sum(c) as 入库数量,sum(e) as 出库数量,(sum(a)+sum(c)-sum(e)) as 期末数量 from (" & FindStr & ") t Group by 产品编码"
FindStr = "Select a.产品编码,产品名称,分类,期初数量,入库数量,出库数量,期末数量 from 产品目录 a Left Join (" & FindStr & ") b on a.产品编码=b.产品编码 ORDER BY 分类 DESC"
Call openRs(FindStr)

出现 [Err] 1052 - Column '期初数量' in field list is ambiguous

期初数量不明确怎么办？

[砸死牛顿的苹果]

最后那句
------
FindStr = "Select a.产品编码,产品名称,分类,期初数量,入库数量,出库数量,期末数量 from 产品目录 a Left Join (" & FindStr & ") b on a.产品编码=b.产品编码 ORDER BY 分类 DESC"
------
的 "期初数量" 存在于 a 和 b 表(别名)
需要去指定一下 a.期初数量 or b.期初数量