Create a table from two tables

[hellind]

If I have one table like this:
PRODUCT VOLUME
NK 200
CN 180
CY 220


and another table:
REGION SHARE
AUSTRALIA 0.45
CHINA 0.55



Want output:
PRODUCT REGION VOLUME
NK AUSTRALIA 90
NK CHINA 110
CN AUSTRALIA 81
CN CHINA 99
CY AUSTRALIA 99
CY CHINA 121


Volume for each product is propotioned between two countries 45% and 55% to generate the output table as shown.

How do I achieve this in MS Access SQL?

 

[Minty]

Don't make a table, just use a query that way if your shares change you won't need to try and remeber to recreate the table.

You need add a calculated field in the query designer to the other fields you require

Volume: [Product] * [Share]

and put both your tables in the query.

Warning : this will generate a very large set of results if you have many products and countries...