Handling Extensive Maths in MS Access

[haxan786]

Hello Team

I have designed a power plant production database in MS Access, as of now its running smoothly even after 2 years.

Database is mostly doing maths for different task. Efficiency correction, Stock calculations with ASTM equations, and hourly calculation based on plant load, to speed up I am using custom functions in VBA but still I feel database is slowing down.

My question is that should I do calculations in forms and upload data to table or let it be in queries.

My hourly query has more than 40 calculated fields.

 

[Gasman]

General advice is not to store calculations, but just calculate when required.

 

[haxan786]

General advice is not to store calculations, but just calculate when required.

Calculation results are needed on daily basis, in a daily generation report where Daily, monthly, and 3 types of yearly calenders get data from hourly query.

Our generation invoicing is based on hourly calculation, everything is calculated on hourly basis like, Declaration, demand, generation, outages, forced Outages, liquidated damages, schedule outages, shortfall , access load, efficiency ( advance calculus with correction Factors ) , partial load corrections and so on ...


I was planning to shift calculations on form and upload the calculated values to hourly table but by this when there is a modification in any corrections factor equations i wont b able to update previous values.

 

[haxan786]

in below example you can see how I converted query based calculations to vba custom functions, but there are like more than 40 calculations.

'IIf(Nz([RevH1])<>"",0,IIf([NEO]+[SHORT_5_4d]<[NPCCD] And Nz([Upward Declaration])<>"",(Nz([Upward Declaration])-([NEO]))*1.5,0))


Public Function LDup(RDACH1 As Variant, NE As Variant, DMD As Variant, UDAC As Variant) As Double



    If RDACH1 <> "" Then
    LDup = 0
    ElseIf NE < DMD And UDAC <> "" Then
    LDup = (UDAC - NE) * 1.5
    Else
    LDup = 0
    End If
End Function

one of Efficiency equation example

Function GetCWT(ByVal T As Double, ByVal RH As Double) As Double

'this is combine function to calculate WetBulb Temp and merge with cooling water temp

GetCWT = 0.0036062 * ([T] * Atn(0.151977 * ([RH] + 8.313659) ^ (1 / 2)) + Atn([T] + [RH]) - Atn([RH] - 1.676331) + 0.00391838 * ([RH]) ^ (3 / 2) * Atn(0.023101 * [RH]) - 4.686035) ^ 2 + 0.4603 * ([T] * Atn(0.151977 * ([RH] + 8.313659) ^ (1 / 2)) + Atn([T] + [RH]) - Atn([RH] - 1.676331) + 0.00391838 * ([RH]) ^ (3 / 2) * Atn(0.023101 * [RH]) - 4.686035) + 16.789

End Function

 

[Gasman]

If you are looking for a trend, then, yes, you would need to store them.
If you have a unique identifier, then easy enough to update the values.

 

[GaP42]

