Sub-total from an other table?

[saturnius]

Hello,
I have the following two tables with a column "sum" that should contain the sub-total as described. Thaks in advance!

table1: overview
column1verviewID
column2:name
column3:sum -> (of all rows of table2.overviewID - this field shoul contain the sum of all single scores for the particular overview ID and should change automatical when new details are added)

table2: details
column1:detailID
column2verviewID
column3:score (from 1-10)

 

[r937]

select overview.overviewID
     , overview.name
     , sum(details.score)
  from overview
inner 
  join details
    on overview.overviewID = details.overviewID
group
    by overview.overviewID

do not save the sum in the overview table unless you have a good reason

rudy

http://rudy.ca/

 

[saturnius]

Hello,
Thank you for your answer.
It is easier to have the value of the sum of all detail scores in the overview table then to calculate it always new. But I will change the sum when I add a new item to the details.
Thank you!

 

[JeremyNYC]

Saturnius,

It may seem easier to store that calculated field now, but the first time you find you've got bad data in there you'll wish you'd not done it. One of the biggest principles of database design is that you don't store calculated values unless you absolutely need them.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing and building Access databases since 1995.