|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Next Appointment By Richard Rost 
4 years ago
Calculate Next Recurring Appointment Date In this video, I'm going to show you how to automatically calculate a customer's next appointment if they're on a recurring schedule in your Microsoft Access database. Angeline from Manhattan, Kansas (a Gold Member) asks: I own a cleaning service. Some of my clients are weekly, and some are every other week. I store their last appointment date in their customer record. How can I update their next cleaning due date automatically when I change their previous appointment date. MembersMembers will learn how to properly calculate exact monthly, quarterly, and annual appointment dates. We will also learn how to store that date in a field in the customer table, instead of just relying on a calculation. This way if the customer cancels or has to reschedule, it's easy to do.
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
Suggested Course
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, appointment scheduling system, appointment due date, recurring appointment, schedule, meeting, bookings, dateadd, date add, vba, afterupdate, after update, Due Dates, Due Date, DueDates, DueDate
IntroIn this video, I will show you how to calculate the next recurring appointment date for regular customers in Microsoft Access, using a cleaning business example. We will walk through updating your customer table to track the last appointment, adding a calculated field to determine the next appointment based on appointment frequency, and building a query to create lists of upcoming appointments. I will also show you how to adjust your forms and queries for different customer schedules, such as weekly and biweekly appointments.TranscriptWelcome 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 calculate the next recurring appointment date for a regular customer in Microsoft Access.Today's question comes from Angeline in Manhattan, Kansas, one of my Gold members. Angeline says: I own a cleaning service. Some of my clients are weekly and some are every other week. I store their last appointment date in their customer record. How can I update their next cleaning due date automatically when I change their previous appointment date? Well Angeline, there are a couple of ways to do this. The easiest way is if their next appointment date is always a certain number of days after their previous one, then we can just use a calculated field and you do not have to store two values in the table. Let me show you how to do that. First up, a little prerequisite. If you have not watched my calculated fields video, go watch that first. Go watch that one on my website right there or you can watch it on my YouTube channel. I will put a link in the description below the video. Go watch that first and come back here. So, here I am inside my TechHelp free template. This is a free download from my website and you can grab a copy if you want to. In this database, I have got a customer table and in this customer table, there is a date field called customer since. How long have they been a customer? Let's change this. Let's go to design view. Let's say instead of "customer since," we are going to call this "last appointment." That's the date they last had an appointment. Let's save that. I am going to go into the customer form. Notice it automatically updated here, but we are going to change the label so instead of "customer since," it will say "last appointment" there. That's just the label. Open up the text box too. Notice here, "last appointment" is the control source. Now the name of the field is still "customer since." So we are going to change that too. I just wanted to bring that to your attention in case you ever change a field in your table. Access will change the control source of that text box, but the name will not change. I do not like that, but that is how they do it. I am going to get rid of credit limit. Let's just get rid of that for now. We do not need to see that there, and family size. Let's just get rid of that too. Save it, close it, open it back up again. Let's say this person--I must have changed this to Dennis in a previous class. I do not remember doing that, but this should be me. I am customer one, always. Dennis is my middle name. Let's say my last appointment was today. You can use the little box here to click on that or here is a little trick: Ctrl + semicolon on your keyboard. This works in Excel as well. It puts the current date in there. There is the previous date. Now I want to put down here a calculated date that will show my next appointment date. Let's say everybody is due for another appointment after a week. Let's just do that first. It is easier. Let me show you how to do that. We are going to go into design view. Let's copy this guy, copy, paste. We will make this one "next appointment." This guy here, we are going to set the name to "next appointment," and the control source is now going to be a calculated value. I am going to delete that. In here, I am going to zoom in so you can see what I am doing better. Shift F2 zooms in. That is the zoom box, the big box. I am going to put in here: =LastAppointment+7 In Access, date values are numeric--one is one day, so + 7 is seven days. Fourteen will be two weeks, for example. Hit OK, save it, close it. Now I like to, since this is a calculated field, I like to make it gray. I am going to open up the Format tab here and just make this a light gray. That way, the user knows they cannot change that. Save, close, open it back up, and there you go. It automatically calculates the next appointment as seven days in the future. This works great for just one customer at a time, but if you want to be able to generate a listing of all of these, it is easier and better to do it in a query, because then you can pull up a list of all your customers and see who has appointments in the next couple of days. This is just displaying in the form, which does not do us a lot of good. So, we will make a query with a calculated field. That is that other video I wanted you to watch. Create, query design. I am going to bring in my customer table. Bring in the star--that will bring in all the fields. Over here, we will make a calculated field. I will zoom in again, Shift F2. This is going to be: NextAppointment: LastAppointment+7 Calculated fields are a little bit different in a query--use the colon. Hit OK. Now when I run this query, you can see there is all my information and there is the next appointment date. Now, that is an actual field that is saved in the query. We are not saving two fields worth of data. It is still calculated, but it looks like another field. So now I can run queries on this. I can say, show me everybody with an appointment coming up in the next two days. That is a little query criteria--that is another video you can watch. I will put a link to that down below too. I am going to save this as myCustomerQ (my customer query). Now what we have to do is take this customer form. Go to design view. We are going to change where this form gets its data from. Right now, it is pulling its data from the customer table. We are going to switch it over to the customer Q. That is why I put Ts and Qs and all that on the ends of my tables and queries. Now this no longer needs to be a calculated field in here. We can change this to get its data from the actual NextAppointment field. There is a field in the underlying record source. Save it, close it, close it. We still cannot edit it, but at least now it is there. Now we have it in the query that we can use in other places too. I can put this in reports. I can set up criteria for that; all kinds of stuff. You want to make a quick list and say, show me everybody who is due for an appointment tomorrow. You can come in here and say create query design. Give me that customer Q now. We are going to bring in the customer ID, first name, last name. We will bring in the next appointment date and say that it has to be less than date + 1 (today's date plus one). So anybody who has a next appointment date that is tomorrow or earlier. So, anybody in the past. This will be your list of people that you can call to follow up with and say, hey, you are due for an appointment now. Some of these dates are really old in here, but you can see the future dates are not showing up. One more thing. Angeline said that some of her clients are weekly and some are biweekly, every two weeks. What we are going to do is in the customer table, we are going to add a field. We are going to say "appointment frequency." That will be a number, so you can put however many days there are between appointments. If they are three weeks, put 21. Set the default value in here as 7. By default, we will say everybody is weekly. You are going to want to put default values in here for everybody. If you have just a handful of records, it is easier to do them by hand. Let's say this one is 7, this one is 14, maybe this one is 21, another 14 here, a 7. You might get someone who is monthly. Now, in the extended cut, I am going to show you how to do an exact month. You have to use something called the DateAdd function. For now, if it is good enough, just put 30 in here. If it is every three months, you can put 90. If it is yearly, you can put 365. I will show you how to do that exactly in the extended cut. Close that. Go to our customer form. Actually, go to the query first. Go to the query, the customer Q, design view. In the customer table, we have that appointment frequency field. In here, instead of adding 7, we are going to add "appointment frequency" to that. Now when I run it, you will see that the date is updated accordingly. Let's make sure. Here is 1-21, that goes to 1-28. This is 2-1, goes to 2-15. Perfect. Let's put a date in here. Let's go with 1-1 and 30 days in the future is 1-31. Now we just have to add this to our form. Customer form, design view. Slide this up like that. Copy, paste. We will put "frequency" or "number of days," whatever you want to call it. Change this to "appointment frequency," change the name to it--do not forget. The format is not a date format, so we will be able to close that, change that, open it up. Look at that. There we go. If I change it here, let's say every 10 days, it will update right to 1-21 to 1-31, whatever you want in there for a frequency. If you want to learn more, in the extended cut, we are going to do this stuff: we are going to store that next appointment date in the table. This way, if you get a customer who normally you see every three weeks, but they are saying, hey, next week we go on vacation, can you make it four weeks or come before, you can manually change that next appointment date as needed, without changing their frequency. You still want to know that they are a three-week customer. The only way we can do that is with a little bit of code, a little bit of VBA--just a couple of lines, actually one line of code to be honest--and you just have to know where to put it. Then we are going to be able to pick the period. Instead of having just a number of days, what if it is a monthly thing? Every month, every three months, so we will do months, quarters, weeks, years, the whole year thing. You get the exact date this way using a function called DateAdd. That is all covered in the extended cut. This one is 20 minutes long, that is a decent size. Silver members and up get access to all of my extended cut videos. I think we are at about 230 of them now. There is a lot of stuff to watch. You get all of them. Gold members can actually download these sample databases that I build. How do you become a member? Click on the Join button below the video. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos plus my Code Vault, where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me. You will get one free Expert class each month after you finish the beginner series. Platinum members get all the previous perks plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free Developer class each month after you finish the Expert classes. These are the full-length courses found on my website, not just for Access either. I also teach Word, Excel, Visual Basic, and lots more. You can now become a Diamond Sponsor and have your name or company name listed on a Sponsors page that will be shown in each video as long as you are a sponsor. You will get a shout out in the video and a link to your website or product in the text below the video and on my website. Do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more and they will always be free. QuizQ1. What is the purpose of the calculated field shown in this video?A. To determine the next appointment date based on the last appointment date B. To calculate the total sales for each customer C. To generate a random customer ID D. To validate user input in a form Q2. In Access, why is it recommended to use a calculated field for the next appointment date rather than storing both the last and next appointment dates in the table? A. It reduces redundant data storage and is automatically updated B. It makes the database slower C. It is required by Access D. It prevents the use of queries Q3. How do you create a calculated field in an Access form to show the next appointment date by default as seven days after the last appointment? A. Set the control source to =LastAppointment+7 B. Set the control source to LastAppointment-7 C. Enter 7 manually in the text box D. Use the SUM function Q4. What is the numeric value of one day when adding days to a date in Access? A. 1 B. 7 C. 0.5 D. 30 Q5. Why is it better to perform this calculation in a query if you want to list all customers due for an appointment? A. Because it allows you to filter and report on all customers at once B. Because forms cannot display calculated fields C. Because tables cannot store dates D. Because Access requires all data to be in queries Q6. How does the tutorial suggest you implement customizable appointment frequencies (weekly, biweekly, etc.) per customer? A. By adding an "AppointmentFrequency" field, storing the number of days between appointments B. By creating separate tables for each frequency C. By creating a new database for each customer D. By adding a Yes/No field for biweekly only Q7. What default value does the tutorial recommend setting for the AppointmentFrequency field for most customers? A. 7 B. 14 C. 30 D. 365 Q8. If a customer wants a three-week frequency, what number should be stored in the AppointmentFrequency field? A. 21 B. 14 C. 30 D. 10 Q9. What feature or function does Richard suggest using in the extended cut to calculate exact monthly, quarterly, or yearly appointment intervals? A. DateAdd B. DSum C. DatePart D. Nz Q10. Why does the tutorial recommend coloring the calculated next appointment date field gray in the form? A. To indicate it is a calculated, non-editable field B. Because gray fields are easier to read C. So it will not print in reports D. Because gray improves performance Q11. According to the video, when you change the control source of a text box in Access, what happens if you also change a field's name in the table? A. The control source updates, but the text box name does not B. Both the control source and text box name update automatically C. Neither updates and you must start over D. The text box will not work anymore Q12. What does the tutorial use to filter a query to find customers due for an appointment tomorrow? A. NextAppointment < Date()+1 B. AppointmentFrequency = 14 C. LastAppointment = Today() D. CreditLimit > 0 Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-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. SummaryToday's video from Access Learning Zone covers how to calculate the next recurring appointment date for a regular customer using Microsoft Access. This topic comes from a question submitted by a member who runs a cleaning service. She stores each client's last appointment date in their customer record and wants to automatically update the next cleaning due date whenever the previous appointment date changes. Some clients schedule weekly cleanings, while others are seen every other week.If the next appointment is always a set number of days after the last one, you can handle this easily with a calculated field rather than storing two separate dates in your table. A calculated field can dynamically display the next due date based on the last appointment date plus the number of days between appointments. Before proceeding, I recommend watching my video on calculated fields if you have not already done so. Within the TechHelp free template database, customers are tracked in a table. Let's suppose you have a field to record when the customer joined, but instead use this as their "last appointment" field. After updating the table, adjust the labels and control source properties in the customer form to reflect this change, making sure everything is named consistently. To enter the last appointment date, you can use the date picker, or for efficiency, press Ctrl plus the semicolon on your keyboard to insert today's date. After that, you can add a calculated field in the form which displays the next appointment date. If you schedule cleanings every seven days, the calculation simply adds 7 to the previous appointment date. Since Access stores dates as numbers (with one unit per day), adding 7 advances the date one week. For clarity, I recommend formatting this calculated control with a light gray background so users know it is not editable. While showing this information on the form works for a single customer, it is often more useful to create a query that calculates next appointment dates for all customers. Using a calculated field in a query, you can easily see who is due for a cleaning within any time frame you choose. The query becomes especially powerful when you want to create filtered lists, like customers due in the next two days. You can then update the data source for your customer form to use this query, so you only need to keep track of the appointment date in one place and can display the next appointment automatically wherever you need it, even in reports or other queries. To handle different appointment frequencies, add an "appointment frequency" field to your customer table. This field stores the number of days between appointments for each client. Set the default to 7 for weekly customers but adjust it per client as needed (14 for every other week, 21 for three weeks, 30 for monthly, and so on). You can then update your query to add this frequency value to the last appointment date instead of a static 7, and the calculation for next appointment dates will automatically adjust for each customer's schedule. If you want to display and possibly update this frequency value on the customer form, simply add a text box bound to the new field. Now, if you change the frequency for a customer, the calculated next appointment will update immediately to reflect the new interval. In the Extended Cut, I go further to show how to store the next appointment date in the table itself. This allows you to handle exceptions, such as when a client needs to adjust their schedule for one specific appointment without changing their routine frequency. This approach involves just a little bit of VBA code to update the field as needed. Additionally, I cover how to use the DateAdd function for more flexible scheduling (such as exact months, quarters, and years), so you can accommodate customers with monthly, quarterly, or annual appointments and get the precise dates you need. Silver members get full access to all my extended cut TechHelp videos and a monthly beginner class. Gold membership also provides database downloads, access to my Code Vault, and higher priority on questions. Platinum members receive all of these perks plus advanced priority, access to beginner courses for all subjects, and a free Developer class each month. Even if you are just enjoying the free videos, do not worry. I will keep producing them as long as there is interest, and they will remain free. 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 ListRenaming fields in Access tables and formsUpdating form labels and control sources after field changes Using keyboard shortcuts to enter today's date in Access forms Creating a calculated field in a form to show future dates Formatting calculated fields to indicate they are read-only Creating a query with a calculated appointment date Switching a form's record source to a query Displaying calculated fields from a query in forms Filtering query results by next appointment date Adding an appointment frequency field to a table Setting default values for appointment frequency Calculating the next appointment using customer-specific frequencies Updating queries to use variable appointment frequencies Adding appointment frequency to forms for easy data entry Testing next appointment dates based on frequency changes |
||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access appointment scheduling system, appointment due date, recurring appointment, schedule, meeting, bookings, dateadd, date add, vba, afterupdate, after update, Due Dates, Due Date, DueDates, DueDate PermaLink Next Appointment in Microsoft Access |