Difficult to advise with the outline provided so far.
1. The "calculation" query is run hourly, the report daily (collating the hourly calculations)? Why would the query not be run daily too, with data filtered to each hour for the purposes of calculation, or do some rely on previous week. month, year, showing trends over time? is the data on which the calculation(s) are based transient and not retained?
2. If you store the calculation results, do you retain the reports or simply run the report each time for a required day?
3. If you plan to store calculated values but have concerns re updating old values then you need to consider how to store the results with a table that describes the calculation and version in use.
Calc Result table / Calc Master Spec table (with expression, notes, version and active flag, date active from/to? For some you may not be able to go back and recalculate from historical data.

 

[gemma-the-husky]

If you need a static calculation for a period of time you could store the result in memory somewhere without storing the result in a table. If it's used frequently or it's a complex calculation it probably might be of benefit.

I suppose if you store it in a table then all users can get the result, rather than all having to evaluate it repeatedly.

 

[tvanstiphout]

everything is calculated on hourly basis

If that is how you operate: the hour is over, that data is never going to change anymore, let's calculate the numbers and report on them (either now or at end of day, or in bigger reports looking for long-term trends), then I would calculate once, and store in one or more Results tables.

 

[haxan786]

Difficult to advise with the outline provided so far.
1. The "calculation" query is run hourly, the report daily (collating the hourly calculations)? Why would the query not be run daily too, with data filtered to each hour for the purposes of calculation, or do some rely on previous week. month, year, showing trends over time? is the data on which the calculation(s) are based transient and not retained?
2. If you store the calculation results, do you retain the reports or simply run the report each time for a required day?
3. If you plan to store calculated values but have concerns re updating old values then you need to consider how to store the results with a table that describes the calculation and version in use.
Calc Result table / Calc Master Spec table (with expression, notes, version and active flag, date active from/to? For some you may not be able to go back and recalculate from historical data.

1- All the calculations are done in hourly query. Then data is filtered in Daily, Yesterday, Weekly, Monthly, Fiscal Year, and Agreement Year in 6 different queries. All the data is collected for a Daily Generation Report.

This is the daily report, The left part is total calculattions based on hourly data and rigth side is coming from simple daily table with no issue.

I am converting Hourly query to Daily. The Daily query is used in all other queries with date range. Daily Sum, Average, Weighted Avg, all coming from hourly query. I am not a database expert, I just started building database and within year I was able to get fimilar with Table Structures, Relationships and VBA with help of this forum, which help me to update my database over the time.

All I need now is the performance.

 

[GPGeorge]

Have you considered creating a reporting version of the database, which you could update as needed, with the calculations required by your reports denormalized in tables that support your various reports? In other words, a Data Warehouse approach? Pretty much along the lines Tom suggested?

 

[Pat Hartman]

The primary reason for not storing calculations is because they become invalid if the underlying data changes. Data warehouses always store summarized data and calculated values. You have a transaction database that requires a lot of very recent history calculations. Without seeing the application and analyzing what is going on, I can't make a definitive statement. all I would say is that, provided history can never be changed and you enforce that rule, I would not be against storing the calculated values.

 

[haxan786]

The primary reason for not storing calculations is because they become invalid if the underlying data changes. Data warehouses always store summarized data and calculated values. You have a transaction database that requires a lot of very recent history calculations. Without seeing the application and analyzing what is going on, I can't make a definitive statement. all I would say is that, provided history can never be changed and you enforce that rule, I would not be against storing the calculated values.

In most cases, History data will not change ever, for some reason some of the equations might change over time which I can append manually.

Currently, I am only storing a single efficiency value in the hourly table, I am considering all the calculations to go in a table like the below example.

Approach-1: Doing all calculations in VBA and update the table on save button.
Approach-2: keeping the current hourly query live, when there is a change in any equation and requires updating previous data, update the Hourly table with Hourly Query ( Only by me )

Private Sub Command201_Click()
    On Error GoTo ErrorHandler

    Dim db As DAO.Database
    Dim rsDetails As DAO.Recordset

    Set db = CurrentDb

    Set rsDetails = db.OpenRecordset("SELECT * FROM [DDL Detail]", dbOpenDynaset, dbSeeChanges)

    Do While Not Me![Daily Generation Sub].Form.Recordset.EOF
        ' Retrieve ReadingId from the main form record (DDL Master)
        Dim ReadingIdx As Integer
        ReadingIdx = Me!ReadingId
        
        ' Retrieve DDL_ID from the subform record
        Dim ddlID As Integer
        ddlID = Me![Daily Generation Sub].Form![ID] ' Hourly table 'DDL_ID' with your actual field name
        
        ' Retrieve values from the subform record
        Dim neoValue As Double
        neoValue = Nz(Me![Daily Generation Sub].Form![NEO]) ' 'NEO'  in the subform
        
        Dim EffQ As Double
        EffQ = Nz(Me![Daily Generation Sub].Form![EfficiencyQ]) '  'EfficiencyQ' uncorrected efficiency in the subform
        
        Dim HRc As Double
        HRc = Nz(Me![Daily Generation Sub].Form![HRcorr], 1) '  'HRcorr' Heat Rate correction factor in the subform
        
        ' Perform the calculation for Efficiency
' Calculate effxValue considering null values in NEO or LHV
        Dim effxValue As Variant
         effxValue = Nz(Round(EffQ * HRc, 5))
    ' Ensure subform record is saved before updating
        Me![Daily Generation Sub].Form.Dirty = False ' Save any pending changes in subform

        ' Refetch the record to get the latest data
        rsDetails.Requery

        ' Update the Eff_Actual field with error handling
        rsDetails.FindFirst "[ID] = " & ddlID & " AND [ReadingId] = " & ReadingIdx
        If Not rsDetails.NoMatch Then
            rsDetails.Edit
            rsDetails![Eff_Actual] = Nz(effxValue, 0)

            ' Attempt update with retry mechanism
            Dim updateSuccessful As Boolean
            Do
                On Error Resume Next
                rsDetails.Update
                If Err.Number = 0 Then
                    updateSuccessful = True
                    Exit Do
                ElseIf Err.Number <> 3197 Then ' Not a write conflict
                    MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical
                    Exit Do
                End If
                On Error GoTo ErrorHandler
                ' Wait briefly before retrying
                DoEvents
            Loop Until updateSuccessful

        End If
        Me![Daily Generation Sub].Form.Recordset.MoveNext
    Loop

    rsDetails.Close
    Set rsDetails = Nothing

    DoCmd.RunCommand acCmdSaveRecord
    Me.E02_By = TempVars("EmployeeId")

    MsgBox "Record Saved", vbInformation, "Thar Energy Ltd."
    
    Exit Sub

ErrorHandler:
    MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical
    Err.Clear ' Clear the error for further iterations
    Resume Next ' Continue to the next iteration after an error
End Sub

 

[haxan786]

Have you considered creating a reporting version of the database, which you could update as needed, with the calculations required by your reports denormalized in tables that support your various reports? In other words, a Data Warehouse approach? Pretty much along the lines Tom suggested?

I have many considerations but I am here for expert opinion. I have already shared the problem statement. My plans are as mentioned in the last reply. Storing data on daily basis and manually when there is a big change in formulas.

 

[haxan786]

In future, I am also planning to connect my backend to PowerBI, where I will need to do DAX for all calculations from the start. That is one of the reasons for storing the calculated data.

 

[The_Doc_Man]

The general rule is that Access won't tell you anything you didn't tell it first. If you are asking it for trend-over-time data, you need to store the instantaneous value and its associated time marker that will contribute to that time-based trend.

If you NEVER need to go back and attempt to regenerate (rebuild) a report for a certain date, then there are theoretical rules that could permit you to only retain the sums that contribute to the formulas. However, if you have ANY history-based reports, then you need to have history saved in order to report on it.

Therefore, I offer at least a qualified "store it" vote. Note that this does not prevent you from eventually doing a "roll-up" summary of your historical data for your longer-term observations once you reach a point where you KNOW you will never try to go back to generate detailed reports.

 

[Pat Hartman]

Me![Daily Generation Sub].Form.Dirty = False ' Save any pending changes in subform

There cannot be any changes to save. When focus moves from the subform to the main form, Access automatically saves the subform record if it is dirty. Vice versa is also true.

You should be doing the calculations in a query or in the subform. When you refer to the subform, you are referring to the current record, whatever it is.

 

[GPGeorge]

I have many considerations but I am here for expert opinion. I have already shared the problem statement. My plans are as mentioned in the last reply. Storing data on daily basis and manually when there is a big change in formulas.

Unfortunately, I anticipated that you'd infer from my suggestion that you could continue to create the calculated field values, but do so in a separate accdb designed specifically for reporting purposes. In that reporting version, the tables would be as de-normalized as necessary to facilitate fast, efficient reporting.

Basically, either your Option 1 or Option 2 in post #12 would be effective. The resulting tables could be updated as necessary as well.

 

[haxan786]

Unfortunately, I anticipated that you'd infer from my suggestion that you could continue to create the calculated field values, but do so in a separate accdb designed specifically for reporting purposes. In that reporting version, the tables would be as de-normalized as necessary to facilitate fast, efficient reporting.

Basically, either your Option 1 or Option 2 in post #12 would be effective. The resulting tables could be updated as necessary as well.

Got it, thanks everyone for valuable inputs.