Struggling With Calculating Recordset Fields !!!

**Gicu** · 06-19-2023, 09:19 AM

Maybe something like this:

Code:

strSQL = "SELECT tblData.TestDATE, tblData.TestTIME, tblData.TestREADING, tblData.TestDOSAGE, tblData.Ratio, tblData.Carbs,"_ "tblData.DosageCalc, tblData.TimeOrder,MinRatio: DMin("Ratio","tblData","[Ratio] > 1"),MaxRatio:DMax("Ratio","tblData","[Ratio] < 20"),"_ "AvgRatio: DAvg("Ratio","tblData","[Ratio] > 1 And [Ratio] < 20"),AvgCarbs:DAvg("Carbs","tblData"),Carb_Ratio:[AvgCarbs]\[AvgRatio] From tblData " _
& "ORDER BY tblData.TimeOrder DESC;"

Cheers,

**kd2017** · 06-19-2023, 09:33 AM

Originally Posted by Welshgasman

It is for his use only @kd2017

Okay great, thank you for the info.

My question is why are the aggregated fields part of the main query? As far as I can tell from what's been presented so far the conditions of the aggregated fields aren't dependent on the values in the row, so just run a second query?

**CJ_London** · 06-19-2023, 09:47 AM

I refer back to post#5. If you want answers, answer our questions so we don't have to speculate the requirement.

....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** · 06-19-2023, 10:46 AM

Hi Sorry CJ London,

I am trying to build an SQL string that would calculate fields once a text box (Number field) is updated

So once this update is done, it looks at Avg readings based on min and max numbers, but max number is 20, min number is 1 then carbs and readings can calculate insulin dose required, this changes based on food and reading, i think Vlad may have hit the result, i will pull Vlads suggestion into a new query and see where i am going wrong

I just couldn't remember where and which database i have calculated fields in a query wether it be a stored data/number field or I have named a field such as MinRatio: Ratio, AvgRatio: Ratio, ration being the field

sorry if i have mis explained as i always appreciate your help

**DMT Dave** · 06-19-2023, 10:48 AM

Thanks Vlad, kd2017 for your suggestions also

Will come back

**DMT Dave** · 06-19-2023, 12:09 PM

Hi Vlad, this works well in design /run query, do you always have to double quote to read in SQL ?

I have done this in the past where there needs to be double quotes around field and table names ?

Code:

strSQL = "SELECT tblData.TestDATE, tblData.TestTIME, tblData.TestREADING, tblData.TestDOSAGE, DMin(""Ratio"",""tblData"",""[Ratio] > 1"") AS MinRatio, " _ 
& "DMax(""Ratio"",""tblData"",""[Ratio] < 20"") AS MaxRatio, DAvg(""Ratio"",""tblData"",""[Ratio]"") AS AvgRatio, " _
& "DAvg(""Carbs"",""tblData"",""[Ratio]"") AS AvgCarbs, [AvgCarbs]\[AvgRatio] AS [Carb_Ratio] " _
& "FROM tblData;"

**DMT Dave** · 06-19-2023, 12:29 PM

Ahhh so close, Item not found in this collection, this is i believe just a field name, i can't see wood for tree's !!!

Code:

strBoxStart = "<script><table width='auto';style='text-align:left;border:1px solid black;font-family:calibri;border-collapse:collapse;padding:10px'><tr style='background:white;mso-highlight:blue' ctx.shadowblur;20; ctx.shadowcolor;blue></script>"        strBoxEnd = "</tr></table>"


        strHTML = "<HTML><Body><table border='3' width=auto'><font color='blue' size='3' face='Times New Roman'><tr><th>DATE</th><th>TIME</th><th>READING</th>" & _
        "<th>DOSAGE</th><th>MIN RATIO</th><th>MAX RATIO</th><th>AVG RATIO</th><th>AVG CARBS</th><th>RATIO</th></tr>"
        
        strBody = strHTML
                    
    strSQL = "SELECT tblData.TestDATE, tblData.TestTIME, tblData.TestREADING, tblData.TestDOSAGE, DMin(""Ratio"",""tblData"",""[Ratio] > 1"") AS MinRatio, " _
        & "DMax(""Ratio"",""tblData"",""[Ratio] < 20"") AS MaxRatio, DAvg(""Ratio"",""tblData"",""[Ratio]"") AS AvgRatio, " _
        & "DAvg(""Carbs"",""tblData"",""[Ratio]"") AS AvgCarbs, [AvgCarbs]\[AvgRatio] AS [Carb_Ratio] " _
        & "FROM tblData;"


    strFS = "<font size='3' face='Arial' style=text-align=center; vertical-align=middle>"
    strFE = "</font>"
    
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    Do While Not rs.EOF


    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(rs.Fields("MinRatio"), 4) & strFE & "</td>" & _
        "<td style='background-color:#F8F8FF'>" & strFS & Left(rs.Fields("MaxRatio"), 4) & strFE & "</td>" & _
        "<td style='background-color:#F5F5F5'>" & strFS & Left(rs.Fields("AvgRatio"), 4) & strFE & "</td>" & _
        "<td style='background-color:#F8F8FF'>" & strFS & Left(rs.Fields("AvgCarbs"), 4) & strFE & "</td>" & _
        "<td style='background-color:#F5F5F5'>" & strFS & Left(rs.Fields("Carb_Ratio"), 4) & strFE & "</tr>" & "|"
