Multiplying values from two Access tables

I have two tables as below in Accecss 2007.

Town Table
----------
TownName开发者_C百科 | FlatCount | DetachedCount | SemiCount
A        | 5         | 3             | 4
B        | 2         | 6             | 3

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

I would like to get an output like below by multiplying the Count from the Town table with the corresponding PCost in the Cost table. FlatCost = Town.FlatCount * Cost.PCost for a flat.

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

I have tried to do this by using IIF, but not sure how to get PCost for each property type within the IIF clause.

Thanks

---

Looks like you are mixing data and meta data e.g. the data value Flat in table Cost becomes metadata value (column name) FlatCount in table Town. This is not a good idea and is probably why you are having difficulties writing what should be a simply query.

Restructure your Town table so that it has columns TownName, Prop and PCount. And remember that most bad SQL DML is caused by bad SQL DDL ;)

---

You could use a subquery to retrieve the cost of an item:

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

---

You have to cross join the tables. Then, for good values, put PCost in the multiplication, else, put 0.

You can then do a SUM using a Group by :

SELECT t.Town, 
       t.FlatCount, 
       SUM(t.FlatCount * IIF(c.Prop = 'Flat', c.PCost, 0)) AS FlatCost,
       t.DetachedCount,
       SUM(t.DetachedCount * IIF(c.Prop = 'Detached', c.PCost, 0)) AS DetachedCost,
FROM Town t, Cost c
GROUP BY t.Town, t.FlatCount, t.DetachedCount