Subject Re: [IBO] Update summary in TableA based on records in TableB
Author Robert Martin
Hi Terry

You should not sum them and store them in TableA, this breaks a number of the normalisation 'rules' for database design.  You are much better calculating it as desired.  There are plenty of ways of doing this, such as the below...

SELECT a.fieldA, a.fieldB, (SELECT SUM(b.Hours) FROM TableB b WHERE b.StaffID = a.StaffID) AS TotalHours
FROM TableA a

I don't know your tables or fields but hopefully the above will give you the idea !


 On 31/07/2014 3:23 p.m., tblac@... [IBObjects] wrote:

Hi, the problem is that in TableB I have 'Tom Jones' who has 3 records. He has a field called WorkingHours. In the 1st record he works 3 hours, the second record 2 hours and the 3rd record 4 hours. So he works a total of 9 hours. I want to put this in a field in TableA called TotalHours.

My question is do I have to use a loop on TableB,sum the hours and put the total into TableA or is there a better way to do this?



No virus found in this message.
Checked by AVG -
Version: 2014.0.4716 / Virus Database: 3986/7951 - Release Date: 07/30/14