'
   rs.MoveNext
   Loop
    
    Set olItem = myApp.CreateItem(olMailItem)
    Set olAccount = myApp.Session.Accounts.Item(1)
    With olItem
    .To = "me"
    .subject = "Blood Sugar Data"
    .HTMLBody = strBoxStart & "<br>" & Replace(strBody, "|", "<br>") & "<br>" & strBoxEnd
    .SendUsingAccount = olAccount
    .Display
    End With

**kd2017** · 06-19-2023, 12:42 PM

Unless I've overlooked something I feel like this has gone off the rails... I've been wrong before

Take a look at this, I took it back to something closer to the code in your original post. Obviously it may not run as is but I've highlighted the parts I'd like to draw your attention to.

Code:

    strBoxStart = "<script><table width='auto';style='text-align:left;border:1px solid black;font-family:calibri;border-collapse:collapse;padding:10px'><tr style='background:white;mso-highlight:blue' ctx.shadowblur;20; ctx.shadowcolor;blue></script>"
    strBoxEnd = "</tr></table>"
    
    strHTML = "<HTML><Body><table border='3' width=auto'><font color='blue' size='3' face='Times New Roman'><tr><th>DATE</th><th>TIME</th><th>READING</th>" & _
    "<th>DOSAGE</th><th>MIN RATIO</th><th>MAX RATIO</th><th>AVG RATIO</th><th>AVG CARBS</th><th>RATIO</th></tr>"
    
    strBody = strHTML
    
    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;"

    strFS = "<font size='3' face='Arial' style=text-align=center; vertical-align=middle>"
    strFE = "</font>"
    
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    dblMin = DMin("Ratio", "tblData", "[Ratio] > " & intMin) '<--- You didn't need to run these domain functions against the recordset, (in this context) they are run independent of it
    dblMax = DMax("Ratio", "tblData", "[Ratio] < " & intMax)
    dblAvg = DAvg("Ratio", "tblData", "[Ratio] > " & intMin & " And [Ratio] < " & intMax)
    dblAvgCarbs = DAvg("Carbs", "tblData")
    dblRatio = dblAvgCarbs / dblAvg '<--- your original post left out an equal sign here
    
    Do While Not rs.EOF
        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
    
    Set olItem = myApp.CreateItem(olMailItem)
    Set olAccount = myApp.Session.Accounts.Item(1)
    With olItem
        .To = "me"
        .subject = "Blood Sugar Data"
        .HTMLBody = strBoxStart & "<br>" & Replace(strBody, "|", "<br>") & "<br>" & strBoxEnd
        .SendUsingAccount = olAccount
        .Display
    End With

The SQL you posted in #21 above is quite inefficient

Code:


SELECT tblData.TestDATE, 
       tblData.TestTIME, 
       tblData.TestREADING, 
       tblData.TestDOSAGE, 
       DMin("Ratio","tblData","[Ratio] > 1")  AS MinRatio, 
       DMax("Ratio","tblData","[Ratio] < 20") AS MaxRatio, 
       DAvg("Ratio","tblData","[Ratio]")      AS AvgRatio, 
       DAvg("Carbs","tblData","[Ratio]")      AS AvgCarbs, 
       [AvgCarbs]\[AvgRatio]                  AS [Carb_Ratio] 
FROM   tblData;

It calls DMin(), DMax(), and DAvg(), which are VBA functions, from the SQL engine once on every single row of tblData returning the same value each time when all you needed to do was call them once.

Struggling With Calculating Recordset Fields !!!

Thread Tools

Similar Threads

Calculating IIf statement using two fields

Query not calculating all fields

Calculating a two date fields! Help please

Calculating subform fields

Issue with Calculating Fields in SharePoint (Projected Fields?)

Posting Permissions