Calculated Fields and Breaking the Rules

[GK in the UK]

Just reached a milestone in my replacement accounting application. Imported about 50k records and after a few false starts I now have matching debtors, creditors and trial balance. I had to code a 'conversion' routine to deal with differing conceptual design but my project seems to have come to life now it has forms filled with data.

I stuck to the 'no calculated fields' mantra. But I wonder if it was counter-productive. I've lost count of the number of queries and code lines I've had to write stating (pseudo code):
Invoice_line_total = qty x units x price - discount. Then do it again to add the tax element. An additional (whisper) calculated field for line_total written at invoice creation time would have saved loads of code.

for the invoice total, I code:
Invoice_total = net value + vat value
for a part paid invoice, I code:
Outstanding = net value + vat value - value allocated

It does seem tiresome at times. All these queries could have been simplified if I just updated the calculated field at the point of creation, which is on two forms only, frmInvoice, and frmPayment. It's not too late to change it (though it will be quite a bit of work - I suspect I'll leave it alone). Should I be the purist or the realist ? What say the Access gurus ?

 

[pbaldy]

I'd consider the new Calculated data type if you're using a recent version.

 

[plog]

I've lost count of the number of queries and code lines I've had to write stating (pseudo code):

You should have only had to write your equations in just one query (let's call this BaseQuery). Then instead of writing the equation in another query you would instead use BaseQuery as the datasource for that new query and your equation would be a field available to you.

Outstanding = net value + vat value - value allocated

I'm also thinking you might have structured your data incorrectly. Generally you use SUM() to add a bunch of values together, not write a long equation. For example if you wanted an inventory balance you would store credits and debits to inventory all in the same field:

tblInventory
invDate, invAmount, ID_Product
1/1/2019, 18, 1
1/2/2019, -14, 1
1/3/2019, 10, 1
1/4/2019, -12, 1

Then when you wanted a balance it would be this query:

SELECT ID_Product, SUM(invAmount) AS Balance FROM tblInventory GROUP BY ID_Product

and the credits and debits would be summed and you would have your balance. How many fields are you storing values in that you have to add up? Just the 3 listed above or are there more?

 

[GK in the UK]

3 fields plus one for the unit quantity. It's not a sum, it's a calculation. It crops up in a lot of places, for example a ledger, where I need to show the net value for each record. So it's not the column totals, it's the record value. Of course I use SUM to get the total of many records.

Here's my line calculation function. ALL line calculations are done in here. It just seems so unwieldy to have to drop the names of four fields in there. My question was posed, partly, because when I began to look closely at the data I needed to export from my existing application, a commercially available (albeit discontinued) one, I could see that the net value is saved along with the fields that it's derived from, and I thought, uuhh, maybe I should have done the same

Public Function fTransLineNetValue(quantity As Currency, ConvFactor As Currency, SalePrice As Currency, DiscPercent As Currency) As Currency
' usage:    = fTransLineNetValue (nz(tlQuantity), nz(tlConversionFactor,1), nz(tlSalePrice), nz(tldiscPercent))
        
Dim LineValue As Currency


' calculate the value before discount
LineValue = fRoundCurr((quantity * ConvFactor * SalePrice), Cplaces)
' now deduct the line discount
LineValue = LineValue - fRoundCurr((LineValue * DiscPercent), Cplaces)
fTransLineNetValue = LineValue
    
''    MsgBox ("quantity = " & quantity & vbCrLf & _
''        "ConvFactor = " & ConvFactor & vbCrLf & _
''        "SalePrice = " & SalePrice & vbCrLf & _
''        "DiscPercent = " & DiscPercent & vbCrLf & _
''        "LineValue = " & LineValue)

End Function

 

[gemma-the-husky]

to be honest, when we get to an invoice, I would be inclined to store invoice line extensions, VAT calculations, invoice totals and so on.

For one thing there needs to be no doubt how you have treated rounding in your database. The VAT total obtained by evaluating VAT per line is likely to be different from the VAT total obtained by summing the invoice lines, and evaluating VAT on that total.

Also you may need the total to squirt into a separate accounting system.

With static data, such as calculated invoices, the underlying information isn't going to change anyway.

By the same token, surely no-one (well, some might) would say don't store the price charged on an invoice line, because you can re-extract it from the pricing tables.

I think you have to be practical about some things.

 

[GK in the UK]

Yes, thank you, I'm kind of wishing I had saved the net total in the invoice lines. I'm a bit annoyed with myself for not anticipating what I was letting myself in for. I think I'll add the field and just work through simplifying the queries and data sources. Obviously the existing calculation will continue to work.

I am saving the vat and net total in the header record.

Funny you should mention vat, following the guidance for vat calculations and rounding has been an interesting exercise. Individual lines should be rounded to 0.1 pence, and the vat total should be the sum of the line vat (to 0.1 pence) and finally rounded down to the nearest 1p.

There are other methods but that's the one I used.

 

[Pat Hartman]

The problem with storing calculated values, especially those used in multiple places is that you always run the risk of someone missing some update somewhere and letting a change happen that impacts the calculation but that doesn't always get applied.

I use two methods depending on the complexity of the calculations.
1. If they are simple and involve only one table or one table and one lookup (for rates), then I create a base query and all subsequent queries use the base query so that I always have only a single place to modify a calculation.
2. If the calculations are more complex or include aggregation which would prevent the main query from being updateable, I use a function.

I've worked with lots of accounting packages and they seem to be locked into saving calculations which I always find difficult and several have actually been unreliable. If you are conscientious and everyone who works on the app is equally conscientious, then you can probably get away with saving calculated data but if you want only a single method, I'd go with the function.

 

[MajP]

As others have said, if the calculation is involved I will use a function.
Lets assume you have to pass 4 fields to the function then your call in the query might look like

Select CalculateValue([FieldA],[FieldB],[FieldC],[FieldD]) as SomeName, otherFields from SomeTable.

But even this can be a pain to always type out, especially if you end up doing it a lot.
So the following is not an efficient way, but I may do it like this if efficiency is not needed. I will just pass in the PK and calcualte the four fields using dlookups.

Public Function CalculateValue(PK as long) as Double
  Dim valA as long
  Dim valB as Double
  Dim valC as double
  Dim valD as long
  Dim strWhere as string
  strWhere = "SomePKfield = " & PK

  valA = nz(dlookup("FieldA","SomeTable",strWhere),0)
  valB = nz(dlookup("FieldB","SomeTable",strWhere),0)
  ValC = nz(dlookup("FieldC","SomeTable",strWhere),0)
  valD = nz(dlookup("FieldD","SomeTable",strWhere),0)
  'do some calculation
  CalculateValue = ...
end function

Now to use it, it is a lot easier to type.

Select CalculateValue([SomePkField]) as SomeName from SomeTable.

On a very large query I imagine this would be noticeably slower.

 

[pbaldy]

On a very large query I imagine this would be noticeably slower.

It could be sped up by using a recordset instead of the DLookup's. One trip to the data instead of four.