Hi.
I'm no expert but have created a database and am stuck on something I'm sure is possible.

I have a 'final cost' field in my 'job' mainform and a subform with a field listing several 'extra costs' for each job. I want a field in my mainform that automatically populates with the total of all these costs.

The closest I got was a query including both of the fields and with simple a+b expression field to add them. The problem here was that the 'final cost' was repeated for each 'extra cost' and the results were like 'extra1+final' 'extra2+final' etc.

Not sure if I have explained this well. I have attached a screenshot of the form with the fields I'm talking about in bold red.

Any ideas? Am I being dense?

Cheers. Siobhan.

I have a 'final cost' field in my 'job' mainform and a subform with a field listing several 'extra costs' for each job. I want a field in my mainform that automatically populates with the total of all these costs.Shifty,

You can calulate controls with data from pretty much anywhere in the database. From your screenshot, it looks like the little subform is based on a child recordset of the main form. Is it??

If so, try putting an extra text box on the main form wherever you want it, and then setting the value of it with the MainForm's OnCurrent Event...Me.TextBoxName = DSum ("[child field name (ICP value??)]", "child table name",

"[master table's PK fieldname ("ref"??)] =

Forms!MainFormName![master table's PK field control name]") + Me.FinalValueThe closest I got was a query including both of the fields and with simple a+b expression field to add them. The problem here was that the 'final cost' was repeated for each 'extra cost' and the results were like 'extra1+final' 'extra2+final' etc.That's probably because that tables are joined in a relationship, and Access performed the calculation on a "record" basis. Any expressions written in queries will result in a new column being formed, like what you saw.

You might also want to check out Bob Larson's link to a good (but impossible to memorize) table matrix with various subform reference syntax lines.

Thanks aje, that does make sense to me but sadly I can't get it to work.
I think it's me being so clueless about code.
Doh!
Not sure what to do now...

I've attached a test version of my little database. This is the one on which I've been trying to work on this problem. It's pretty much the same. Could you show me on there?

Sorry if I'm being really stoopid.
S

Do you like the attached file?? I used a bit of drawing technique... :)

Also, the following part of the query is an incorrect reference. Use the "." to refer to fields in tables while in SQL....SELECT main.ref, main.[Site Number], main.[Site Address], main.Section,
main.Eng, main.Company, main.[Contract No], main.WBS, main.[Start Date],
main.[PO Number], main.[PO Value], main.[Final Rec'd], main.[Final Ret'd],
main.[Final Value], main.[TCSU9 rec'd], main.[SLD Rec'd], main.[Total Value],
main.[File closed], main.[File closed date], main.Comments,
([ipc (2)].[IPC Value]+[main].[PO Value]) AS Expr1I assume you were trying to do the same thing with these "!" references that the DSum does?? If so, you can probably delete them. You get the parameter popup anyway everytime you open the form...