How to pass/convert a calculated field value to a static field value (1 Viewer)

[Les D]

I need to know how to take the value returned by an expression in a calculated field and place it in another 'static' field that doesn't update itself when the underlying data in the original expression changes.

This is the application: Ten years ago wrote an invoicing program that calculates and adds sales tax (from various combinations of three jurisdictions) to each invoice. The problem is that sales tax rates (which are stored in a 'sales tax table') recently changed - something I didn't anticipate when I wrote the database.

If I update the tax rates in the sales tax table, older invoices are also recalculated using the new rates. Obviously, I need the invoices written under the prior tax rates to remain unchanged.

I am a beginning to intermediate-level user. Am I on the right path or missing an entire concept for handling this type of data?

Thanks in advance for any help!

-Les

 

[NickHa]

I'd be inclined to keep the old and new rates separate (i.e. two entries for each tax type). If you include a valid from date, you can select the record you want based on the invoice date.

I fell foul of this problem some years ago when trying to cope with taxes for each state and territory in Australia - so you have my sympathy!

 

[Les D]

That makes sense, although I was hoping I had overlooked an even simpler answer. I would have thought it common for the results of certain calculations/expressions to be stored in a static state... such as running and then comparing the results of "what-if" scenarios by changing the underlying data.

I'll give the date approach a try. Thank you for the suggestion!

 

[Beetle]

Another option would be to add a field to the invoice table to store the amount of tax at the time of sale for each invoice. This is not a violation of normalization (IMO) because it is a static value that needs to be stored for historical purposes. In your case you would need to add the field and use an Update query to push the value into that field at the old rate for the existing records. Going forward, you would likely use a Form/Control event to push the current tax amount into that field for new records.

 

[Les D]

Sean: This is along the lines I envisioned. An udate query to post the old data and a contol to pass newly calculated data. I may get stumped on the control, a macro I assume, but will give it a try. And... hello from Fort Collins on this beautiful Spring-like day in Colorado! -Les

 

[Beetle]

I would use code for this, most likely in the Before Update event of the form. Code might look something like the following (untested, with just some made up variable and field names - but it should give you the gist).

Private Sub Form_BeforeUpdate (Cancel As Integer)

'declare the variables
Dim curTotalTax As Currency
Dim dblTaxRate As Double

'lookup the current tax rate from the tax rate table
'this assumes tax rates are stored as values like 0.078
'if stored as percentage values like 7.8 you need to divide
dblTaxRate = DLookup("TaxRate", "tblTaxRates")

'calculate the total tax
curTotalTax = Me!MaterialTotal * dblTaxRate

'push the TotalTax value to the field
Me!TotalTax = curTotalTax

End Sub

And you're right. It is a beautiful day here in the Centennial state. Welcome to the forum by the way.

 

[Les D]

Thanks for just the gist, Sean. Tax rates would have changed again before I figured this out on my own. I was thinking of other similar situations such as product totals on an invoice that are based on an underlying item/price table... you wouldn't want prior invoice totals to change when prices are changed. I think there are many situations like this, waiting to bite those at my level south of the belt line. Thanks again, Les

 

[missinglinq]

The two ways of handling this type of thing have been addressed here, and IMO, creating a current tax rate Field, updating existing Records placing the old tax rate in this Field and, moving forward, storing the current rate at the time that a New Record is created, is the way to go! The trade-off of

• Storing an extra bit of data
• ..................vs..................
• Looking at the invoice date
• Going to the tax rate table
• Comparing the invoice date to the 'from' tax rate date
• Returning the appropriate tax rate

every time a Record is accessed/created is a no-brainer!

Linq ;0)>

 

[Les D]

Linq: We are all in agreement. And as Sean said, and I concur, the two feilds constitute two different types of data, dynamic and static. Even if that means the DB isn't technically normalized, I think the benefit ourweighs the cost- especially in my business where one Photoshop file is larger than five years of invoices! Thanks for the input. -Les

 

[NickHa]

Well, we don't all agree here, but that's OK

If you look at the problem from a purely technical point of view, then the arguments are valid. However, if you look at it from a business point of view, you have to cater for the dynamics of change - both historical and future - hence my preference for a new dated record.

The key point here is that Les has a satisfactory outcome for his needs, so in that respect I'm content with the solutions offered.

 

[Les D]

NickHa-You are gracious, thank you. How would you handle the other situation I mentioned concerning item price changes pulled from a product table into an invoice? How do you protect the item total (presumably a quantity*price expression) in the invoice when the underlying item price changes? Is this not the same problem as the tax issue? I ask because it's another issue I need to address in my programming and thought I had it solved using Seans suggestion.

Please keep in mind, my new and respected Access friends... I know just about enough to identify a problem, only to go on and solve it incorrectly. The questions I ask are not arguments... I don't know enough to argue!

What I do know is how thankful I am for the input I've received. -Les

 

[NickHa]

Yes, price changes are much the same as tax changes or any other financial deviation such as manufacturing and transport costs.

In an ideal world I would advocate the maintenance of historical data and I would encourage the change in any input variable to be maintained in a journal of some kind. In a database, journal = table, with the necessary fields to record when, where and why the change happened. Crucially, the ability to calculate any value relative to the date of execution should be maintained. It is not unknown for taxes or price changes to be retrospective (especially where governments are concerned!).

In a practical world I would take a more pragmatic view and do what's needed to satisfy the problem at hand, which is where I think you are. So the simple answer in your situation is to do what works for you and to follow the KISS principle.

I have neatly avoided answering your question here - sorry!

 

[Les D]

Ahhh, but you did answer it in that I know I am dealing with an issue that presents itself in a variety of ways and requires a similar solution.

KISS? Yes. I bear in mind always that I am a small businessman and not a fortune 500 company. I program small apps like this because I need to out of necessity. I need quick, practical, effective solutions that get me back to my 'real' work quickly. If I have to revisit a program I wrote 10 years ago because of an oversight like changing tax rates... well that's a success overall.

I suspect my next posting will be more specific, like... why isn't my poorly-written code doing this or that! Truly, I love what Access does and wish I had time enough to become more proficient at it.

Cheers, Les