summing a column in a child datatable

[jdink22]

i have a datagrid that displays two levels of information based on a relationship. The first level is simply dates, which link to the second level which is tasks associtated with that date. I want to also include a "total hours" column in the first level/parent datatable. What would be the best way to cycle through this hierarchy and get the total hours for each row related to each unique date? (please see attached file for a snapshot of the grid). So for the example I have attached, the "Total Hours" column in the first level will read "4".

 

i was thinking something along these lines:

 

Dim drow As DataRow

Dim trow As DataRow

Dim totalHours As Integer

For Each drow In das1.Tables("temp_Dates_table").Rows

For Each trow In das1.Tables("job_item_info").Rows

****totalHours = totalHours + das1.Tables("job_item_info").Columns("quantity") **** (this line is the concept only)

Next

das1.Tables("temp_Dates").Columns("hourstotal"). = totalHours

Next

 

[edit]Please use more sensibly sized attachments in future[/edit]

Edited June 25, 2003 by Squirm

[JABE]

Instead of iterating over DataRows, try using DataTable.Compute. Its second parameter accepts a filter criteria, a convenience for your problem :)

[jdink22]

I looked at using the .compute function, but I wasn't quite sure how to apply it...Don't I still need to iterate through each row of the first table (dates) in order to get the total for the related rows for each individual date? :confused:

[JABE]

Yup, I guess you still have to iterate thru the parent table, then you call Compute, passing in the ID of the parent table in its child table as the second parameter. Look up how to use compute in http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatatableclasscomputetopic.asp.

 

By the way, if the values in the child table are static, you can also do the SUM in your SQL.

[jdink22]

Finally got this one to work - thanks!! :cool:

 

*****RESOLVED*****