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