sql - 将两个Access表中的值相乘

我在Accecss 2007中有两个表格，如下所示。

Town Table
----------
TownName | FlatCount | DetachedCount | SemiCount
A        | 5         | 3             | 4
B        | 2         | 6             | 3

Cost Table
----------
Prop     | PCost
Flat     | 10
Detached | 20
Semi     | 30

我想通过将Town表中的Count乘以Cost表中的相应PCost来获得如下输出。 FlatCost = Town.FlatCount * Cost.PCost为单位。

Results
-------
Town | FlatCount | FlatCost | DetachedCount | DetachedCost | .....
A    | 5         | 50       | 3             | 60           |
B    | 2         | 20       | 6             | 120          |

我试图通过使用IIF来做到这一点，但不确定如何在IIF子句中为每种属性类型获取PCost。

谢谢

最佳答案

您可以使用子查询来检索物料成本:

select  TownName
,       FlatCount
,       FlatCount * (select PCost from Cost where Prop = 'Flat') as FlatCost
,       DetachedCount
,       DetachedCount * (select PCost from Cost where Prop = 'Detached')
            as DetachedCost
,       ...
from    Town

关于sql - 将两个Access表中的值相乘，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/6517058/

town

sql - 将两个Access表中的值相乘