| Subject | Re: [IBO] Update summary in TableA based on records in TableB |
|---|---|
| Author | Robert Martin |
| Post date | 2014-07-31T03:49:30Z |
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 !
Cheers
Rob
On 31/07/2014 3:23 p.m., tblac@... [IBObjects] wrote:
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 !
Cheers
Rob
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?
Thanks
Terry
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2014.0.4716 / Virus Database: 3986/7951 - Release Date: 07/30/14