SQL/ACCESS函数未按预期工作

Question

Public Function GetPendingChangeOrders(strJ As String) As Double
strSQL = "SELECT DISTINCT Sum(jcdetail.cost) AS SumOfcost " & 
"FROM jcchangeorder INNER JOIN jcdetail ON (jcchangeorder.ordernum = jcdetail.ponum) AND (jcchangeorder.jobnum =jcdetail.jobnum) " & 
"GROUP BY jcdetail.jobnum, jcdetail.type, jcchangeorder.type, IIf(DLookUp(""type"",""jcchangeorderstep"",""jobnum = '"" & [jcchangeorder].[jobnum] & ""' and ordernum = '"" & [ordernum] & ""' and Type = 20"")=20,-1,0) " & _
"HAVING (((jcdetail.jobnum)='" & strJ & "') AND ((jcdetail.type)=19) AND ((jcchangeorder.type)<>2) AND ((IIf(DLookUp(""type"",""jcchangeorderstep"",""jobnum = '"" & [jcchangeorder].[jobnum] & ""' and ordernum = '"" & [ordernum] & ""' and Type = 20"")=20,-1,0))=0));"

Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly, dbReadOnly)
If Not rs.EOF Then
dblResult = Nz(rs.Fields(0), 0)
rs.Close
Set rs = Nothing
GetPendingChangeOrders = dblResult
Else
GetPendingChangeOrders = 0
End If
End Function

所以我被扔进了一些MS Access数据库，里面到处都是VBA/SQL语句。我确实是一个初学者，但我已经设法弄清楚了一些事情，并熟悉了我们用来打印工作报告的数据库。有些调用函数设置错误，从错误的表中提取，我基本上需要一些帮助来确定应该用哪种方式来解决这个问题。

