|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Stored or Calculated By Richard Rost 
5 years ago
When to Use Stored Instead of a Calculated Value In this video, we will discuss when you should use a stored or a calculated value in your Microsoft Access tables. I'll show you how to apply a customer discount based on the time the person has been a customer (number of years) but also allow for you to manually edit the discount amount. I'll show you how to display what the discount should be on the customer form and we'll make a button to apply it. We'll also apply it if the current discount rate is empty. We'll use conditional formatting to highlight the calculated value in red if it's different from the stored one. Bryce from Washington DC (a Platinum Member) asks: I give my customers a percentage discount based on the time they've been a customer. For example, after a year it's 5%. After two years, it's 7%. And so on. I need the ability to manually change it, however. I give some customers a higher discount if they’ve made a lot of purchases. Others get none. It depends on many factors, and one of those factors is that it's completely up to me. LOL. How do I track this in Access? MembersMembers will learn how to make a much more complex function to determine the discount rate based on multiple factors include table lookups. We'll put this public function in a global module. We will then use an Update Query to assign values to any customers who do not have one.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Links
IntroIn this video, we will talk about whether you should store a calculated value in a table or always calculate it on the fly in Microsoft Access. We'll cover best practices for database design, when it's appropriate to store values versus calculating them in queries, and the real-world exceptions to the rule, such as when you need to manually edit a value or improve performance. I'll also show you how to implement both a stored discount rate and a calculated suggested discount for customers, along with a simple VBA trick and conditional formatting to help manage these values in your Access database.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, we're going to discuss whether you should use a stored value or a calculated value in a field in Microsoft Access. Generally, the rule of thumb for good database design is if you can calculate a value, you should calculate it and not store it in the table. However, there are some exceptions, and we're going to talk about those exceptions in this video. Here we go. Usually, I begin the TechHelp videos with the question, but first today, before I give you the question, I want to go over some basic theory. Now we've got a prerequisite first. If you don't know what calculated query fields are, where we perform a calculation in a query, go watch my calculated fields video first. Here's the URL on your screen. I'll put a link down below in the links section so you can go click on it. But go watch that first. If you don't know what calculated fields are, then you're not going to understand the rest of this video. When I say calculated query fields, I mean query fields. Do your calculations in queries. The calculated field data type in tables, that's evil. Don't use it. I hate that, stay away from it. Now that we know what calculated query fields are, the question is, and the gist of this video is, do we calculate that value on the fly all the time or do we calculate it once and store it in the table? Usually, if you can calculate a value and it's the same calculation every time, it is best to do so. Here, for example, you can see the customer last first query that we built in the blank template. It's just last name comma first name, but it's a calculated field. It's always the same: take the last name, comma space, first name, done. Here's another one, extended price from our order detail. Quantity times the unit price is the extended price. That's always the same; it never changes. It is rare to store calculated values in the table. Lots of people email me. They say, hey, I did this thing in the query and it's not saving in the table. I have to use it for something else. Well, no, it's a calculated value. You're going to use that query to get that value. You're going to base your forms and your reports and your other queries on that calculated field in the query. One of the major reasons why you want to calculate it is because if you change the formula, if you change the calculation and you have that value stored in the table, now you've got to go change all those values either manually or with an update query. This is why, for things like extended price, you want to calculate it. If something is date specific, it's going to constantly change. That's why you use a calculated field, like years as a customer. Today's date is going to change every time you open this query. If you wanted to display that value on the customer form or in a report, as of today, you're going to want to use a calculated value for that. If you're going to decide to store a value, let's say in the customer's record, you have to control all points of data entry for that field. Now we already know giving your users direct table access is bad. If you've taken even my Access Beginner 1 class, I tell you, your end users should never work directly in the tables. You as the developer, maybe, if you've got some reason, if you want to go in there and tweak some stuff, fine, but generally people using the database, even you, should go through the forms. Now even in our simple free TechHelp template, I've got three different forms that are based on the customer table. There's the customer list, the basic customer query, then the customer query with the contact subform. If I do a stored value and it's based on a calculation, then I've got to make sure I update that in three different places. The code to change that has to be in three different places. Like that last name, first name. That was a stored value, not a calculated value, but a stored value. I'd have to make sure to put whatever code updates that in three different forms. That can be cumbersome. So we have to make sure if you are going to use a stored value, you've got to update all of the calculations. Now, of course, there are exceptions to the rule. Usually I say generally yes, 90% of the time you're going to calculate the value, but there are exceptions. The number one exception is if you need to manually edit the value. That's actually the point of the question today. The person's asking me, she has to be able to manually edit it. She wants to use a formula to figure something out, but she wants to be able to override it. That's what brought up this particular video. But there are some other instances too where you might be better off storing a value. If you do a lot more reporting than data entry or edits - in other words, let's say you get summary information every day and you have to enter in some data, and every day all day long you've got dozens of people on your network constantly pulling reports off of this information. Big queries, lots of data, you're constantly pulling information, especially over a network. If you don't update it, maybe you make one little change here and there. If you're constantly reporting on it, it might make sense to save that value in the table. The amount of editing is tiny; the amount of lookups and the calculations required is very great. If you're doing a lot of lookups on a value, especially if you're looking up something from a different table, then you might want to consider storing that data instead of looking it up constantly over and over again. Obviously, if that value is based on an aggregate, like if you're doing customer order totals, for example, you want to store a value that's how many orders - what's the total dollar amount that this customer is worth? Well, every time you pull that up, it's got to calculate and add up all of their orders. If you perform that function multiple times a day, you may want to consider storing that information. If it's something you look up once in a while, leave it as a calculated value. Especially an exception is if you want to speed up your database, especially over a network, pulling down lots of records, constantly over and over again, over that wire. If you're not using a database server and you're just using a Microsoft Access backend, that can slow things down. Storing the value might speed things up. I'm not saying go out and change all your calculated values to stored values. Remember, these are exceptions to the rule. This is probably going to be like 1% of the time you might want to do this. Use a stored value if you need to manually edit it. That goes without saying. You can't use a calculated value if there's ever a chance that you have to manually change it. You can use a calculation to give you a default value or to say, hey, it should be this. Then, of course, you have to store the value at the table by manually editing it. Use a stored value if the calculation is based on an aggregate from values in other tables and your database is running slow. Like I said, if you're constantly pulling information like customer credit limits and it's got to constantly make multiple calculations on thousands of records and things are running slow, you might want to consider storing that value. Use a calculated value if the formula is always the same, and especially if it's based on fields in the same record, like that extended price. All those fields are in that same record, so you don't have to do any lookups in other tables. Calculate that. If you're more concerned with proper database normalization than performance, if you want to make sure your database is built properly and that it's a little more bulletproof and you don't want to take any shortcuts, and performance isn't totally a big deal, then use the calculated field. Calculated values are technically the right way to build it. Storing a value that should be calculated for database performance technically goes against the rules. But I say there's theory and then there's real world practice, and sometimes you have to make little shortcuts to get your database to run better, even though it's not theoretically the best way to do it. Let me give you an example of when it's better to store a value that should be calculated. Let's take into consideration social media. Social media posts generally have likes associated with them. Little likes down here, you click the like button, and it adds them all up and shows you, whether it's Facebook or Twitter. Soon on my site, I'm going to be adding likes pretty soon on my site too. The post itself is going to get many times more views than likes. This particular post is going to be seen by, let's say, 10,000 people, and maybe those 10,000 people, 13 of them are going to click on that like button. You want to optimize this for viewing. For viewing the information, for reading the information, that little tiny, you know, 1,000 people that actually click on the like button, that's minuscule. The like data is going to be stored in a separate table so you can track which users like the post. If you hover your mouse over that 13 likes, it shows you a list of all the people who liked your post. That's important data to save if you care about who liked the post. That's all in a separate table, that's basic relationships 1-on-1. We don't store that in the post table. Now, to speed up views for that post, let's call it a post. To speed up the views, you're going to store a count of those likes in the post table, which is only updated when someone clicks that like button. This table has the post data in it, like the post ID, the number of likes, the person who posted it, the description, the full whatever. This is the post table. If you're going to display this information 10,000 times, all you have to do is read one little value out of one record, out of one table. If you didn't store this value, what would have to happen? If you wanted to display post 1,000, if you wanted to display the number of likes, now you've got to go to the likes table, count up all the 1,000, there's five of them, and then display it. This has to happen 10,000 times for 10,000 views, versus just displaying that number five. The 13 times out of that 10,000 that someone actually clicks like, all you have to do is add a record in here and update that field. But you have to make sure that you update this field anytime someone likes or unlikes your post. There's a little more maintenance involved on your end, as the programmer. You can't just issue a DCount. You've got to track values in two tables. There's a little more work involved, but it will greatly speed up your performance. Now, with all of that theory under our belt, let's actually get to the question. Today's question comes from Bryce in Washington, DC, one of my platinum members. Bryce says, I give my customers a percentage discount based on the time they've been a customer, for example. After a year, they get a 5% discount. After two years, it's 7%, and so on. I need the ability to manually change it, however. So right there, you can see she has to manually change it, so that means we're talking stored value, and not just a calculation. She continues, I give some customers a higher discount if they've made a lot of purchases. Others get none. It depends on many factors, and one of those factors is that it's completely up to me, LOL. How do I track this in Access? Well, Bryce, this is one of those situations where you can't just use a calculated value, because you need the ability to manually change it, which is one of the things I said earlier. In your case, I would recommend displaying two fields on the customer form. Display the actual discount that you've given them. A second field would show what it should be based on how long they've been a customer. That value is going to change. If customer A places an order today, their discount is going to show a zero, because they haven't been a customer for a year yet. If they come back six months later and want to place an order, it still shows zero, because they haven't been a customer for a year yet. If they come back after a year, their discount's still zero, because it's a manual stored value, but that second field will show and say, hey, they should have a 5% discount, do you want to change it? We can even use some conditional formatting to make it look red so whomever is going to put the order in knows they now qualify for a 5% discount. But you don't want the system to automatically change it, because you want to be able to manually make that edit. So I'm going to show you how I would implement this in Microsoft Access. Here I've got a copy of my TechHelp free template. This is the free download from my website. You can grab a copy if you'd like. It's absolutely free. I'll put a link down below in the downloads section. In here, I've got a customer form with a customer since field. That's a date field. We can use that to figure out how long they've been a customer for. Let's put a value in the customer table. Bryce needs to be able to manually update this, so it has to be a field in the table. We'll call it discount rate. That'll be a number. I'm going to make this a double format as a percentage. So 0.01, for example, will be 1%. It just makes calculations easier, and you can also have fractional discounts, like 12.5%. Default value zero is good. Close this. Save changes. Yes. Let's drop it in the form now. Right click, design view. We can just copy one of these other fields here. I'll copy family size, copy, paste, slide it down here. This will be the discount percentage. That's fine. Open this up. This will be the discount rate. Make sure you copy the name up here too, and the format will be percent. Save that. Close it up. Open it back up again. For this one here, I'll put a discount percentage in for 10%. Remember that's at the bottom of the tab order now, so if you hit tab, it's going to go to the next record. Now, this is all fine and dandy, but Bryce also wants to be able to see what their discount rate should be, based on the calculation. What we'll do then is simply add an unbound calculated field down here. Copy, paste. Change this to "should be" like that. I like to make calculated values gray, so I'll just gray that out a little bit, not too dark. Now, open this up. The name of this field is going to be discount calc for calculated discount rate, and the control source is where we'll put our function. That will display what this calculation should be. We'll use Bryce's calculations. She says that if they've been a customer for two years, it's 7%. Otherwise, if it's been a year, they get 5%. Otherwise, it's zero. As a pretty straightforward, simple calculation, I'm going to use the switch function. If you've never used the switch function before, go watch my video on the switch function. I'll put a link down below in the links section. Switch is basically like a nested if function. So, it's equals switch. Let me zoom in so you can see this better. There we go. Switch, and it's just expressions with values to use if the expression is true. For example, I'm going to calculate years roughly. We could get exact years with a more complicated calculation, but this is good enough for just doing something simple like this. There are 365 days in most years, so 730 days in two years. I'm going to say if today's date minus customer since (and in queries and in field properties, usually I like to play it safe and put the brackets around there, but you don't always have to), if that is greater than 730, set this value to 0.07. That's 7%. If that evaluates to true, they've been a customer for two years or more, give them the 7%. We're done; it stops checking there. I'm going to ask for the last time after the switch function, because the next one is going to be more than a year, which technically they both are true, but it short-circuit evaluates. In other words, once the first condition is true, you're done. It exits the function. The next value is if date minus customer since is greater than 365, give them 0.05. Otherwise, for all other values, give them a 0. How do you represent that? I say 1 equals 1, comma 0. If 1 equals 1 is always true, so if they get this far, they'll always get the 0. If you want to make it false, just make it 1 equals 0. It puts that in there. This is our calculated value. This is what it should be. Save that. Close it. Close it, open it back up again. This guy's got a 10% discount. He should have a 7% discount. He's been a customer for many years. Now see, this value is for display purposes only. We're not calculating this on the fly for every single customer. If you want a report that's based on this value and you've got 10,000 customers in your database, this value is now calculated for each customer as you go to the customer. If I go over here now, I can see this customer should have a 7% discount. Now let's fill it in here. What I can do here is simply copy and paste this value (copy and paste), and we're good. Or we could even make a little button here to click on it and it copies that value up there. In fact, let me show you how to do that. This is bonus material. Usually I don't go over a lot of programming in the TechHelp free ones, not because I'm holding back, but because I know a lot of beginner users watch the TechHelp free videos. Usually my more advanced users are the members. But I'm going to show you a real quick way. If you've never done any VBA programming before, go watch my intro to VBA video. It's free. It'll give you all the basics. It's 10,000-15 minutes long. Go watch it. Let's make a little button right here. I'll just copy the context. Actually, one of these buttons here, I think, has an embedded macro. Let me see what we've got here. That's an event procedure. Event procedure I can steal. I'm just going to copy and paste this one. I'm going to slide it down here, and I'm going to say apply. In other words, that's what they should have. I'm going to apply that value, apply that discount to up here. That discount rate, and this one's discount calc. It's real easy. I'm going to right click, build event, and there's a build event way up top there, or you can come over here on the on-click event. See, on-click event right there. Either one works. You might get asked what builder you want. Pick the code builder if you've never done any VBA programming before. I'm inside private sub 34, Command34_Click. I should have given the button a name first, but I'm skipping that step for today. So what am I doing? I'm just going to say here we're applying the value from discount calc. So we're going to say discount rate equals discount calc. That's it. It's all you have to do. One line of code. See, it's not scary. Come back over here, save that, close it. Now, when I click apply, that's going to copy that seven up to there. Boom, done. That's how easy that is. So simple. VBA is so easy to learn. What you could also do if you want, and this is totally up to you, is to automatically give someone a default value if they don't have a discount rate. How do you do that? I suggest going back to the customer table. Go to discount rate. Get rid of that zero in there. Don't default people to zero unless you want zero. If you want that zero, leave the zero. If you want to default that, you can use something called the on-current event. I've got videos on the on-current event too. I'll put a link down below about on-current. On-current event is an event that runs when you move from record to record, including when the form opens and the first record loads. Go to the form properties, find the events, on-current. Code builder. Here I am in the form current event. Here, I can say if isnull(discount rate), then discount rate equals discount calc, end if. In other words, if they don't have a discount rate already, give them the default discount, whatever's in that value. Close it, save it, open it back up again. If I move to someone who doesn't have one, it puts it in automatically, and you can't really see it happening, but it's happening. Here, I'm on this guy. His customer since is showing up as blank, so he gets a zero. If you go to a new record, it'll also be a zero. Let's do someone who's, what was it, a year? Should give a five, so let's change this person here. What's today? I'm going to hit control semicolon, 3/3/21. If I make this just over a year, if I go 3/1/20, now they've got a 5% discount calculated. If I hit apply, they get that. If I delete that, now it's null. If I leave the record and come back to it, that default value gets repopulated because it was null and the on-current event set it to five. That's up to you, Bryce. If you don't want to do that, if you want to leave it zero, if you want it to be zero, don't make it null. Put a zero in there and then that on-current event won't change it because you made it zero. That's the benefit of using a stored value: you can have the calculation, and you can use it - even use that calculation to give you a default value. I wouldn't put that in the default value of this field, simply because when you create a new record, the customer since is going to default to now, so it's going to be zero. I would leave it in the on-current event. Want this guy to be red if these two are different? Because you might come back later next year, you might open this up and see, oh, you had the 5% discount. Now you qualify for a 7% discount, and you want to apply the new one or not, based on your decision. Let's use a little conditional formatting. If you've never done conditional formatting, I have videos on that. There's conditional formatting, and there's expression-based conditional formatting too, which is a separate video. We can come in here, pick this guy, go to Format, Conditional Formatting, add a rule. Say the field value is - I can't see it - the field value is not equal to what value? Well, it's actually a field, so make sure you put it inside brackets for a field. Otherwise, it'll put quotes around it and that's not what you want. If it's not equal to discount rate like that in brackets, then set whatever color you want. So, background red, foreground white, maybe bold it. That'll be applied if the value is not the discount rate. Save it, close it, open it up. Oh, look, that one's red. That visually tells you, maybe I should apply that. Let's check someone else out. Oh, this guy's a 5%. He should be 0, and he should be 5. I want to leave him though. He's a bit of a pain, I'm going to leave him at 0. Next, 7s. Good, good. This way, when you open up a customer, you'll see if they should have a discount or not. You can see this system gives us the ability to have a calculated field that shows us what the value should be. We can even use it as a default value if we want to, but because it's a stored value, we have the ability to manually change it. We're getting the best of both worlds here. We also have the benefit that if we do lots and lots of reporting on this value, it'll run much faster because it doesn't have to run this function, this calculation, every time you run a report - the value is stored in the table. Want to learn more? In the extended cut for members, I'm going to show you how to do the same thing, but with a much more complex discount formula. We used a very simple formula to calculate the customer discount. There's basically one criterion with two values in it. What if you want to base it on one year, two years, three years, five years, different tiers in there? Then we also have to give them extra bonus points based on the amount of total orders they placed. If the customer has ordered at least $2,000 of stuff, give them an extra point of discount, and so on. Multiple criteria looking up from different tables. The whole reason why you want to store this data in the first place is because you've got a crazy complex formula calculating that value. We'll build a custom function in a public module. Then we'll build an update query to apply that same value to all of the previous customers in the database who might not have a discount rate assigned. You're not just necessarily waiting for someone to open up that customer record, just assign it with an update query. That's all covered in a half-hour long extended cut for the members. Silver members and up get access to all of my extended cut videos - there are well over 100 of them now. It's a very good value. How do you become a member? Click the join button below the video. After you click the join button, you'll see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I've built in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. Platinum members get all the previous perks, plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more. If you like this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select all to receive notifications when new videos are posted. Click on the show more link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level One course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel. If you like Level One, Level Two is just $1. It's also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level One course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is the general rule of thumb in Microsoft Access database design regarding calculated values?A. Always store calculated values in tables for easy access B. Never use queries for calculations C. Calculate values on the fly rather than storing them in tables D. Use calculated fields only in reports Q2. Why is it usually better to calculate values instead of storing them in the table? A. Calculated values are harder to change B. Changes to the formula require updating all stored values if stored in the table C. Tables can only store text fields properly D. Queries cannot be used in reports Q3. Under what circumstance should you consider storing a calculated value in a table? A. When the value changes every day B. When users may need to manually edit the value C. When the query supports calculated fields D. Only when using lookup fields Q4. Which of the following scenarios is an exception where storing a value instead of calculating it might be better? A. When the calculation always involves the same fields in the same record B. When aggregate values from other tables are frequently used in reporting, causing performance issues C. When normalization is your top priority D. When you want to avoid any data redundancy Q5. What is a major maintenance drawback of storing calculated values in multiple forms? A. Formatting issues will always happen B. Queries will not reflect stored values C. You must ensure all locations where the value can be changed are kept updated D. Users can overwrite the formulas Q6. If you only occasionally reference a calculated value, what is the recommended approach? A. Store it in the table to save time B. Calculate it in a query each time it is needed C. Use a lookup wizard D. Create a duplicate field in the table Q7. What is the recommended solution if a value should default to a calculation but also be editable manually? A. Only use a calculated field in the query B. Store the value in the table and provide a calculated field for reference and defaulting C. Lock the field from user edits D. Use only a lookup field Q8. In the example of social media likes, why might storing the like count in the post table be preferable? A. User likes rarely change B. Displaying post counts requires heavy transactional logic C. The like count is needed often and recalculating each time from the likes table would slow performance D. User data should not be linked Q9. What was Bryce's requirement for discount rates in her Access database example? A. Discounts are always calculated solely based on years as customer B. Discounts must be manually editable based on several factors C. Discounts should never be displayed to users D. Discounts should be calculated across several tables each time Q10. What is a suitable data type and format for the 'discount rate' field as described in this video? A. Text, formatted as general text B. Number, formatted as percentage (Double) C. Currency, formatted as Euro D. Date/Time, formatted as short date Q11. What technique does Richard suggest to visually alert users when the stored value and calculated value differ? A. Changing the table background color B. Adding a pop-up message for every difference C. Using conditional formatting to highlight the field D. Disabling the form until corrected Q12. What is the benefit of using the On Current event for assigning a default discount rate in the customer form? A. It only works for new records B. It applies the default value every time a record is opened if the field is null C. It prevents the user from ever changing the discount D. It recalculates everyone's discount rate and overwrites manual entries Q13. For performance and normalization, why is it not recommended to store aggregate calculated values unless necessary? A. Doing so may lead to data not updating properly if the calculation changes B. Queries cannot retrieve stored values C. Forms cannot use stored values D. Access does not support aggregates Q14. What should you do if you want Access to provide a default calculated value only if the value is currently blank (null)? A. Set a default value in the table design B. Use the On Current event to check for null and assign the calculated value C. Create a trigger in SQL Server D. Use table macros exclusively Q15. Which function is used in the video to create a simple calculated discount field based on number of years as a customer? A. IF B. SWITCH C. CASE D. CHOOSE Answers: 1-C; 2-B; 3-B; 4-B; 5-C; 6-B; 7-B; 8-C; 9-B; 10-B; 11-C; 12-B; 13-A; 14-B; 15-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone explores whether you should store a value in your Microsoft Access tables or calculate it dynamically using queries and forms. This is a common database design question, and the decision can affect both the integrity and efficiency of your database.First, let's review the basics. If you are not familiar with calculated fields in queries, I recommend reviewing my earlier lesson on that topic, as this concept is fundamental to the discussion here. Calculated queries allow you to generate results—such as a customer's full name or the extended price on an invoice (quantity times unit price)—on the fly. Generally, good database design principles dictate that if a value can be calculated from other data, you should calculate it rather than store it redundantly. Storing calculated results in tables can lead to inconsistencies and more complicated maintenance when your underlying formula changes. For instance, suppose you use a query to show each customer as "Last Name, First Name." This is created using calculated query fields, and there is no need to store the formatted name in the table since it can always be recreated from the individual fields. Similarly, the extended price in an order should always be calculated as quantity times unit price, not stored, because both components exist in the order details record. However, there are exceptions to this rule. Sometimes calculated values need to be stored, particularly if manual overrides are necessary. If, for example, you want the flexibility to adjust a discount manually rather than stick strictly to an automatic calculation, you must store that value because calculated fields do not allow direct editing. Another scenario involves performance considerations. Suppose you run frequent reports or perform lookups on aggregates, such as the total dollar value of all of a customer's orders, and your database becomes slow due to the frequency and size of these lookups. In that case, storing the result can significantly improve performance, especially in larger databases or when the backend is simply an Access file and not a full database server. Real-world situations often call for balancing theoretical purity with practical needs. For most values, especially those calculated from fields within the same record, dynamic calculation is preferred for data integrity. If the formula changes, the calculated value updates automatically—no need to manually correct existing data. On the flip side, if the calculation involves heavy lookups, aggregations over many records, or requires manual adjustments, storing the value can be justified. A good example of this arises in social media applications, where the "like" count on a post changes far less often than the number of times the post is viewed. Instead of dynamically counting likes each time, the count can be stored and updated only when a like is added or removed, greatly speeding up display times. To illustrate how to implement this in Access, let's look at a real email I received. The request came from a member who needed to store a customer discount rate that is usually based on their history but sometimes overridden at her discretion. The solution here is to show two fields: one field displays the actual, editable discount stored in the table, and another calculated field shows what the discount should be based on business rules such as years as a customer. For example, suppose after two years, the customer should receive a 7 percent discount, and after one year, a 5 percent discount. The calculated field will always show what the discount should be—based on the "customer since" date—but the actual field remains editable, allowing manual override. You can even use visual cues, like conditional formatting, to highlight whenever the calculated rate and the stored rate differ, drawing attention to areas where an update might be needed. In designing the form, you add both the editable discount rate field and the calculated discount display. The calculation itself can use Access functions such as Switch to determine the appropriate discount tier. For the calculated value, this is for display only—it is not stored in the table for each customer. But you have the choice to enter it manually, or even automate transferring the suggested value to the stored field with a simple command button that copies the calculated value. For those new to programming in Access, taking input from a calculated control and copying it to an editable field is a straightforward introduction to using VBA. If you would like the program to automatically assign suggested discount values to new records that do not already have a stored discount, an On Current event on the form can be used. Conditional formatting can highlight discrepancies between the suggested and actual discounts, making management more efficient—an immediate visual indicator appears when the two do not match. By following these techniques, you get the best of both worlds—the efficiency and integrity of calculated values with the flexibility to manually override where business rules require it. In today's Extended Cut, I also cover how to implement more advanced discounting formulas based on multiple factors, such as tiered years and order totals, using a custom VBA function in a public module. I demonstrate how to batch-apply updates to all records in your database using an update query, so you do not have to wait for each record to be opened individually. If you are interested in the step-by-step details, including the advanced techniques mentioned here, you can find a complete video tutorial with thorough instructions on my website at the link below. Live long and prosper, my friends. Topic ListWhen to use stored values vs calculated values in AccessPotential pitfalls of storing calculated fields in tables Using queries for calculated fields in Access Examples of safe calculated values in queries Why calculated values make updating formulas easier Handling date-based calculated fields like years as customer Risks of multiple data entry points for stored values Exceptions when storing values may be preferable Manual override scenarios requiring stored values Performance considerations for storing versus calculating values Aggregates and reporting needs affecting storage decisions Normalization versus performance tradeoffs Social media likes example for storing frequently-viewed aggregates Case study: Manually editable customer discount rates Implementing a stored discount rate field in a customer table Configuring discount rate field formatting as percent Adding a stored discount field to a form in Access Calculating recommended discounts using the Switch function Display of calculated and actual discount rates on a form Using an unbound calculated control for suggested discounts Applying the calculated value to the stored value with a button Basic VBA to copy calculated values to table fields Setting default stored values with the OnCurrent event Conditional formatting to highlight discrepancies Balancing calculated vs stored values for efficient reporting |
||||
|
| |||
| Keywords: TechHelp Access why you should not store calculated information in tables, is it better to store, computed, calculating, calculate discount rate, calculated query fields PermaLink Stored or Calculated in Microsoft Access |
