How do I create a table that contains figures calculated from other tables?

[Riz-Man]

Hi,

I have two tables:

Table A provides total sales volume of the UK shoe market from 2000-2007.

Table B provides the sales volume of different shoe manufacturers from 2000-07.

I want a third table created, called Table C. This should look exactly like Table B but instead of sales volume it shows percentage sales that are calculated by using the figures in Table A and B (i.e. [sales volume from Table B/total sales volume from Table A] * 100).

Could someone point me in the right direction please (assuming that such a table can be created, based on a calculation of figures in other existing tables).

Thank you.

 

[Ron_dK]

You should not create a table to store calculated values.

In stead you should create a query, grabbing data from the relevant fields in table A and/or B. In the query you can add fields which do the calcualtions.

Changing data in your A or B table fields will provide you with calculated
figures thru the query at any time.


Hth

 

[Riz-Man]

I see - what is the calculation (Access formula) I would perform in such an event...for say just one record (say Manufactuer X) in a given field (say the year 2007)?

Thanks.

 

[Ron_dK]

First make sure that there is a relationship between table A and B.
Then create a query which might look something like this :

SELECT TableA.YourFieldA, TableB.YourFieldB, [YourFieldA]*[YourFieldB] AS total FROM TableA LEFT JOIN TableB ON TableA.[Amount-ID] = TableB.[amount-id];




Hth