目前，如果我们运行该报告，并且它调用">GetPendingChangeOrders"，它会执行它应该执行的操作，但当我们查看挂起的内容时。即使它在"JCCHANGEORDERSTEP"表中的状态为21(DENIED(，它也会显示结果。我包括了它的图像。

JCCHANGEORDER的列与JCCHANGOERDERSTEP的列(JOBNUM、ORDERNUM、TYPE(相同，但JCCHANGIORDER中的类型只有一个类型1，我认为它表示嘿，我是活动的。

JCCHANGEORDERSTEP包含1个已启动(待定(、20个(已批准(、21个(已拒绝(。它从报告的结果中过滤出20分，而不是21分。所以我只需要一些帮助，并解释为什么在混合物中添加21不起作用。

谢谢你抽出时间。

EDIT-1添加了IMGSIMGUR访问图片

Answer 1

在查看了您的图像并研究了现有的SQL代码后，我认为以下SQL查询可能更合适，也更可读：

select sum(d.cost) as sumofcost 
from 
(
jcchangeorder o inner join jcdetail d
on o.ordernum = d.ponum and o.jobnum = d.jobnum
) inner join
(
select distinct s.jobnum, s.ordernum 
from jcchangeorderstep s 
where s.type = 1
) q
on o.jobnum = q.jobnum and o.ordernum = q.ordernum
where
o.jobnum = ?job and d.type = 19 and o.type <> 2

这里，jcchangeorderstep.type = 1由表之间的inner join处理的jcdetail记录的包括，而不是每个记录的单独的dlookup。

您可以通过以下方式在功能中实现这一点：

Public Function GetPendingChangeOrders(strJ As String) As Double
Dim strS As String
strS = strS & "select sum(d.cost) "
strS = strS & "from "
strS = strS & "    ( "
strS = strS & "        jcchangeorder o inner join jcdetail d "
strS = strS & "        on o.ordernum = d.ponum and o.jobnum = d.jobnum "
strS = strS & "    ) inner join "
strS = strS & "    ( "
strS = strS & "        select distinct s.jobnum, s.ordernum "
strS = strS & "        from jcchangeorderstep s "
strS = strS & "        where s.type = 1 "
strS = strS & "    ) q "
strS = strS & "    on o.jobnum = q.jobnum and o.ordernum = q.ordernum "
strS = strS & "where "
strS = strS & "    o.jobnum = ?job and d.type = 19 and o.type <> 2 "

Dim rst As DAO.Recordset
With CurrentDb.CreateQueryDef("", strS)
.Parameters(0) = strJ
Set rst = .OpenRecordset
If Not rst.EOF Then
rst.MoveFirst
GetPendingChangeOrders = Nz(rst.Fields(0), 0)
End If
rst.Close
End With
End Function

---

编辑：

根据随后的评论，以下内容似乎更符合您的要求：

select sum(d.cost) 
from  
jcchangeorder o inner join jcdetail d 
on o.ordernum = d.ponum and o.jobnum = d.jobnum
where
o.jobnum = jobparam and
d.type = 19 and
o.type <> 2 and
not exists 
(
select 1 from jcchangeorderstep s 
where s.jobnum = o.jobnum and s.ordernum = o.ordernum and s.type <> 1
)

这可以通过以下方式在VBA函数中实现：

Public Function GetPendingChangeOrders(strJ As String) As Double
Dim strS As String
strS = strS & "select sum(d.cost) "
strS = strS & "from  "
strS = strS & "    jcchangeorder o inner join jcdetail d "
strS = strS & "    on o.ordernum = d.ponum and o.jobnum = d.jobnum "
strS = strS & "where "
strS = strS & "    o.jobnum = jobparam and "
strS = strS & "    d.type = 19 and "
strS = strS & "    o.type <> 2 and "
strS = strS & "    not exists "
strS = strS & "    ( "
strS = strS & "        select 1 from jcchangeorderstep s "
strS = strS & "        where s.jobnum = o.jobnum and s.ordernum = o.ordernum and s.type <> 1 "
strS = strS & "    ) "
Dim rst As DAO.Recordset
With CurrentDb.CreateQueryDef("", strS)
.Parameters("jobparam") = strJ
Set rst = .OpenRecordset
If Not rst.EOF Then
rst.MoveFirst
GetPendingChangeOrders = Nz(rst.Fields(0), 0)
End If
rst.Close
End With
End Function

Answer 2

HAVING和GROUP BY子句中的这部分查询会给您带来问题：

IIf(DLookUp("type",
"jcchangeorderstep",
"jobnum = ' [jcchangeorder].[jobnum] ' and 
ordernum = ' [ordernum] ' and
Type = 20")=20,-1,0))=0);

它错综复杂，很难阅读。但它说，"如果此作业和订单出现在JCCHANGEORDERSTEP中，类型为20，请将其排除在外。"因此，这就是您需要解决的问题。

整个查询可能应该通过多种方式进行修复。但我认为这个可能会让你到达你需要去的地方。

strSQL = "SELECT DISTINCT Sum(jcdetail.cost) AS SumOfcost " & _
"FROM jcchangeorder " & _
"INNER JOIN jcdetail " & _
"ON (jcchangeorder.ordernum = jcdetail.ponum) " & _
"AND (jcchangeorder.jobnum =jcdetail.jobnum) " & _
"GROUP BY jcdetail.jobnum, " & _
"jcdetail.type, " & _
"jcchangeorder.type, " & _
"DLookUp(""type"",""jcchangeorderstep"",""jobnum = '"" & [jcchangeorder].[jobnum] & ""' and ordernum = '"" & [ordernum] & ""' and Type = 1"") " & _
"HAVING (jcdetail.jobnum='" & strJ & "' AND " & _
"jcdetail.type=19 AND " & _
"jcchangeorder.type <> 2) AND  " & _
"DLookUp(""type"",""jcchangeorderstep"",""jobnum = '"" & [jcchangeorder].[jobnum] & ""' and ordernum = '"" & [ordernum] & ""' and Type = 1"")=1;"

我所做的更改为条件："如果此作业和订单出现在JCCHANGEORDERSTEP中，类型为1，请将其包含在内。"如果没有实际看到您的数据并亲自测试代码，我无法保证这会起作用。可能有一些打字错误，所以我已经解释了我要做什么，所以你可以纠正它们。

另外，请花一些时间浏览Stack Overflow教程。这个社区可以是一个很大的帮助，如果你与之合作。

试试这个：
在与OP讨论了所需的结果后，这似乎是一个更好的解决方案。它给出了所有变更单的总和，只有的变更单步长为PENDING。

strSQL = _
"SELECT SUM(JCD.cost) AS sumofcost " & _
"FROM jcchangeorder JCCO  " & _
"INNER JOIN jcdetail JCD  " & _
"ON JCCO.ordernum = jcd.ponum  " & _
"AND JCCO.jobnum = jcd.jobnum  " & _
"INNER JOIN (SELECT JCCOS.ponum,  " & _
"JCCOS.jobnum  " & _
"FROM jcchangeorderstep JCCOS  " & _
"GROUP BY JCCOS.ponum,  " & _
"JCCOS.jobnum  " & _
"HAVING Count(*) = 1  " & _
"AND First(JCCOS.type) = 1) JCSELECT  " & _
"ON JCCO.ordernum = JCSELECT.ponum  " & _
"AND JCCO.jobnum = JCSELECT.jobnum  " & _
"GROUP BY JCD.jobnum,  " & _
"JCD.type,  " & _
"JCCO.type "
"HAVING JCD.jobnum='" & strJ & "' AND " & _
"JCD.type=19 AND " & _
"JCCO.type <> 2;"

JCCO、JCCOS和JCD是SQL别名。SQL理解它们。JCSELECT是一个带别名的子查询。JCSELECT创建一组只有一个PENDING步骤的所有作业/订单。