2. ASPRunnerPro General questions
  3. Master/Detail - Adding Subtotals
This topic is locked

Master/Detail - Adding Subtotals

 		5/27/2009 3:59:07 PM
ASPRunnerPro General questions
B
 Barse author

Is it possible to add totals from detail records and sum them into a total into the master record?
For example, in an order/order items relationship, you would add up the total dollar amount of each line item and total it in the master record as the order total.
I tried getting it done through the SQL query but wasn't able to get it done.
Thanks in advance.
J
 		Jane 5/28/2009

Hi,
you can calculate and update total value for each record in the master table in the After record added/updated events on the Eventstab for detail table.

Here is a sample:

str = "select sum(FieldName) from DetailTableName where FieldName=" & values("KeyField")

Set rstmp = server.CreateObject("ADODB.Recordset")

rstmp.open str,dbConnection
'update master table

dbConnection.Execute "update MasterTableName set TotaField=" & rstmp(0) & " where MasterKey=" & values("KeyField")
rstmp.close

set rstmp=nothing
B
 		Barse author 5/28/2009

Thanks for the reply. What should I be using for what you have in KeyField below? Should I be using Keyfield or am I using the key column name used to set up the relationship?
So for example, the fields I want to sum in Detail (table called Components) are called SubTotal, the field I want to sum into in Master (table called BeadedInventoryItems) is called GrandTotal. The key column is called ItemNumber.
So would the code be:
str = "select sum(SubTotal) from Components where SubTotal=" & values("ItemNumber")

Set rstmp = server.CreateObject("ADODB.Recordset")

rstmp.open str,dbConnection
'update master table

dbConnection.Execute "update BeadedInventoryItems set GrandTotal=" & rstmp(0) & " where MasterKey=" & values("ItemNumber")
rstmp.close

set rstmp=nothing
I get an error when I use this. Thanks again.

BTW, my site is here if you want a live look:

www.barse.com/proj

Hi,

you can calculate and update total value for each record in the master table in the After record added/updated events on the Eventstab for detail table.

Here is a sample:
J
 		Jane 5/29/2009

Please publish your project on Demo Account and open a ticket at http://support.xlinesoft.com sending a URL to your pages along with instructions on reproducing this error.
M
 		Matthew 7/19/2009

I am having the same issue and would like to do something very similar. Did you find a solution to this? Thanks
J
 		Jane 7/20/2009

Matthew,
It's difficult to tell you what's happening without seeing actual files.
Please publish your project on Demo Account and open a ticket at http://support.xlinesoft.com sending a URL to your pages along with instructions on reproducing this error.