Struggling With Calculating Recordset Fields !!!

[DMT Dave]

Hi Guy's I am struggling to work out how to add calculated fields to recordset results!



Is this better setup in the query statement or setup within the loop, I think i know where i am going wrong, the DMin, DAvg and DMax require a table source instsead of rs.field perhaps ???

I can't for the life of me set this up when i should be able to, should be simple, just can't see it!!!!

Trying to add Min, Avg, Max And a division to recordset email:

Note: full event not added, all are Dimmed per name

    strSQL = "SELECT tblData.TestDATE, tblData.TestTIME, tblData.TestREADING, tblData.TestDOSAGE, tblData.Ratio, tblData.Carbs, tblData.DosageCalc, tblData.TimeOrder " _            & "From tblData " _
            & "ORDER BY tblData.TimeOrder DESC;"




    Set rs = CurrentDb.OpenRecordset(strSQL)


    Do While Not rs.EOF
    
    dblMin = DMin(rs.Fields("Ratio"), , "[Ratio] > " & intMin)
    dblMax = DMax(rs.Fields("Ratio"), , "[Ratio] < " & intMax)
    dblAvg = DAvg(rs.Fields("Ratio"), , "[Ratio] > " & intMin & " And [Ratio] < " & intMax)
    dblAvgCarbs = DAvg(rs.Fields("Carbs"))
    dblRatio dblAvgCarbs / dblAvg
    
    
    strBody = strBody & "<tr>" & _
        "<td style='background-color:#F5F5F5'>" & strFS & Format(rs.Fields("TestDATE"), "dddd-dd-mmm-yyyy") & strFE & "</td>" & _
        "<td style='background-color:#F8F8FF'>" & strFS & rs.Fields("TestTIME") & strFE & "</td>" & _
        "<td style='background-color:#F5F5F5'>" & strFS & rs.Fields("TestREADING") & strFE & "</td>" & _
        "<td style='background-color:#F8F8FF'>" & strFS & rs.Fields("TestDOASGE") & strFE & "</td>" & _
        "<td style='background-color:#F5F5F5'>" & strFS & Left(dblMin, 4) & strFE & "</td>" & _
        "<td style='background-color:#F8F8FF'>" & strFS & Left(dblMax, 4) & strFE & "</td>" & _
        "<td style='background-color:#F5F5F5'>" & strFS & Left(dblAvg, 4) & strFE & "</td>" & _
        "<td style='background-color:#F8F8FF'>" & strFS & Left(dblAvgCarbs, 4) & strFE & "</td>" & _
        "<td style='background-color:#F5F5F5'>" & strFS & Left(dblRatio, 4) & strFE & "</tr>" & "|"
'
    
   rs.MoveNext
   Loop
    
   Debug.Print rsbody

[DMT Dave]

I Might have this guy's, will come back and update soon

[Welshgasman]

Why aren't you using the table?

I have never seen anyone try this.?

The docs say you need a domain. I would not say a recordset was a domain.?

[DMT Dave]

No, i am struggling to do this what should be simple!!!!

trying calc fields in query so i can write it to SQL, getting data type mismatch error

all number fields are set to Number in Double format



Just tried adding [ ] in the first couple of criterias as i didn't see that error but still data type mistach

Am i doing this calculated fields correct (obviously not) lol

[CJ_London]

Am i doing this calculated fields correct (obviously not) lol

No - to get a record to return, it would need to have a ratio which was equal to the minimum , maximum and and average ratio - plus your carbs and insulin criteria as well. So might work if your tblData contained just one record or multiple records with exactly the same values.

It looks to me like your whole approach is wrong. Suggest explain what you are trying to do, provide some example data and the result required from that example data and what you want to do with it subsequently (display in a form or report, edit the raw data, whatever).

[DMT Dave]

Ahh thanks guy's, you are suggesting add fields called MinRatio, AvgRatio etc etc, then run an update query with the calculations ?

so there would be no criteria's in the select query then add to SQL ?

[CJ_London]

you are suggesting add fields called MinRatio, AvgRatio etc etc, then run an update query with the calculations ?

I'm not suggesting anything since it is not clear what you are trying to do

[Welshgasman]

I just use the Avg(), Sum() etc in queries, daily, weekly and monthly.

[CJ_London]

Possibly. Without knowing what you trying to do, I'm not prepared to guess. Try it and see if it meets your requirements. You probably need two queries one an aggregate to get your min/max etc and another based on that for your calculations. Just be aware every time you add, delete or modify a record then min/max etc is likely to change, so storing the values seems a futile exercise since you will need to recalculate every time.

[Welshgasman]

Dave,
They would not be criteria but actual fields in the query.

[DMT Dave]

Hi CJ London, and WGM, yes data will change as this is to do with fluctuating blood sugar levels, I have tried setup a query (post 4) to have this query as an SQL statement in my recordset, this calculation will differ when a new enter is added into blood sugar test reading

I think i have the syntax wrong in post 4 ?

I just can't remember how to do calculations in a self made query field

Thanks for your help

[DMT Dave]

I can then set the recordset to these self made fields MinRatio, AvgRatio, MaxRatio, AvgCarbs and Insulin Req

The ratio based on carbs eaten as well as readings will keep changing the ratios, i do have the calculations on text boxes on a form, but i don't think i can integrate text boxes into an SQL recordset.

These are the formulas i am trying to add to query field (post4)

dblMin = DMin(rs.Fields("Ratio"), , "[Ratio] > " & intMin)
dblMax = DMax(rs.Fields("Ratio"), , "[Ratio] < " & intMax)
dblAvg = DAvg(rs.Fields("Ratio"), , "[Ratio] > " & intMin & " And [Ratio] < " & intMax)
dblAvgCarbs = DAvg(rs.Fields("Carbs"))
dblRatio dblAvgCarbs / dblAvg

intMin is 1
intMax is 20

so i know these would need to be changed in the query calc

[kd2017]

I mean no offense but... do the results of these queries have the potential to harm patients???? If so then I think (again I mean no offense) you need to hire a professional for this application... There are a lot of mistakes and fundamental misunderstanding here that need to be addressed.

[Welshgasman]

It is for his use only @kd2017

[Welshgasman]

So use the table fields?
You cannot just make stuff up.