Available Credit
By Richard Rost
3 years ago
Show a Customer's Available Credit in MS Access
In my TechHelp Free Template, which is the database that I use for most of my videos, we can track customers and orders. There is a CreditLimit field but we don't use it at all. So in this video I'll show you how to display how much credit a customer has available by adding up all of their unpaid order totals subtracting that from the credit limit to show their available credit.
Roger from Salt Lake City, Utah (a Gold Member) asks: In your database you have a credit limit field but you don't do anything with it. How can I see how much credit a customer has available before they put an order in?
Members
Members will learn how to update the available credit in real time on the customer form as the order is changed (items are added, changed, or deleted) and we will prevent the user from going over the customers available credit.
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!
Pre-Requisites
Links
Recommended Courses
More Info
- Original Forum Post
- Here's that DSum statement:
=Nz(DSum("OrderTotal","OrderTotalQ","CustomerID=" & Nz([CustomerID],0) & " AND IsPaid=FALSE"),0)
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, available credit, credit remaining, credit limit usage, unpaid order total
Intro
In this video, I will show you how to calculate and display a customer's available credit in Microsoft Access by subtracting their total unpaid orders from their credit limit. We'll cover how to create an aggregate query to sum order totals, use the DSUM and NZ functions to handle calculations and null values, and add these fields to your customer form for easy viewing. I will also demonstrate how to use conditional formatting so available credit values stand out. This tutorial is based on a question from a viewer and uses the free TechHelp database template.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to utilize that credit limit value that we have in our customer database. We'll calculate the customer's unpaid order total, subtract that from their credit limit, and show the available credit left.
Today's question comes from Roger in Salt Lake City, Utah, one of my Gold members. Roger says, and I'm paraphrasing, in your database you have a credit limit field, but you don't do anything with it. How can I see how much credit a customer has available before they put an order in? Roger actually posted this in the Access Forum on my website, and like I said, I was paraphrasing his question. The guys gave him some help - Alex and Kevin. They got a working solution for him, but I was saying to myself when they posted this, this would make a great video. So, here we go.
Before we get started, we've got some prerequisites for you. We can do this without any programming, but you're going to have to watch a couple of other videos first. If you haven't yet watched my invoicing video, go watch this. This is where I show you how to put the order entry system in and print out invoices.
You will need to know what an aggregate query is. That's where you can sum up records in a query. So go watch this. If you don't know what a field name alias is, go watch this video.
Here's a big one: D-Sum. We're going to use this function to calculate the actual amount of unpaid orders that the user has. Make sure you understand how to use D-Sum. It's a cousin of DLookup.
Just in case that customer doesn't have any orders, you might get a null value returned. Go watch the NZ function video to show you how to convert null values over to zeros. These are all free videos, by the way. They're on my website. They're on my YouTube channel. Go watch all of those first and then come on back and finish this one.
I'll wait for you. Go on. Go ahead.
Okay, here I am. My TechHelp free template - this is a free database. You can download it from my website. If you watched the invoicing video, you know that already. I strongly recommend you build the invoicing database yourself. That's the best way to learn. Don't just download my copy (you can if you want to), but the downloads are there for people who maybe built it a while back, but want to download it and see what I've done. If you want to learn this stuff, the best way to do it is to just do it yourself. Follow along with me.
We've got our customers and each customer has an order. Each order has an order total that's calculated. The line item information is in the order detail table. We have this order detail query that calculates the extended price by multiplying each item's unit price by its quantity. All of those extended prices add up down here for each order. These are all calculated values. You don't have to store those in the tables anywhere.
Now, since these are all calculated values, and I basically want to put that value here, the easiest way to go about this is to make another query that has all of our order totals in it. So, let's make an order total query.
Create - Query Design. We're going to bring in the order table and the order detail query, because that guy has the extended price in it, which we need.
So, what do we want to see in this query? Let's just bring in only the fields we really need. Bring only what you need to survive. It's my industrial strength hair dryer, and I can't live without it. Who knows that movie quote?
Anyways, we need the order ID. I want the customer ID, because I've got to be able to pick the customer. I need the is paid, because we only care about orders that are not paid. If they're paid already, then they don't count toward your credit limit. I'll also need the extended price from over here.
If I run this guy right now, that's what it looks like. Here you can see all of the line items for order one. Here are all the line items for order two. There's one line item for order three. What I want is to put these together. I want to sum these up so each order is its own line in here, its own record.
To do that, we're going to turn this into an aggregate query. Turn the add tables box off there. Go to Totals. This turns on the aggregate functions. Group by, group by, group by - that's fine. Those are all going to be the same, because they're from the same table. This guy over here, what do I want to do here? I want to sum that up.
Now when I run it, look at that. I got one item for each order. Order one - there's its order total. Order two - there's its order total. I don't want to see "Sum of extended price" in here, so let's put an alias on that right here. We'll call this OrderTotal: just like that.
I just discovered this feature, but I think it's so you can find your mouse if you have a multi-monitor set up. I just discovered that if you double tap the control key. That's kind of neat. I've been using the computer how many years, and I just figured that out.
Now if I run that, there you go. That's what I want to see there. Don't worry that it's not currency. We're going to format the text box later. Save this - control S. Save this as my OrderTotalQ.
Now for the next step, we're going to put a box on the customer form showing their total of unpaid orders. So far, these are all unpaid orders. Let's go add some stuff in here.
Let's go to the customer form. What are orders? Customer one - there's a $55 order. Let's put something else in here. This will be a $500 order, but let's say this one is paid. One more order. This one's for a phaser rifle and it's $700, and he hasn't paid it yet.
If I run that query now (my OrderTotalQ), there we go. $55, there's a $500 paid order, and there's $700 down here unpaid. You can see I have a couple of unpaid orders.
Now what I want to do is on the customer form, I want to sum up the order totals from the OrderTotalQ where the customer ID equals the current customer ID on that form, and isPaid equals false. I just kind of said in English what my DSUM function is going to be. In fact, a lot of times what I'll do when I have this query is I'll pop up my handy dandy Notepad and I'll just write the function in here:
=DSum("OrderTotal","OrderTotalQ","CustomerID=" & CustomerID & " AND IsPaid=False")
There we just wrote our DSUM function. Now we're going to set this aside for a second. I have a big monitor; I'm sliding it over to the right. It's over here. See, it's hiding there. I use two things a lot: Notepad and Paint. Believe it or not, I use Microsoft Paint (comes with Windows) just to take a screenshot of something real quick and put it over on the side.
So, I got what I need from this. Let's put that field here on our form.
Design view. I'm going to close this back up just to save some space here. Open that up like that. Let's take Credit Limit and I'm just going to slide it down just to set it apart.
Copy, paste. Change the label over here. This will be Available Credit.
Open up the properties for this box. The name of the box is going to be AvailableCredit (if I can spell today). The control source is going to be that thing we just typed in. So, I'm going to copy this and paste it in here in the Control Source.
If it helps you, you can zoom in (Shift+F2 to zoom in). Usually I do my typing in here, but paste. There we go.
=DSum("OrderTotal","OrderTotalQ","CustomerID=" & CustomerID & " AND IsPaid=False")
Sum up the order total from the OrderTotalQ where the CustomerID equals the CustomerID from this form that we're on. A space because we have to continue the string now (string concatenation). If you don't know about string concatenation, go watch my concatenation video. I'll put that in the link section down below. I should have listed it in the prerequisites, but usually if you're to the point where you're watching the invoicing video and the aggregate queries, it's something that you know about concatenation.
Now, and IsPaid equals false. Hit OK.
That goes in here. I like to make my calculated fields like this guy gray. I use the format painter. Just like that. Make sure this says currency.
Save that. Close it. Open it. There we go.
Is available credit 755? Excuse me, that's not available credit. That's unpaid order total. Let's go ahead and fix it. I'm leaving this in the video because if I make a mistake like this, chances are you will too.
Available credit comes next. This is going to be unpaid orders - Unpaid Order Total.
Available credit is next.
We're going to copy this guy. Copy, paste one more time. This is the easy one. Available credit.
Let's name this guy AvailableCredit. This one is simply what? =CreditLimit - UnpaidOrderTotal. When it pops up like that, just hit tab and it'll fill it in.
I'll zoom in so you can see it. There you go.
Save it. Close the form. Save it. Close it. Open it. There you go. There's your available credit.
If you want, throw the conditional formatting on that maybe. Design view. We can open this guy up like this. Go to Format, Conditional Formatting, New Rule. We'll say if the field value is greater than (whatever number you want), I'll say greater than zero. We'll make it green. OK. New rule. If it's less than zero, we'll make it red. That way you're like, you're over your credit limit, buddy. If it's equal to zero, you'll still get the same gray.
Save it. Close it. Open it back up again. Look at that - he's green, the available credit. Now if I go to the next guy, he's in the red because he's only got a $4,000 credit limit, but he's got an order in the system already for $37,000.
Now in the Extended Cut, I'm going to show you how to prevent that. In the Extended Cut for the members, if they try to put something in that puts you over your credit limit, like 200 pairs of Captain Picard underoos, it's going to yell at you and say you're over the credit limit. We'll talk about that in more than a few minutes. For now, this is just giving us a visual.
A couple of things. First thing is, if you go to a new record, the customer ID is null. There isn't one there yet. We don't want the user seeing errors over here, so let's throw an NZ function around that CustomerID so that in case the CustomerID is null, it turns it into a zero.
Design view. Go into our function here. Click there, Shift+F2 to zoom in. I'm going to put an NZ around this guy. NZ( [CustomerID], 0 ). So if CustomerID is null, the NZ function will turn it into a zero and it won't cause an error in the DSUM function.
OK. Save it. Close it. Open it. Now if I go to a new record, I don't get anything in there. It's just blank. But you might not want to see blank. You might want to actually see a zero there.
If you go to another customer who doesn't have an order (if I go to customer three who doesn't have an order), I see a null there again. Why is that? Well, because the DSUM function is evaluating customer three's orders. They don't have any, so DSUM is returning null. Again, we can put a second NZ function there, wrap it around the whole thing, and that will return a zero as well.
Back to design view. Back in here, where we just were. So we've taken care of if CustomerID is null. But what about if the DSUM doesn't find any orders? It'll return a null value. We don't want that. So we'll put NZ out here - NZ([whole thing], 0). Look at that.
Now if they don't have any orders, you'll get a zero there. Save it. Close it. Open it. Go to the end, and there's your zero. She has $4,339 available credit. If you go to a new record, it should still also - yep, there's your zero. Sweet.
So that's how you can see visually what the available credit is. When you go to place an order, I like to have it so that in my final version of my database, I'll have that and I'll have this over here so you can see them side by side.
Let's talk about that Extended Cut. Two things we're going to do: we're going to prevent the user from going over the credit limit, and we're going to update that available credit each time the user changes an item on the order so you can see it in real time.
Here's the Extended Cut database. If I come in here and I try to put in, you know, 20 pairs of those, it allows that because that's under the credit limit. Notice how it updated over here.
If I come in here and I put in Worf's tooth sharpener and that's $600 - or, yeah, one of them $600 (I can't type today). This will put the customer over the credit limit. I'm going to set the quantity as zero. There's a million ways you can handle this. Yes, some are more elegant than this. Easily, just for now to keep it easy. I just set the quantity as zero, and then the user can come back and say, well, sorry sir, that's too expensive. Or I'll give you a discount. I'll give you one at $530. How's that sound? There we go. Okay, and I got 10 bucks left.
That's what we're going to cover in the Extended Cut. Silver members and up get access to all of my Extended Cut videos on this. There are hundreds of them by now. I've been doing this for years. Gold members can download these databases that I build and the TechHelp videos.
If you really want to learn this order entry system stuff with credit limits and sales tax and quantities and inventory and all that, I have lots and lots and lots of lessons on building an order entry system. I start for real in my Access Expert Level 8 class because by Level 8, you've learned enough between the Beginner Series and the first seven Expert classes. You've learned about forms, designing queries, and things like that, so we can now start building the order entry system.
That's where you want to start if you want to learn how to do that after you finish the other ones, of course. It goes all the way up through the Expert Series and into the Developer classes, depending on how far you want to go. You need VBA to do proper inventory control, and I teach you all that. I'll hold your hand. I'll walk you through the whole thing. But that's where you want to start.
But that, my friends, is your TechHelp video for today. I hope you learned something. Live long and prosper, and I'll see you next time.
Bye. Bye. Bye. Bye. Bye. Bye.
Quiz
Q1. What is the main purpose of utilizing the credit limit value in the customer database as shown in the video?
A. To calculate and track how much credit is left for each customer based on unpaid orders
B. To record the customer's total order price for tax purposes
C. To prevent customers from placing any orders
D. To display customer order history for reporting
Q2. Which function is primarily used to calculate the sum of unpaid orders for a customer?
A. DLookup
B. DSum
C. Avg
D. Min
Q3. Why is the IsPaid field important in the query used to compute the customer's unpaid order total?
A. It helps to determine the customer's address
B. It identifies only those orders that have been paid
C. It ensures only unpaid orders are counted towards the credit usage
D. It sorts orders in descending date order
Q4. In the query design, what is the purpose of creating an aggregate query?
A. To separate each line item on its own line
B. To associate orders with multiple customers
C. To sum extended prices for each order, combining line items into a single order total
D. To filter orders based on the product category
Q5. What is the significance of using a field name alias such as OrderTotal in the aggregate query?
A. It allows duplicate field names in the query
B. It enables formatting of currency fields
C. It provides a descriptive name for calculated fields for easier reference
D. It hides fields from the final query output
Q6. What formula is used to calculate the available credit on the customer form?
A. =CreditLimit + UnpaidOrderTotal
B. =OrderTotal - CreditLimit
C. =UnpaidOrderTotal - CreditLimit
D. =CreditLimit - UnpaidOrderTotal
Q7. When referencing the current customer in the DSum function on the customer form, how is the syntax typically structured?
A. "CustomerID=" & [Form]![CustomerID]
B. "CustomerID" = CustomerID
C. "CustomerID=" & CustomerID
D. CustomerID = "CustomerID"
Q8. Why is the NZ function used in the calculation for available credit and unpaid order total?
A. To prompt the user for input values
B. To convert null values into zeros and prevent errors in calculations
C. To average the unpaid order totals
D. To randomly select a customer for the calculation
Q9. What potential problem is solved by wrapping the whole DSum expression in an NZ function in addition to using NZ on CustomerID?
A. It eliminates duplicates in the query results
B. It prevents errors when DSum returns a null due to no unpaid orders for a customer
C. It prevents database locks during queries
D. It disables conditional formatting
Q10. What is the purpose of applying conditional formatting on the available credit textbox in the customer form?
A. To disable editing of the field
B. To sort customers by credit score
C. To visually indicate whether the customer is over, under, or at their credit limit
D. To protect the form from accidental deletion
Q11. According to the video, what is the best way to learn and understand the invoicing and credit limit features discussed?
A. Just watch tutorials without practicing
B. Download databases and memorize the code
C. Build the invoicing database yourself and follow along step by step
D. Hire a professional developer to do it for you
Q12. In the Extended Cut, which additional feature is discussed for handling credit limits in the ordering process?
A. Automatically increasing the credit limit
B. Preventing users from placing orders that exceed the available credit
C. Sending marketing emails to customers over their limit
D. Deleting unpaid orders older than 30 days
Answers: 1-A; 2-B; 3-C; 4-C; 5-C; 6-D; 7-C; 8-B; 9-B; 10-C; 11-C; 12-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.
Summary
Today's TechHelp tutorial from Access Learning Zone covers how to use the credit limit field in a Microsoft Access customer database. I will explain how to calculate each customer's unpaid order total, subtract it from their overall credit limit, and display the available credit remaining.
The inspiration for this tutorial came from a student who asked how he could determine a customer's available credit before placing a new order. While others in the forum had already provided some guidance, I thought this would be a great topic for a full lesson.
Before you continue, there are some essential prerequisites. You will not need to do any programming here, but you should have already watched a few foundational videos. First, you should be familiar with the invoicing system, where I show how to set up an order entry process and print invoices. You should also know how aggregate queries work, which allow you to sum up records in a query. If you are new to the concept of field name aliases, watch my video on that as well.
A very important function for this lesson is DSUM, which is what we will use to calculate the total value of unpaid orders for each customer. If you do not already understand DSUM or its cousin DLookup, be sure to watch those videos first. Also, if a customer has no orders, their unpaid order total could return a null value. Learning about the NZ function will help you convert those null values into zeros for easier handling. All of these supporting videos are free and available on my website and YouTube channel.
If you have not done so already, I strongly recommend you build the invoicing database from scratch as you follow along. While it is possible to download my free template database, you will learn much more by creating it yourself.
In our scenario, each customer has one or more orders, and the order total is a calculated value. Line item details are stored in an order detail table, where the extended price for each item is calculated by multiplying the unit price by quantity. All the extended prices for a given order are summed to give you the total for that order. These calculated values are not stored in the table but derived from queries.
To achieve our goal, we first need to create a query that summarizes the total for each order. Start by making a new query that contains the order table and the order detail query (which already calculates the extended price). You only need to include essential fields: the order ID, customer ID, an IsPaid flag (so we know whether to include the order in the credit calculation), and the extended price field.
At first glance, this query will show each line item for each order. To summarize these line items into a single total for each order, turn the query into an aggregate query. Use the Totals option and select Sum for the extended price field. Now, instead of multiple line items per order, you will have only one record per order, each showing the total value.
To make the result more readable, assign a field alias such as OrderTotal to the summed value. Save this query as OrderTotalQ.
Next, we need to display a customer's total of unpaid orders directly on their customer form. The idea is to use DSUM to sum up all order totals from OrderTotalQ for the current customer where orders have not been paid. In other words, you want to total the unpaid orders for the customer whose record is open in the form. I like to draft these kinds of functions out in Notepad for accuracy.
The DSUM formula for unmet payments involves summing the OrderTotal field from the OrderTotalQ query where CustomerID matches the current customer and IsPaid is False.
On the customer form, I create a new unbound text box labeled Unpaid Order Total. The control source for this box is the DSUM function I drafted. Make sure to format it as currency for clarity.
Reviewing my work, I realized that this box was displaying the unpaid order total, not the available credit as I intended. To correct this, I created another text box labeled Available Credit. This one simply subtracts the unpaid order total from the customer's credit limit. So, in the control source, I used the expression CreditLimit minus UnpaidOrderTotal. Again, I made sure the format was currency.
For additional clarity, I like to use conditional formatting. For example, if available credit is above zero, highlight it green. If it falls below zero, make it red to visually indicate the customer is over their credit limit. If it is exactly zero, keep it neutral.
There are a few minor adjustments needed to account for edge cases. If you move to a new record and the CustomerID is null (because you have not entered any customer data yet), you do not want to display errors. Wrapping the CustomerID in the NZ function will convert null to zero and avoid these errors. In DSUM, if there are no unpaid orders for a customer, the function returns a null value. You can nest the entire DSUM function inside another NZ to ensure it always returns zero instead of null. This helps keep your form looking clean and consistent.
Now when you view a customer on the form, you can see their available credit as calculated from their limit minus unpaid orders. Whenever you go to a new customer or add a new one, these fields will display either the correct available credit or zero.
In the Extended Cut for this lesson, I walk through how to actually prevent users from exceeding the credit limit when placing new orders. I also show how to ensure the available credit updates automatically whenever the order details change, so users always see real-time information.
In that version, if an order would put a customer over their limit, the system reacts, such as by adjusting quantities or displaying an appropriate message. There are many ways to handle this situation but I demonstrate a straightforward approach for clarity. In addition, I cover how the available credit box updates instantly as you change order items.
If you want to gain a solid understanding of building an order entry system in Access—from credit limits to inventory control and everything in between—I strongly recommend starting with my Access Expert Level 8 class and progressing through subsequent lessons. I provide step-by-step guidance through the entire process and cover advanced techniques as well.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.
Topic List
Calculating unpaid order totals for customers
Creating an aggregate query to sum order details
Using the DSum function to total unpaid orders
Displaying unpaid order total on the customer form
Subtracting unpaid orders from credit limit
Calculating available credit for each customer
Using field name aliases in queries
Formatting calculated fields as currency
Applying conditional formatting to highlight available credit
Handling null values with the NZ function in forms
Preventing errors when CustomerID is null
Ensuring zero displays when customer has no orders
|
