Total value of invoices created per job

[Guest]

I have jobs built on a Main form with several subforms on tab controls, the
parent-child key always being the Ref.
I have now added an invoice form (as another subform) and it is working OK
and seeing the Ref and I am saving into the underlying table without any
problems.
On some jobs we will raise more than one invoice.
I want to have a field somewhere (probably on the Main form itself) showing
the total value of the invoices created to date for that job (Ref).
I have tried to build a query to do this and then use a control with the
query as its record source (is this a good approach?)
Main trouble is, I cannot get the query to subtotal or group "by ref".
The fields I am using are in the Invoice table and are simply Ref and
InvTotalValue.
Can't get my head round what is no doubt a simple issue....advice, please!!
Many thanks as always
CW

 

[Arvin Meyer [MVP]]

As an example in the Northwind sample database, will demonstrate, add a
footer to your subform, and a textbox (which I'll name txtTotal) in the
footer. You can set both the height of the textbox, and the footer to 0".

In the textbox's controlsource property (in the property sheet), type:

= Sum(Forms!YourFormName!InvoiceTotalField)

Now place a textbox on your main form and set its controlsource to:

= Forms!FormName!NameOfSubformControl.Form!txtTotal

That's it!

 

[kingston via AccessMonster.com]

Are you using a totals query: group on Ref, sum Value? Are there any
invoices other than "invoices created to date"? If so, filter these out of
the query prior to the totals query.

 

[Guest]

Great - thanks Arvin. (I keep forgetting how useful Northwind can be....)
Many thanks
CW

 

[Guest]

Ah yes, it's that easy (when you know!).
Lovely stuff, many thanks
CW

 

[i_takeuti]

I have jobs built on a Main form with several subforms on tab controls, the
parent-child key always being the Ref.
I have now added an invoice form (as another subform) and it is working OK
and seeing the Ref and I am saving into the underlying table without any
problems.
On some jobs we will raise more than one invoice.
I want to have a field somewhere (probably on the Main form itself)
showing
the total value of the invoices created to date for that job (Ref).
I have tried to build a query to do this and then use a control with the
query as its record source (is this a good approach?)
Main trouble is, I cannot get the query to subtotal or group "by ref".
The fields I am using are in the Invoice table and are simply Ref and
InvTotalValue.
Can't get my head round what is no doubt a simple issue....advice,
please!!
Many thanks as always
CW