perform calculations on database lists

[LisaM]

Iam using Margaret's new db class module and it is fantastic I do have a query though I would like to calculate a field from my database file.
e.g each day i enter how many calories burnt during exercise.
I would like to have a label that display's the total no. of calories burnt.
How would i go about coding this?
should i use the

db.getfield("name")

function or the

db.PullList(dbFields() As String, Filter As String)

function

So something like:

Sub CalorieTotals
      Dim Cal As List
      Dim Value As String
      Cal = db.PullList(Array As String("Calories"))
      For each Value + next value in list

I hope this makes sense It's late and my eyes hurt haha
I've been searching for 8hrs now and i'm not quite finding an answer to my question. (I probably have found the answer I just don't know it being a noob lol)

 

[margret]

OK, need a little more info.

Are you the only one in the list?

Are you just wanting all values added together?

Do you want a date range? If so, did you store a date to each record?

Do you just want a total based on your name?

Let us know. The last option looks like what you would need to do.

You could also do something like:

Sub AddCal
   Dim CalTot As Long
   db.FirstRecord
   For l = 0 To db.Record_Count -1
      CalTot = CalTot + db.GetField("Calories")
      db.NextRecord
   Next
End Sub

 

[LisaM]

Thanks that code looks like it is what i am looking for. I will try it out today

Sorry for my vague post.
Basically this is my database structure

Sub SelectdbW               'Structure for database #2 created in workarea 1
   db.CreateDataBase(1, "workout.dat", File.DirInternal, Array As String("date", "workout", "phase", "duration", "calories", "maxhr", "avghr", "completed", "skipped", "intensity"))
End Sub

for each value added to field "Calories" i would like to display the total calories in a label.
so say i have entered 3 days worth (300, 350, 400) Then my label should read 1050
I also want to do averages so what is the average value of the data added in the MaxHR. So i want to retrieve all data from the MaxHr field which may look like (180, 177, 190) my code should calulate the average (180 + 177 + 190) / 3) 3 being the list size. and my label should show 182.

There are a few values i want to pull from my databases like (Highest value in the list, finding all values that equal a certain value etc. I'm just not very good with lists i figured if i got help with one of my calculations i could figure out the rest from there

Thanks again for your time and help.

 

[LisaM]

It works well thanks
I added a line at the end to set the label . Now i just need to figure out the other calculations!! Thanks for pointing me in the right direction

Sub AddCal
    Dim CalTot As Long
    db.FirstRecord
    For l = 0 To db.Record_Count -1
        CalTot = CalTot + db.GetField("Calories")
        db.NextRecord
    Next
   Label14.Text = CalTot
End Sub

For anyone else looking for these calculations I'll post the subs as i get them working.

'To find the average and display it as a label.
Sub AvgMaxHR
    Dim AvgMax As Long
   Dim AC As String
    db.FirstRecord
    For l = 0 To db.Record_Count -1
        AvgMax = AvgMax + db.GetField("maxhr")
        db.NextRecord
      AC = AvgMax / db.Record_Count
    Next
   Label19.Text = AC
End Sub

'To find the highest Value in a list
Sub HMaxHR
    Dim HMax As List
   HMax.Initialize
    db.FirstRecord
    For l = 0 To db.Record_Count -1
      HMax.Add(db.GetField("maxhr"))
      db.NextRecord
   Next
   HMax.Sort(True)
   Label21.Text = HMax.Get(1)
End Sub

 

[margret]

Your average is easy:

Sub MyAverge
    Dim MyAverage As Long
    db.FirstRecord
    For l = 0 To db.Record_Count -1
        MyAverage = MyAverage + db.GetField("maxhr")
        db.NextRecord
    Next
   Label15.Text = MyAverage / Record_Count
End Sub