Is there a way to sum data in DataTables?

[Arg81]

Before I start, I have mentioned this before, but I didn't really get much help, no offense to those who did reply, but it wasn't what I was wanting to achieve.

I have a decimal field in one table that is used to represent the time spent on a request in hours, i.e. 0.25, 0.5, 0.75, 1, 1.25 etc etc.

I am trying to do a sum. Basically the relationship is linked on two fields to the master table.
The first sum needs to be the sum of all the time spend on Field1. The second sum needs to be the sum of the time spent only on Field2 for each Field1.
I.E.

Request No | Revision No | Time Spent | EmployeeID

1 -------------- 1 ---------- 0.5 --------- 16
1 -------------- 1 ---------- 0.75 -------- 5
1 -------------- 2 ---------- 0.25 -------- 12
1 -------------- 2 ---------- 0.5 --------- 2

So basically, the sum of the Time Spent on Request No 1 would be 2 hours, the sum for Request 1 Revision 1 would be 1.25 hours, and Request 1 Revision 2 would be 0.75 hours.


I can get this working by creating SQL and linking into the database, but that then relies on the user having to submit everytime they add a row to the Time table. I would like this to be real time if possible, so in a sense summing the data straight out of the DataTables.

Thanks for any help.
Luke

 

[Arg81]

They just contain numbers. The two labels are bound at form_load;

lblDWRNo.DataBindings.Add("Text", dsSearchDWR1, "DWR.DWRNumber")
lblRevNo.DataBindings.Add("Text", dsSearchDWR1, "DWR.DWR_Revision.RevisionNumber")

As soon as the parameter query is run, then they obviously bind to the loaded data.

I've just put the "If" statement in and the form nows loads OK, but the time label is blank!!

 

[Arg81]

I lie....it's suddenly worked!!

Cheers dude, how would I then do the time spent on a whole request, would it be the same but minus the (CInt(lblRevisionNumber.Text) ?

Luke

 

[jmcilhinney]

Replace:

DirectCast(DirectCast(requestTimes(CInt(lblRequestNo.Text)), ArrayList)(1), SortedList)(CInt(lblRevisionNumber.Text)).ToString()

with:

DirectCast(requestTimes(CInt(lblRequestNo.Text)), ArrayList)(0).ToString()

Remember, each item in the SortedList contains a key (request no.) and an ArrayList. The ArrayList has two items: the total time for the request and a SortedList. This SortedList has items that contain a key (revision no.) and the total time for the revision. I don't know if this will sink in, but the first line above gets the ArrayList that corresponds to a request number, then takes the second item, which is a SortedList, and from that gets the time associated with a revision number. The second line gets the ArrayList that corresponds to a request number, then takes the first item, which is the time associated with that request.

 

[Arg81]

..Think I've got it

When I said it didn't work, then it did...well I've figured the first time a value is selected and then loaded (I.e. on my parameter query I am searching by EmployeeName), the label is blank. but if I then load another employeeName the label has the value in.

I'm assuming I'll have to run CalculateTime after each insert of a row in the revisions grid?

Cheers,
luke

 

[Arg81]

Oh my, this was nearly a year ago!!!

However, I've just come across a problem when deleting from the grid.
EXAMPLE:
2 rows in the time datagrid. Remove 1, CalculateTime works and adjusts the 2 values on the labels.
When removing the last row in the grid, it removes OK, the labels go back to displaying 0, but then the error;

"Index was out of range.
Must be non-negative & less than the size of the collection.
Parameter: Index"

This only appears when removing the last row from the time grid. It never shows when deleting a row as long as a row still exists in the grid.

The strange thing is, the error never appears when the form is loaded, and 9/10 times there is no row in the grid anyway (I.E. when opening the form after someone has added a new record to the main table, or after adding a new revision).

I can see that the problem is with the CalculateTime, and think that it's to do with this part;

[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] revisionTimes.ContainsKey(row("RevisionNumber")) [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'add the time spent to the running total for the revision
[/COLOR][/SIZE][SIZE=2]revisionTimes(row("RevisionNumber")) = [/SIZE][SIZE=2][COLOR=#0000ff]CDec[/COLOR][/SIZE][SIZE=2](revisionTimes(row("RevisionNumber"))) + [/SIZE][SIZE=2][COLOR=#0000ff]CDec[/COLOR][/SIZE][SIZE=2](row("TimeSpent"))
[/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'add a new entry for this revision
[/COLOR][/SIZE][SIZE=2]revisionTimes.Add(row("RevisionNumber"), [/SIZE][SIZE=2][COLOR=#0000ff]CDec[/COLOR][/SIZE][SIZE=2](row("TimeSpent")))
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE]

^^ It adds a new entry for the revision if one doesn't exist, but there is nothing in there to say what happens if one doesnt....
Hopefully someone can help on this, after a year of development / internal politics / holidays / other projects (the usual) , this has recently gone live and this so far is the only error in the whole application!!!

Kind Regards,
Luke