Totals Not Saved
By Richard Rost
2 years ago
Issues with Storing Calculated Values in Access Tables
In this Microsoft Access tutorial, we'll explore why storing calculated values, like order totals, in your database tables can cause issues. I will show you how to effectively handle calculated fields without saving them, ensuring data accuracy and efficiency across forms, reports, and queries.
Eric from Burbank, California (a Platinum Member) asks: I'm working on reports for my order entry system, and I noticed the order total isn't saved in the OrderT table. How can I save it there so I can use it in my reports and other queries?
Members
In the extended cut, I will show you how to store calculated order totals directly in the order table. We'll discuss how to manage instances where line items are modified, added, or deleted, including creating a custom delete button. This approach is useful for performance optimization and integration with external systems.
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!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, calculated fields, storing values in tables, order total calculation, recalculation errors, query-based calculations, aggregate queries, DSum function, extending queries, performance optimization, audit trails, external system integration, asynchronous processing, complex business logic
Intro
In this video, we'll talk about handling calculated values such as order totals that are not saved directly in your Microsoft Access tables. We'll discuss why it's usually better to calculate these totals dynamically, look at the risks of storing them, and show how to use features like form footer calculations, the DSum function, and aggregate queries to display and summarize data whenever you need it. We'll also cover important considerations like performance, audit history, and cases where saving a calculated value might make sense.
Transcript
Today we're going to talk about a question that I get asked all the time. People say, hey, I got this form. I got some calculated values on the form like an order total. But that value is not being saved in my table anywhere. How can I do stuff with it? We're going to talk about that in today's video.
Today's question comes from Eric in Burbank, California, one of my platinum members. Eric says, I'm working on reports from my order entry system and I notice the order total isn't saved in the order T table. How can I save it there so I can use it in my reports and other queries? Well, that's a very good question, Eric. And the short answer is, you don't have to. Most calculated fields are best left calculated for a reason.
There are lots of reasons why we'll talk about some of them in a few minutes. But my general rule of thumb is most of the time, I'd say 99% of the time, you're best to leave it as a calculated value because it can change. If you change an order, if you change any of the items on that order, or something simple like a discount rate or whatever, then you have to make sure you update that total. And if that total is saved in the order table and you forget to do that recalculation, then your data is wrong.
So first, let's take a look at exactly what Eric is talking about. And then I'll show you how you can get those calculated values without having to store that total in the table itself.
So first up, a little prerequisite. If you're not familiar with my invoicing video, go watch this first. It's free. It's on my website. It's on my YouTube channel. And I'll show you how I walk through building the order entry form, the order details down here, and a printable invoice. So go watch this first so you understand what we're working with.
All right, so here I am in the TechHelp free template. This is basically the database that we build in the invoicing system. We've got a customer form, and customers can have orders. Now in here, we have the order information up top. And then in here is the order subform with the detail items. Each of these detail items has a quantity and a unit price, two shield generators at $350. This here is a calculated value.
It's calculated in the query in the order detail queue, which is right there. And you can see there's an extended price over there. This is for each line item. That's a calculated value. If you take a look at how it's derived, it's right there. We did this in the invoicing video. Extended price is the quantity times the unit price. There's no need to save that in the table because we can recreate it.
It's better in this particular case if we leave it as a calculated value. Because if one of these things changes over here, then it's automatically updated. Right? Likewise, down here at the bottom, this order total is also a calculated value. And we do that with a form footer calculation right there. I open up the properties for that guy. You can see it's right here. It's the sum of the extended price. In other words, go up here and find all of the extended price fields and then add them up. And put it down here at the bottom.
And that's another calculated value. So this order total isn't stored in the table because you can calculate it. Right? And that's generally better because again, if you're going to store this in the table, then you've got to be able to intercept anywhere along the line that the user can change that value or any of its dependencies. Right? The quantity, the unit price. Other forms you might have it on. Or, you know, having forbidden, you're actually letting them modify stuff in tables and queries, which you should never do. Right? Your end users should always be able to only work with forms and reports. All right?
And likewise, we can get this value into our reports. Here's our total right there. And that's a calculated value in that report. Right? We come right over here, and we can see. Oh, yeah. I'm here. There it is, right there. Right? It's the sum of the extended price. Same thing. Yeah, this is just a footer section. So you don't need to store these data in the table. Because you can get them through a lot of different means too.
Let's say, for example, that you want to display the customer's total of all of his paid orders right on the customer record. I do this sometimes with some of my databases. So you can see what a customer's value to the company is right at a quick glance. Well, for that, you can use DSum. And the best part about knowing these functions is there's no programming involved. You just got to know the basic functions. Right? Here's the DSum function. If you don't know how to use this guy, go watch this video. DSum allows you to add up the records in a table or query.
Now, we've got our order detail query that adds up the line items. We also have another query here that we built called the order summary query. And this takes each order. And it adds up all the line items on that order into this order total. And this thing is called an aggregate query. An aggregate query, which is this totals thing here. I have a whole separate video on this too. Right? Aggregate queries. It's basically taking a bunch of records and grouping them together on something like customer ID or even order ID. And then you add up or count or max or min or any of those functions.
So with this aggregate query, we can sum up all of the order totals. And if I run this now, you can see here order one. There's the total of all the details on that order, order two, order three, order four, and so on. And if I want to get a total for all of the orders for a specific customer, then I just make another aggregate query or I can use a DSum, like I said before, and just add these up. Right? And for my DSum, I like pulling up Notepad once in a while. Sometimes it's easy if you can see this stuff on the screen.
This would be DSum. We're going to sum up the order total from the order summary queue. And then what's our criteria? Well, it's got to be the customer ID equals whatever the customer ID is on. Let's say the customer form. So we'll do it like this. Right? And you also want to check to make sure it's paid. So maybe the first time one here will go is paid equals true. And I got to turn off the spell check. We'll turn that off. I don't like that in Notepad.
All right, there we go. That's right over here, by the way. The new version of Notepad. It's got a lot of cool features, but I just want it as a plain simple text editor. I don't like all these fancy features in my Notepad.
Okay, so I'm going to look up. I'm going to sum up all of the order totals from this order summary queue where the orders are paid and the customer ID equals the customer ID on the customer form. So I'll copy that. I'll close this. Let's come over here. Let's just repurpose this credit limit field. We'll call this sales total. Right? And instead of credit limit, what we'll do is we'll call this guy sales total. And we'll put our DSum right in there. Same.
Okay, zoom in so you can see it better. There it is. Right? Okay. Now close it. I like to make these gray so that the user knows that that's not something they can edit. Right? Like that, maybe. Close it. Close it. Open it. And there you go. There's that person's sales total. That's an aggregate of all of their orders that are paid, which I think they only have one. Someone's beaming in. All right, 1220. They have another order, but it's not paid. So that's fine.
All right. That's how you can use an aggregate query and DSum to get that data anywhere you want. Let's say you want to do a query with all of your customers in their order totals. Okay. Well, we already have this guy this order summary. We just got to make another query based on this that groups by the customer ID. Right? Let's do that. Create query design. I'm going to bring in my customer table first. Where's customer T? I'll bring in that order summary queue. All right. A relationship is made.
Now, if you only want to see customers that have orders, then leave this join alone like it is. But if you want to see everybody whether they have orders or not, you can change the join type. Make that an outer join. I have a whole separate video on that. We'll just leave it as it is for now. All right. We can close this. Let's say I want to see the customer ID, the first name, the last name, and is paid has to be true. And the order total. I want to sum that up. Let's see what I got so far though.
Okay. That looks good. Let me mark that second order for me paid. Let's go over here and mark that one paid. So I got two records showing up orders. And we'll go to this one and I'll mark that paid. Okay. But now if I run this there, you can see I got two orders one there and one there. Okay. All right. Now we're going to aggregate this stuff together.
Okay. So go to query design, turn on totals. And now over here, we're going to sum based on this guy based on the order total. And now when I run it, I just see one entry for me, and it adds them up. Right. A customer order total. Okay. So the point I'm trying to make here is that you can derive those calculated values anyway you want. If you need it in one spot, you can use a DSum function. If you need a list of stuff, you can use an aggregate query. There's no reason to have to store these numbers in the table.
Well, I don't want to say no reason because we're going to talk about that in just a minute. And while I'm talking about it, by the way, these are calculated query fields. Okay. This is a calculated query field, which is okay or calculated form footer.
We do not use the calculated field data type in a table. Right. There's a thing in here called calculated field, which no, that's bad. I got a whole separate video coming out soon on why these are very bad. Do not use these or lookup wizards or attachments. This stuff in here you should or hyperlinks or only objects. Well, pretty much everything from here down is bad. Don't use it. It's on my evil list. Right. I got a whole page on evil access stuff, and you'll see all these things here. I'll put a link to this down below too. A video is coming on this soon too.
Now with all of that being said, the point I'm trying to drive across is for most databases, especially if you're a beginner, intermediate level access developer. Don't worry about storing these values in the table because you can calculate them on the fly. Pretty much anytime you want.
Okay. Now for the advanced users out there for the advanced developer students, there may come a time when you might want to store a value like that directly in your table. And here's a list of reasons when you might want to do that. The biggest one is performance optimizations for very large sets of data. If you're talking millions of records, right. Let's say you're doing your year-end reporting, you've got all the data and a split database stored up on the server. You've got to constantly pull down all these millions of records. It might make sense to store the order totals in the order table because it's going to pull down a whole lot less information.
It's rare, but that's one of the instances where it's a good idea to do that. Historical snapshots. Even if the underlying data changes. Some companies have weird financial rules. Right. They want to be able to know what the data was at the time the order was placed. Even if it changes, usually in which case you make a copy of the data, but it happens.
Audit trail and traceability payroll records at the time the records were put in. Right. Integration with external systems is a big one. I've had clients before that had other software that they had to be able to work with their access database. And that software couldn't calculate the order totals. So you had to store that data in a table so the other software could read it properly. So that does definitely happen sometimes.
Complex business logic really weird loyalty discounts promo codes tiered pricing. Sometimes all that does make sense to store the value in the table instead of having to put all these different calculations together on the fly all the time. Okay. Regular or legal requirements. And enough said about that there's all kinds of weird laws and rules depending on your business you may need to store it instead of calculating it. That's really all I can go into as far as that's concerned. You know who you are.
And then sometimes asynchronous processing sometimes you can't wait for that calculation. Sometimes you got to save the data immediately and then transfer it somewhere else. Transaction service for example so there are some instances where it makes sense to do the calculation at the moment that the data is entered deleted or edited and save that information directly in the table.
But if you do that again you have to make sure that you can track all of the places where that information can be changed. So what if you are one of the people that falls into one of these categories? Well I will show you how to do this. In the extended cut for the members.
We're going to do it where we're going to take the order table. We're going to save that order total in the order table itself. But we have to be able to handle every instance of where a line item is modified added or deleted. Deleted is a pain in the behind or to make our own custom delete button and I'll explain all of this in the extended cut for the members.
So if you want to save the data, you can download all of the data and get access to all of my extended cut videos, not just this one all of them. Gold members can download these databases you get access to the code vault and all kinds of cool stuff. And of course everybody gets some free lessons all my members are well taken care of.
But there you go that's why you don't generally want to store this information in your table if you can calculate it, calculate it unless you've got a specific reason why you don't want to. That's going to do it for your TechHelp video for today. Hope you learned something live long and prosper my friends. I'll see you next time.
Sammy is a certified Microsoft Office Specialist, and he not only offers Access application development, but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your Access project, Sammy is your guide. Check him out at ShamaConsultancy.com.
TOPICS:
- Saving calculated values in a table
- Calculated fields vs stored fields
- Calculating order totals dynamically
- Using form footer calculations
- Using DSum function for aggregates
- Creating aggregate queries
- Query design for customer order totals
- Reasons not to store calculated fields
- Performance optimization considerations
- Historical snapshots and audit trails
- Integration with external systems
- Handling complex business logic
- Custom delete button for data integrity
COMMERCIAL:
In today's video, we're addressing a common question: how to deal with calculated values like order totals that aren't saved in your database tables. You'll learn why it's often best to keep these as calculated values to avoid errors when order details change. We'll explore functions like DSum to pull these numbers into your forms and reports without storing them. For those needing to save these values due to specific cases like performance optimization or integration needs, stick around for the extended cut where I'll guide you on how to manage those. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. Why is it generally recommended not to store calculated values in a table?
A. Calculated values might cause data corruption
B. Calculated values cannot be displayed in reports
C. Calculated values, if stored, need constant updates if any dependencies change
D. Calculated values occupy unnecessary storage space
Q2. What is the primary advantage of keeping values as calculated rather than stored in a database table?
A. It simplifies the data retrieval process
B. It reduces the storage space required for the database
C. It ensures accuracy by updating automatically when dependencies change
D. It allows users to modify data directly in the query
Q3. In which situation might storing calculated values in a table be beneficial?
A. When there are only a few records
B. When calculated values need to be accessed by external systems
C. When working with simple data types
D. When creating test databases
Q4. What function is used to sum up records in a table or query without programming?
A. SUM()
B. SUMIF()
C. SUMPRODUCT()
D. DSum()
Q5. What is the process of combining multiple records together and performing calculations such as sum or average called?
A. Data mirroring
B. Aggregate querying
C. Data mining
D. Schema designing
Q6. According to the video, what should end users be encouraged to work with instead of tables and queries?
A. Forms and reports
B. Direct database access
C. VBA scripts
D. SQL statements
Q7. What is a potential drawback of the calculated field data type mentioned in the video?
A. It enhances performance too much
B. It simplifies database schema excessively
C. It is on the list of "evil" access features to avoid
D. It provides too much flexibility in calculations
Q8. What is one of the situations mentioned where you must be diligent if you choose to store calculated values in a table?
A. When updating software regularly
B. When changing database administrators
C. When tracking all places where information can be changed
D. When altering network configurations
Q9. What are some criteria that might require an advanced developer to store a calculated value in a table, as mentioned in the video?
A. Regular maintenance updates
B. Auditing and compliance requirements
C. Simple tracking of inventory
D. Multiple user access issues
Q10. What tool is recommended to sum up calculated values efficiently for individual customers on a form?
A. DSum with criteria for customer ID
B. SQL SUM command
C. Direct table editing
D. VBA custom function
Answers: 1-C; 2-C; 3-B; 4-D; 5-B; 6-A; 7-C; 8-C; 9-B; 10-A.
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.
Summary
Today's TechHelp tutorial from Access Learning Zone addresses a common question that I often receive. People often ask me why the calculated values on their forms, such as an order total, are not being stored in the table, and how they can work with these values.
A specific question from one of my students highlights this issue. He noticed that the order total in his order entry system isn't being saved in the table labeled 'OrderT.' He asked how this could be rectified so he could use the order total in his reports and other queries. This is a very relevant question, and the straightforward answer is that usually, you don't need to store these calculated fields in the table. In most cases, calculated values should remain as they are because they can change when any part of the order changes, such as order details or discounts. If these values are stored and you forget to update them, your data can become inaccurate.
Let's discuss the concept more thoroughly. When there is a calculated value, the best practice is to leave it as calculated because changes to any part of the data will reflect in the calculated result automatically. For example, if any item or relevant detail changes, the system will automatically update the calculated total.
For instance, let's use an example where we have an order entry form and subtables for details. Here, each item can have a quantity and unit price, and the total is derived by multiplying these values. This calculation happens in a query, not the table, ensuring automatic updates if any input changes. The same principle applies to computing the total order value, which can be calculated on the form using a footer section summing individual line items.
In terms of generating reports, calculated fields can be included directly in these reports without needing to save them in the table. The footer similarly sums the extended prices for display. This method avoids the need to monitor and update stored data, reducing the risk of errors.
If you wish to display cumulative data like the total for a customer's paid orders on their record, you can use functions like DSum to calculate this dynamically without code. Simply add a field indicating what you want calculated and from where. This approach is advantageous because you can design aggregate queries that group and sum data based on specific parameters such as customer ID, offering flexibility and efficiency.
There are some cases, especially for advanced developers, where you might choose to store calculated data directly in the table, such as for performance optimization with very large datasets, when historical snapshots are needed, for audit and traceability purposes, for integration with external systems, or when dealing with complex business logic and legal requirements.
If you fall into these specific categories, there's a way to handle this correctly, albeit with some complexity. It involves managing each possible data change scenario, such as adding, modifying, and deleting records, ensuring your data remains accurate.
In the extended tutorial for members, I'll be demonstrating how to save the order total directly in the table and handle various scenarios that might affect the stored data. Remember, doing this requires careful handling to ensure all data changes are captured accurately.
In conclusion, for most situations, especially for beginners and intermediate users, it's best to maintain calculated fields as such, recalculating data on demand instead of storing it. This minimizes errors and maintains data integrity.
For a more detailed tutorial with step-by-step guidance on everything discussed here, visit my website.
Live long and prosper, my friends.
Topic List
- Saving calculated values in a table
- Calculated fields vs stored fields
- Calculating order totals dynamically
- Using form footer calculations
- Using DSum function for aggregates
- Creating aggregate queries
- Query design for customer order totals
- Reasons not to store calculated fields
- Performance optimization considerations
- Historical snapshots and audit trails
- Integration with external systems
- Handling complex business logic
- Custom delete button for data integrity
|
