MS访问-如果所有值都可用查询，则按组和求和

Question

我正试图在ms access中创建一个查询，该查询汇总成本，但希望从结果中排除那些没有价值的查询。我正在挣扎，因为我希望根据另一列从一列中排除项目。

我有一栏与成品，一栏与组件组成的成品，一栏与数量的组件所需的成品和栏与成本的每一个组件。我需要的是得到每个组件所需的总成本，然后将成本归为成品的总成本，这很简单。

然而，有一些空白字段，其中一个或多个组件的成本是不可用的，我希望那些显示在结果与文本“挂起”的成品，而不是它总结只有可用的值。

下面是我想要做的事情的例子：

列表

我需要的结果如下：

结果

如能对此提供帮助，将不胜感激:)

Answer 1

详细阐述6月7日的评论，并假设Table2：

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|         ID         |       FinishedProductid        |     ItemNumber     |     Component      |        Uom         |      ComponentQuantity       |      ComponentUnit       |      stdCost       |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                  1 |                      199127402 |                 10 |          123123123 | PC                 |                            3 | PC                       |              $1.50 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                  2 |                      199127402 |                 20 |          321321321 | PC                 |                            1 | PC                       |              $2.50 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                  3 |                      199127402 |                 30 |          123456789 | PC                 |                            1 | PC                       |              $3.55 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                  4 |                      199127402 |                 40 |          987654321 | PC                 |                         0.25 | H                        |             $82.00 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                  5 |                      199127403 |                 10 |          111222333 | PC                 |                            3 | PC                       |              $1.50 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                  6 |                      199127403 |                 20 |          333222111 | PC                 |                            1 | PC                       |                    |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                  7 |                      199127403 |                 30 |          444555666 | PC                 |                            1 | PC                       |              $3.55 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                  8 |                      199127403 |                 40 |          666555444 | PC                 |                         0.25 | H                        |             $82.00 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

​

​

'resulting sql to get all FinishedProductids


SELECT Table2.FinishedProductid
FROM Table2
GROUP BY Table2.FinishedProductid;

​

​

'once have FinishedProductid's out of the group statement you can just use a calculated variable

SELECT Query2.FinishedProductid, getTotalCost([FinishedProductid]) AS TotalCost
FROM Query2;

'of course a getTotalCost function is needed.  Add the following to a code module

Public Function getTotalCost(FinishedProductid As Long) As String
If isPending(FinishedProductid) Then
getTotalCost = "Pending"
Else
getTotalCost = DSum("ComponentQuantity*stdCost", "Table2", "FinishedProductid = " & FinishedProductid)
End If
End Function


Public Function isPending(FinishedProductid) As Boolean
' if any values of stdCost are null set isPending to true
'public functions can be accessed most anywhere in Access

Dim nullCount As Long
nullCount = DCount("ID", "Table2", "FinishedProductid = " & FinishedProductid & " AND ISNULL(stdCost)")
If nullCount > 0 Then
isPending = True
Else
isPending = False
End If
End Function

'result

-------------------------------------------------------
|       FinishedProductid        |     TotalCost      |
-------------------------------------------------------
|                      199127402 | 31.05              |
-------------------------------------------------------
|                      199127403 | Pending            |
-------------------------------------------------------

说明:我使用了两种查询方法来避免来自组的sql复杂性。这些功能相对来说是解密的，因此可以自我评论。最后，函数可以重用。

编辑: getTotalCost返回一个字符串，以满足返回字符串“待定”和总成本的要求。