Calculated Subtotal from embedded Table on Main Form

[DocBlack4444]

Hello,

Need some advice. I have a VERY simple database set up to help a friend of mine to keep track of very simple plumbing business. he has been using for a while, but asked if i could figure out how to subtotal what customers still owe him so he does not have to do the math.

I made three tables, one for accounts, one for Payments and the other for purchases. one main form that has both payments and purchases embedded as tables as well as lists the account information.

I tried to figure out how to subtotal the payments table on the main form but all i get is ?name, i know that means that it doesn't know what part i am talking about, but i know that the embedded table is Child58.....so i am simply confused. any help would be greatly appreciated.

 

[theDBguy]

Hi. Perhaps you could try using DSum().

 

[DocBlack4444]

Hi. Perhaps you could try using DSum().

DSum() sounds great......

would you be willing to educate me on how it works? and how the formula would flow? i have very basic knowledge of this, i clicked the embedded subform in the design menu to figure out how the two tables would go on the main form.

 

[theDBguy]

DSum() sounds great......

would you be willing to educate me on how it works? and how the formula would flow? i have very basic knowledge of this, i clicked the embedded subform in the design menu to figure out how the two tables would go on the main form.

Sure. Try using this:

=DSum("[Payment Amount]","Payments","[Account ID]=" & Nz([Account ID],0))

Hope that helps...

 

[DocBlack4444]

Sure. Try using this:

=DSum("[Payment Amount]","Payments","[Account ID]=" & Nz([Account ID],0))

Hope that helps...

Perfect, Thank you. Looking at the formula, I think I understand how it works, all except for the Nz and the [/code] part.

 

[jdraw]

Here's a link to definitions and examples of functions in Access.
Good luck.

 

[theDBguy]

Perfect, Thank you. Looking at the formula, I think I understand how it works, all except for the Nz and the [/code] part.

Hi. Nz() was just me being safe, in case you move the record pointer to a new record, so you don't get an #Error. The [/code] part, you shouldn't see at all. That was just for the forum when displaying code snippets.