|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
DCount By Richard Rost 
5 years ago
Count Records in a Table or Query with Criteria In this video, I will show you how to count up values from a table or query. We will count up the total number of orders that a customer has and display them on the customer form. Roberto from Silver City, New Mexico (a Gold Member) asks: I'd like to be able to quickly see the total number of orders that a customer has when I open up their record in the customer form. Right now, I have to open up the order form and look at the number on the bottom. This would save me a lot of time. MembersMembers will see how to use DCount to limit the number of records in a related table (such as in a subform). This would be handy to, for example, set a maximum number of students per class, or a minimum number of items per order. We'll also see how to refresh a parent form when a child form is closed, and put that order count in the button caption. We'll also discuss why you should not use D-functions in queries (use aggregate queries instead).
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
Keywordsmicrosoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, dcount, count records, order count, total number of records, limit number of items, maximum, refresh parent form, button caption, aggregate query
IntroIn this video, I will show you how to use the DCount function in Microsoft Access to count the number of related records, such as the total orders for each customer, and display that information directly on your customer form. We'll walk through adding a text box, setting up DCount with proper criteria, and using NZ to handle cases with no records, making it easy to see order totals at a glance.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I am going to show you how to count up values from a table or query. We will count up the total number of orders that a customer has and display it on the customer form. That will make it easier for you to see how many orders that customer has just by glancing at the customer form. We are going to use a very powerful function called DCount. Today's question comes from Roberto in Silver City, New Mexico, a Gold member. Which is kind of funny because he is from Silver City, but he is a Gold member. Roberto says, I would like to be able to quickly see the number of orders, the total number of orders the customer has when I open up the record in the customer form. Right now I have to open up the order form and look at the number on the bottom. This would save me some time. Of course, Roberto, there is a way to do this. You can do just about anything in Access. We are going to use a nice, simple little function called DCount to count the number of orders and display it on the customer form. Now, before we get into today's lesson, I have some prerequisites for you. Go watch these other videos. Concatenation - that is putting two strings together, DLookup, which is the father of all the D functions. D stands for domain. There is DLookup, DMax, DMin, DCount - we are going to learn today. They are all related. But I want you to go watch DLookup at least first. I will give you the background on how DLookup works. I am not going to go through all that again today. All right, and then the NZ function, converting a null value to a zero. Go watch all four of these, pause it right now, and then come back. Here I am in the TechHelp free template. If you watch the other videos, you know exactly what this is and where to get it. What Roberto is saying is every time he opens up a customer record, he has to click on Orders, and then wait for this to load, and then you can see there are two orders for this customer. If you want to see the next person, he has to click on it again. That one only has one order and so on. So what he wants is a nice, quick way to see how many orders you have down here. So let's right click, design view. I am just going to add a text box down here, so go to Design, grab a text box, drop it down here. We will call this Count of Orders. Let's make it black so everyone can see it. Now, let's open up the properties for this guy. It is Text30. I do not like Text30, so I am going to change this to Count of Orders. What is the control source? I am going to zoom in, Shift+F2. It is going to zoom me into the Zoom box. It is going to be =DCount. Now, instead of looking for a single field, we are just going to count up the records, however many records there are, all of the fields. It does not matter. I am just going to use the asterisk. You can count a specific field if you want to, but the asterisk just says count up all the records. What table am I counting the records in? OrderT. And what is my criteria? Well, the CustomerID in the Order table has to be equal to the CustomerID on the current form. If you want to avoid an error message if there are no orders, you put this inside of NZ. That is why we use the NZ wrapper, comma zero. We will see zero instead of an error. That will put that back here in the control source. Close this. Let's maybe put it over here. Make it look pretty like this. Save it. Close it. Open it back up. There you go, Count of Orders - 2. Go to the next customer. There is 1. There is a zero. I do not think anybody else has any orders. You can use DCount in your forms. You can use it in reports. You can use it in queries if you want to. It is very versatile, just like DLookup and DMax. One thing you should be aware of, though, is if you go in here and add an order, let's say I add a new one, put some stuff in here, put a bogus couple products in here, close this. This does not refresh unless you either manually refresh it with Refresh or you hit F5, or you leave the record and come back to it. Can you have this update automatically when you close the order form? Yes, you can. I will cover that in the Extended Cut for the members. In the Extended Cut, 20 minutes long, I will show you how to put that order total right there on the button. Instead of having a separate text box down here, it will just say 5 orders, 3 orders, whatever. We will refresh that count automatically if you add or delete an order when you come back here. That number will be automatically updated. We will talk about why you should avoid these D functions, DLookup, DCount, and so on in queries. Just use them on forms. I will talk about why. I will show you how to use the DCount function to set a maximum number of records that you can have in a related table. For example, let's say you are doing classes with students. If you want to set it so that a class can only have a maximum number of, let's say, five students in a class, you can do that either in a subform or in a related form. So if you open up the student enrollment form from the class form and you try to add a sixth student, it will yell at you. That is all covered in the Extended Cut. Silver members and up get access to all of my Extended Cut videos. If you really want to learn about these kinds of things, my Access Expert 29 class is two hours long. I cover all of the standard aggregate functions. You can use these in form footers, report footers, write some Average, Count, Max, Min. The domain aggregate functions, all of them, DLookup, Sum, Count, Average, Max, Min, all of them. I do a couple of things like calculating daily sales, creating a custom counter, display running sums. That is a good one. People always want to see that - how to do a running sum. It is easy in reports. It is not so easy in a form. I make a replacement for the Excel VLOOKUP function, which is also very popular. That is in Access Expert Level 29. You can find that on my website at the link right there. I will put it in the link section down below. You can click on it. How do you become a member? Click the Join button below the video. After you click the Join button, you will 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 build 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. Do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more. If you liked 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 will 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 would 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 1 course, check it out now. It covers all the basics of building databases with Access. It is over three hours long; you can find it on my website or on my YouTube channel. And if you like Level 1, Level 2 is just one dollar. And it is 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 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is the main purpose of using the DCount function in the context of this video?A. To count the number of orders a customer has and display it on the customer form B. To automatically place customer details in the order form C. To delete duplicate records from the order table D. To merge customer and order tables Q2. The DCount function belongs to a group of functions in Access known as: A. Summary Functions B. Domain Aggregate Functions C. Data Type Functions D. Table Relationship Functions Q3. What does the asterisk (*) represent when used as the first argument in the DCount function? A. It counts non-null values in a single field B. It counts all the fields in the table C. It counts all the records, regardless of field D. It excludes records with null values Q4. If there are no orders found for a customer, what function can you use to convert a Null result to zero? A. ZeroValue B. NoNull C. NullToZero D. NZ Q5. Why might you want to wrap a DCount function inside NZ when displaying the count on a form? A. To increase database speed B. To prevent error messages and display zero instead of Null C. To automatically refresh the form on every change D. To restrict access to certain users Q6. According to the video, where can you use the DCount function in Microsoft Access? A. Only in tables B. In forms, reports, and queries C. Only in macros D. Only in VBA modules Q7. What is a limitation of the DCount value displayed on the customer form as mentioned in the video? A. It cannot count more than ten orders B. It does not update automatically when new orders are added C. It only works with primary keys D. It formats numbers as text strings only Q8. What shortcut key can you use to refresh the display in Access to show updated information like a new order count? A. F1 B. Ctrl+S C. F5 D. Alt+Tab Q9. Which of the following is NOT a domain aggregate function mentioned in the video? A. DLookup B. DMax C. DMin D. DSyntax Q10. Why should you generally avoid using D functions like DCount in queries? A. They are not supported in queries B. They can be slow and inefficient in queries C. They change table structures D. They only work with single-user databases Q11. In the context of enforcing a maximum number of related records, such as students in a class, how can the DCount function be used? A. To reorder database records B. To ensure a class cannot exceed a set number of enrollments C. To sort students alphabetically D. To update student names automatically Q12. What is a suggested prerequisite video to watch before this lesson? A. Aggregate Queries B. Creating Macros C. DLookup D. Table Relationships Q13. What does clicking the "Join" button on Richard Rost's channel do? A. Subscribes you to the mailing list B. Gives access to free courses only C. Shows membership level options and benefits D. Signs you up for daily video notifications Q14. Which of the following is a benefit available to Gold members? A. Access to full beginner and expert courses for free B. Downloading all sample databases and access to the Code Vault C. Free one-on-one training sessions D. Unlimited email support Q15. What do you need to do if you want the order count on the customer form to refresh automatically when you add or delete an order? A. Close and reopen the database entirely B. Use the Refresh button or F5, or leave and re-enter the record C. Compact and repair the database D. Change the record source to a query Answers: 1-A; 2-B; 3-C; 4-D; 5-B; 6-B; 7-B; 8-C; 9-D; 10-B; 11-B; 12-C; 13-C; 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 will teach you how to count values from a table or query in Microsoft Access, specifically to display the total number of orders a customer has directly on the customer form. This makes it much easier to see how many orders a customer has without needing to open the Orders form and manually check each time.To accomplish this, I use a function in Access called DCount. This is a very handy tool that will let us total up a count of records from another table. For example, Roberto wanted a quicker way to see the number of orders for a customer every time he opens a customer record, instead of having to open the Orders form and look for the number. With DCount, we can have this total show up right on the main customer form. Before we get started, there are a few prerequisite topics you'll need to understand. These include string concatenation (which is simply joining two text values together), DLookup (which is closely related to DCount and is the foundation for all the "D functions" in Access), as well as the NZ function, which is used to convert null values to zeros. If you're not already familiar with these, I suggest you review them first. Assuming you have those basics down, let's step through how to add this functionality. Let's say you have your customer form open. Normally, to find how many orders a customer has, you would have to open the Orders form, possibly wait for it to load, and count the displayed orders. This can be a slow process, especially if you have a lot of customers to look through. Instead, you can add a text box directly to the customer form to display the count automatically. In design view, add a new text box to your form and give it an appropriate name, such as Count of Orders. To make sure it's easy to read, you might want to adjust the formatting, like text color. Next, assign an appropriate control source to this text box using the DCount function. Rather than counting specific values, you can simply use an asterisk to count all records in the specified table. The table in question here is the Order table, and the criteria should match records where the CustomerID in the Order table matches the CustomerID currently displayed on the form. This way, the text box will always show the correct count for the person you're viewing. To avoid any error messages in cases where a customer has no orders, wrap the DCount function with the NZ function. That way, you'll see a zero instead of an error if no orders exist. After setting everything up and saving your changes, when you open your customer form, you should now see the count of orders immediately. Selecting a different customer will update this number accordingly. Remember, DCount is a very flexible function. You can use it in forms, reports, and even in queries if needed, though there are reasons why it's generally better to use these domain functions only in forms. If you add a new order while viewing a customer, you might notice that the count doesn't automatically refresh. You'll have to either refresh the record manually or navigate away and return. There are ways to make this update automatically, for instance by adding code to refresh the form when closing the Orders form. This technique, along with some additional tips, is covered in today's Extended Cut available for members. In the Extended Cut for this lesson, I show you how to put the order total directly on a button, so instead of a separate text box, you can have the button itself display the order count, such as "5 orders" or "3 orders." I also explain how to automatically refresh the count if you add or delete an order and return to the customer form. This ensures the displayed count is always up to date. I talk about why it's best to avoid using D functions like DLookup and DCount in queries, and stick to using them in forms. Additionally, I cover how to use the DCount function to limit the maximum number of related records in a table, such as restricting a class to a maximum of five students. If you try to exceed that limit, Access can alert you. All of this is demonstrated in the Extended Cut. For those wanting a deeper understanding of these topics, my Access Expert 29 class is available. It runs about two hours and covers all the main aggregate and domain aggregate functions. This includes writing functions like Average, Count, Max, and Min for use in form and report footers, as well as creating features such as daily sales calculations, running sums, and creating custom counters. I also teach techniques like replacing the Excel VLOOKUP function in Access. Membership on my site unlocks even more resources. Silver members and above have access to all Extended Cut TechHelp videos, live sessions, and more. Gold members get a download folder with sample databases from my lessons, as well as access to my Code Vault, which contains a wide variety of useful functions. Platinum members get everything mentioned along with access to my full-length beginner and expert courses for not only Access, but other programs like Word, Excel, Visual Basic, and ASP. Even if you are not a member, you can continue to enjoy my free TechHelp videos. These will keep coming as long as there is interest and positive feedback. If you find my content helpful, please give the video a like, leave a comment, and subscribe to my channel for free. Make sure to click the bell icon and choose All to be notified whenever new content is published. There are more helpful resources, links, and related videos available in the "Show More" section below each video, as well as a link to join my email list if you want to get notified when new lessons are posted. If you have not checked it out yet, you can try my free Access Level 1 course, which covers all the basic concepts you need to start building databases in Access. It is over three hours long and available on both my website and my YouTube channel, with Level 2 available for just one dollar and also free for any channel member. For those with questions you'd like answered in a TechHelp video, just visit my TechHelp page and submit your question. 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 ListAdding a text box to display order count on a formSetting a control source using the DCount function Using DCount with an asterisk to count all records Applying criteria in DCount to count related records Using the NZ function to handle null values in counts Displaying the order count for each customer dynamically Understanding when to refresh calculated controls on a form Applying DCount in forms, reports, and queries |
||||||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access dcount, count records, order count, total number of records, limit number of items, maximum, refresh parent form, button caption, aggregate query PermaLink DCount in Microsoft Access |
