> Hi,
> We have a master-detail table which has 3 value fields and a total
> which is
> calculated by the sum of these 3 fields. I'll just call them value1,
> value2
> and value3 and total.
>
> Then we have another value field in the detail table, so basically the
> structure is:
>
>
> Master Table:
> Value1
> Value2
> Value3
> Total (calculated by value1 + value2 + value3
>
> Detail Table:
> Value
>
> The problem is that the Total must always match the sum of the values
> in the
> Detail table.
>
> We tought about creating a trigger to update the Master Table, but as
> the
> Total is calculated we can't just update that and also we can't just
> update
> one field with the sum of the values from the detail table.
>
> Another possibility was to create triggers to check if the values
> match, but
> that also didn't work because when a new value is inserted in the
> Detail
> table the trigger is run and there is still more values to be added, so
> basically we are out of ideas.
>
> Is there any way to assure that the values will always match in both
> tables?
>

how many detail records are there? Do you have a foreign key?
Have you heard about sub-queries?
SELECT FIELD1, FIELD2,
(SELECT SUM(FIELDFROMDETAIL) FROM DETAILTABLE WHERE
DETAILTABLE.FK=MASTER.ID) AS SUMofdetail
FROM MASTERTABLE

Alan