Help Access gurus!

[quinny1]

Hi everyone
I have a main form , “House ” containing a subform ,”works”.The main form has a one to many relationship with the sub form.
How can I display a total on a mainform which is the sum of a field ,”estimate”, on the subform which corresponds to the id field on on the main form.I.E. The result of “SELECT DISTINCTROW Sum(Works.Estimate) AS SumOfEstimate FROM Works WHERE HOUSE.HOUSEID = WORKS.HOUSEID”
I tried an unbound textbox on the house form and set the controlsource to the above query but it doesn’t work. Sameal suggested using unbound forms but is this the only way?
Thanks in anticipation.

quinny

 

[raskew]

(1) Place a text box (txtEstimateTot) in the footer of your subform with the Control Source set to =Sum([Estimate]).
(2) To display the sum place a text box on your main form with the Control Source set to =[Works].[Form]![txtEstimateTot]

You can see an example of this in Northwind's Orders and Orders SubForm forms.

 

[quinny1]

Raskew

Thank you very much.Exactly what I wanted!



Quinny