One to Many Relat. Main/Sub Form calculations

[bkirsch]

I have created a database with a one to many relationship to track customer orders for billing purposes. Is there a way to create a sum if statement in the main form to sum total order $ for EACH customers unbilled orders in the subform?



Thanks for the help.

[pdebaets]

Sure. How do you indicate that an order is unbilled? What is the name of the dollar amount field that you want to sum?

An easy way is to put the sum in the subform header or footer section.

Another way is to use the DSum in a text box on your main form.

[bkirsch]

Billed field is a check box and the field I want to sum is "Inventory $$ Allocated"

Thank you so much!

[pdebaets]

in the subform footer create a text box control and in the control source property put

=sum(iif(Billed,0,[Inventory $$ Allocated]))

Now, if you want the field on your main form, just hide the subform footer section and put a text box control on your main form with control source

=MySubformControlName.Form!MySumFieldName

You'll have to change MySubformControlName and MySumFieldName to their respective names.

[bkirsch]

That equation calculates unbilled for all customers in the subform table...is there an equation that will calculate unbilled for each individual customer (one to many relationship) ??

[pdebaets]

Is you subform linked to the parent form record? What are the subform "link parent fields" and "link child fields" property values set to?

[bkirsch]

I have them linked through a one to many relationship based on a "Company" field...is this the information you are looking for?

[bkirsch]

Figured it out! Thanks for all the help today; I really aprpeciate it!