Last Contact
By Richard Rost
10 months ago
Display Customer's Last Contact & Order Dates
In this Microsoft Access tutorial, I will show you how to display a customer's last contact date and last order date directly in the customer form and customer list form. We'll cover using the DMAX function for single forms and creating aggregate queries for continuous forms to efficiently display these dates.
Jake from Dayton, Ohio (a Platinum Member) asks: I'd like to be able to open a customer record and see their last contact date without having to open the contact form. The same goes for their last order date. And if I could see both of those on the customer list form as well, that would be fantastic. What's the best way to do this in Microsoft Access?
Members
There is no extended cut, but here is the file download:
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, last contact date, last order date, DMAX, Microsoft Access forms, aggregate query, left outer join, customer record, D-function, query design, max function, data aliasing, non-updatable recordset, continuous forms, database optimization
Subscribe to Last Contact
Get notifications when this page is updated
Transcript
Today we're going to talk about how to display the customer's last contact date, meaning the last date that you contacted. We've got a list of contacts, the last order date, or whatever date you want to see on their customer form in your Microsoft Access database. Today's question comes from Jake in Dayton, Ohio, one of my platinum members. Jake says, "I'd like to be able to open the customer record and see the last contact date without having to open the contact form." The same goes for the last order date. "If I could see both of those on the customer list form as well, that would be fantastic. What's the best way to do this in Microsoft Access?"
Well, there are a couple of different ways to do it depending on what you're doing. If you're on a single form, the best way is to use DMAX. If you're on a continuous form like the customer list, the best way to do it is with an aggregate form. Let me show you what I'm talking about. Now, I will start off by saying this is an expert-level video. Expert is what I consider sandwiched between beginner and developer. So we don't need any VBA programming to do this, but it's a little bit beyond the basic stuff. You will need to know a couple of functions and how to make aggregate queries.
Let me show you some videos first that you might want to watch before watching this one. First, watch my DMAX video. The example that I give in this video is how to display the last order date on the customer form. We're going to do this again real quick. I'm just going to review it. But go watch this. I go into DMAX in a lot more detail on how it works. Also, go watch this video on aggregate queries. Again, I give another example very similar to this one, on how to show the last contact date. Today, we're going to put these all together.
Also, go watch this video on outer joins. This is so you don't miss any information. If you join customers in contacts and you've got customers without contacts, you won't see the customer. So this is important too. All right, these are all free videos. They're on my website and my YouTube channel. Go watch those. Come on back, and I'll show you how to put the Legos together differently.
Here I am in Metek Help. Free template. This is a free database you can grab off my website if you want to. In here, we have customers, and customers have contacts, and customers also have orders. A contact to me is every time you talk to the customer, you put in information here like "we talked on phone." So that's the last contact date. If you want to see that last contact date here, which might be handy, you pull up the customer's record and you might see, "Oh, we haven't talked to this guy in four months. Let's figure out what's going on."
Let's go to design view. I will come in here. I'll just add a field down below, copy, and paste. We're going to change this to last contact. This will be a DMAX. It's okay to use DMAX on a single form. Single form means one record because you're just looking up one thing. Open this up. Come in here. Let's change the name of it to "Last Contact Date" maybe. I'll change the format to short date while I'm in here. We'll come in here and we'll change the control source to... I'm going to zoom in so you can see it better, Shift + F2. Whoa. Hang on. My zoom windows are really big. Let me resize this so it's easier for you to see. There's my zoom window, Shift + F2. This is going to be "=DMAX(".
Give me the largest value. What am I looking for? I'm looking for the largest contact date, and that goes in quotes. Where are you looking it up from? I'm looking it up from the contact table, "ContactT." What's my criteria? Well, the customer ID has to be equal to the customer ID on the current form. Close those quotes and put "& CustomerID." That will take the CustomerID and stick it in here inside the string. This will be "CustomerID = " or whatever that value is.
One more thing you might want to do is wrap this whole thing inside of "NZ." NZ is the null to zero, which means if this thing comes back with a null value, what do you want to put in here? Normally, I'd put a different date like a really early date or something, but since this is just a text box, we can just put in here an empty string like that. Just display nothing. Hit okay. Let's close this. Save it, control + S. Close it, open it back up again, and there's your last contact date.
Let's go to someone who doesn't have a contact date. Let's go into a different customer. Let's go to Jean Lucare, and we'll go to contacts. I'm going to delete his contacts so he doesn't have a contact date. Now, if I refresh this with F5, it just shows up as blank. One thing I like to do with stuff like this, since the user can't edit it, is to make it gray. Maybe let's go with that. I'm going to match up that one. See all the fields that the user can't edit are gray.
You want more practice? Let's do the same thing with the order table. We'll see their last order date. I happen to know these fields like the back of my hand, but you might need to open up the table so you have the list of fields. Sometimes I'll just do a screenshot of this and drop it in Paint so I can see what all the fields are. I know what they are because I use this database constantly. Let's go again into the customer form design view. Let's copy this guy, copy, paste, and you can make this pretty later on if you want to. We'll change this to "Last Order." Open this guy's properties up. We'll change this to "Last Order Date."
Now we've already got this written. We just need to change the field names. This is going to be OrderDate. We're looking up the OrderDate from the "OrderT." This is the same. CustomerID is also fine on the order table. Hit okay. Save it. Close it. Let me make sure. Yep. OrderDate, I was right. We kind of embarrassing if I had that wrong. Shut that down. Open it back up again. There you go. Last contact date. Last order date. That's not too hard.
Now this I've already covered in my DMAX video. The reason why you don't want to do this on a continuous form like that is that this has to run for each one of these records and it will slow down if you've got thousands of customers in here. So you want to avoid using any kind of D-function - the lookup, the max, the count, the min, the sum - don't use those on continuous forms and try not to use them in reports. They're fine on single forms. That's what they're designed for.
How do we do them here? Well, for this. So you got a bunch of records. You want to use an aggregate query. Let's come over here. Go to "Create Query Design." I'm going to bring in the customer table. By the way, you can bring them in from over here or over here. I never use this thing. I always turn that thing on. Bring them in from the customer table. Then we also need to join records from the contact table. Here's the important thing. Make this a left outer join. You want to see all the records from the customer table and the records from the "ContactT" where the join fields are equal. If you don't, they have to have a matching value on each side. So if you got a customer like Jean Luc with no contacts, he won't show up in your customer table or in your customer list, and that's not good. Always make sure you do this join with a little arrow there. That way, you see all the customers and the contacts where there are some.
Bring in the stuff from the customer table that you want to see on the form: CustomerID, FirstName, LastName. We'll get rid of the rest of the stuff. Then from over here, bring in the ContactDate. That's what you want to see. It's the important thing. Now we're going to make this an aggregate query by turning this "Totals" button on. Down here, you'll see Group By, Group By, Group By, and then ContactDate. We want to see the most recent one, which is max. Remember, just like the max, right? Dates maximum equals for this in the future. You want to see their first contact date. Don't use first and last ever. I got a whole video on why you don't want to use first and last. Use max.
Let's save this as this will be my, let's call it the "Customer List Query." So we're going to put this behind the customer list form. Now if I run it now, there you go. You can see these are just too narrow. We got to widen it out, right? Maximum contact date. Let's change it like max of contact date. Let's rename that. Let's call that Last Contact Date. Again, Shift + F2 to zoom in. We're going to use an alias here: "Last Contact Date: ContactDate." We're just giving it a new name. That's called an alias. You got a whole separate video on aliasing. I'll put a link to that video down below. But that just renames it, so into the final field comes out. It's Last Contact Date. You can see we are seeing Jean-Luc, even though it doesn't have one. I think someone else is missing one too. Yep. Regina Barkley. That's Reginald Barkley's fake transporter accident twin.
What about the order date? Can you stick the order date in here too? Sure. You can have multiple other tables on this side. Bring in the order date. Do the same. The order table. Do the same thing. You got a left outer join like that. Bring in the OrderDate and make sure you pick max down here for this as well. You can bring in other tables in here as long as you're only grouping by the records from one table. If you group by more than one table, you're going to get a copy of each record for each other table. So, if the customer has two contacts and you got two group-bys under there, one for customer, one for contacts, you're going to get one record for each of those. And you don't want that. So all these other guys have to have a function like max or min or something in them. But once you do that and you run it, there you go. There's max of order date, which again, we can alias that. Right? This OrderDate down here will change this to "Last Order Date" just like that. Save it. Run it. Now we can put this data in our form. Now be careful, though, because notice down here, I can't add a new record, and you can't change this stuff. See? Trying to change it. Trying to type once you do this, once you turn it into an aggregate query, it becomes a non-updatable record set. So this is good for stuff you want to view. I only care about viewing records here. I usually don't edit stuff in continuous forms. But once you do that, you can no longer edit stuff here.
Let's go into here now, design view. I'm going to open up the properties for this form. We're going to change its record source. This is where it's getting its data from, now to this customer list query. The thing we just made. As soon as you just do that, you see that these fields are no longer working. Let's just get rid of them here. Let's get rid of that. Let's get rid of this. Customer sense will become "Last Contact," and then we're going to make "Last Order" next to it over here. "Last Order." I'm going to slide these over to the left. Now this guy's got the green arrow, which means the database can't find customer sense because it's not in the underlying query. We just got rid of it. I change these things up all the time. Double-click there. Over here, we're going to drop this box down. We're going to call that "Last Contact Date." Don't forget copy, paste, change the name. The control source is where you're getting your data from. The name is what this box is actually called. That's the name of that box. Copy paste, slide it over here like that. Then we're going to come over here again, and we're going to make this the "Last Order Date" copy, paste. We're going to close it, shrink that up, shrink that up a little bit, save it, close it. Let's open her back up again. There you go.
Oh, we got wide stuff in here again. We got to change the format. Right-click, design view. Do you really want to see the times in these boxes? Probably not. I'm going to select both of them. I clicked on the first one, held the shift key down, and clicked on the second one. Right-click properties. Go to format and in here, we'll put short date again. Where are you? Short date or whatever format you want. Of course, I use ISO dates, which is year-month-day, which is the best date format ever. Got another whole video on that. I'll put a link to it down below. I'm on a mission to get the whole world on ISO dates.
There you go. There's your complete customer list. First name, last name, last contact date, last order date. These load nice and fast. Aggregate queries run a whole lot faster than individual DLOOKUP or DMAX functions. Just trust me, especially if you've got lots of records. I only have 33 records in here. You got 33,000. This form will take a while, especially if you're pulling it over a network. Trust me, stick with aggregate queries for your continuous forms.
If you like learning with me and this stuff was right at your level, check out my expert lessons. I have beginner lessons for people getting started making forms and building simple reports and queries. That's like nine beginner levels. Then I have 32 levels of expert lessons where we go through all the crazy stuff you can do with Access without needing to program. All the different functions, relationships, referential integrity, all that kind of stuff. That's the expert series. That's before we get into the advanced stuff, which is macros, and the developer stuff, which is VBA. A lot of people don't want all that, and I get it. You can do fantastic things in Microsoft Access without needing to know how to program, although programming is really cool. Check out my expert lessons. There's the link. I'll put a link to it down below as well.But that's going to do it for this video, folks. There is your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time. You'll find links to the sponsors in the description down below the video.
TOPICS:
Displaying customer's last contact date in Access
Using DMAX function on single forms
Using aggregate queries on continuous forms
Understanding Metek Help database fields
Adding fields to customer form design
Formatting fields with short date
Using NZ function for null values
Creating aggregate queries with max function
Using left outer joins in queries
Aliasing fields in queries
Handling non-updatable record sets
Setting record source for forms
Customizing field formats on forms
COMMERCIAL:
In today's video, we're learning about how to easily display the last contact date and last order date directly on your customer form in Microsoft Access. No need to open separate contact or order forms anymore! I'll show you how to use DMAX for single forms and aggregate queries for your continuous customer list form. Both methods ensure you can view the most recent dates quickly and easily without needing extensive VBA programming. Plus, in today's Extended Cut, we'll dive deeper into these techniques to fine-tune your database skills. As always, 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. What is the main topic of the video tutorial?
A. How to program in VBA within Microsoft Access
B. How to display the customer's last contact and last order date in a Microsoft Access database
C. How to set up referential integrity in Microsoft Access
D. How to create a new form in Microsoft Access
Q2. Which function is recommended for displaying the last contact date on a single form in Microsoft Access?
A. DSUM
B. DMAX
C. DMIN
D. DAVERAGE
Q3. When using a continuous form in Microsoft Access, what method is preferred for displaying aggregate data like the last contact date?
A. Using individual D-functions for each record
B. Using a single text box for all records
C. Using an aggregate query
D. Copying data manually to each record
Q4. Why should you not use D-functions like DLOOKUP or DMAX in continuous forms?
A. They require VBA programming which can be complex
B. They are not supported by Microsoft Access
C. They slow down performance if there are many records
D. They do not allow for any data to be edited
Q5. In the video, what is mentioned as a potential issue when joining customer and contact tables?
A. Some customers might be shown multiple times
B. Customers without contacts might not show up
C. Creating a circular reference error
D. Duplicating all customer records
Q6. What is the purpose of using the 'NZ' function in the context of the tutorial?
A. To calculate the net zero balance for customers
B. To skip null values when summing up order totals
C. To handle null values by substituting a default value in the DMAX function
D. To rename query fields in Microsoft Access
Q7. What is a key difference between using single forms and continuous forms for displaying data in Access, as discussed in the video?
A. Single forms require aggregate queries while continuous forms do not
B. Continuous forms allow for editing all records simultaneously, single forms do not
C. D-functions are suitable for single forms but should be avoided in continuous forms
D. Single forms can display data from multiple tables simultaneously, continuous forms cannot
Q8. Why is it important to create a left outer join between the customer table and the contact table in an aggregate query?
A. To ensure only matched records are displayed
B. To ascertain that customers without contacts are still shown in the results
C. To improve query performance significantly
D. To prevent accidental data modifications
Q9. What does the speaker suggest using if you want to rename a field in the results of a query?
A. Use the NZ function
B. Use an alias
C. Use a VBA script
D. Use the MACRO builder
Q10. What is the disadvantage of using an aggregate query in a Microsoft Access form?
A. They cannot include data from more than one table
B. They are prone to data entry errors
C. The resulting record set becomes non-updatable
D. Queries take longer to write and execute
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-C; 7-C; 8-B; 9-B; 10-C.
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 display a customer's last contact date in a Microsoft Access database without needing to open the contact form. This question comes from a user who wants to see both the last contact date and the last order date directly on the customer form or list form.
To address this, there are a couple of approaches you can take, depending on whether you're working with a single form or a continuous form like the customer list. For a single form, the DMAX function is effective, while an aggregate query is better suited for continuous forms.
Before proceeding, it's helpful to have some background knowledge on certain functions and queries. Watching my earlier videos on DMAX, aggregate queries, and outer joins will be beneficial. These videos will guide you on how to effectively display dates and handle data relationships.
Let's start with a single form approach using DMAX to show the last contact date on a customer record. DMAX helps find the maximum value of a set field—in this case, the contact date—from the contact table where the customer ID matches the current form's customer ID. This method is straightforward for single forms since you're looking at one record at a time. Remember to handle null values, potentially by using the NZ function to display an empty string if there's no contact date.
For continuous forms, aggregate queries are the way to go. They allow us to retrieve the latest contact date efficiently. You'll set up a query with a left outer join to include all customer records, ensuring customers without contacts are still visible. By using the "Max" function on the contact date field, you can determine the most recent contact date without duplicating records.
Once your aggregate query is set up, modify your form's record source to the new query. This may cause some fields to become unresponsive if they aren't included in the query, so adjust your form fields accordingly. You can also use this same query method to add the last order date by pulling in data from the order table and applying similar logic.
It's noteworthy that aggregate queries enhance performance, especially when dealing with a large number of records, as they are more efficient than running repeated DMAX functions in continuous forms.
For those eager to expand their Access knowledge, my expert lessons are a great resource. They delve into more advanced functionalities and form design without diving too deep into programming, which might not be everyone's preference.
You can find a comprehensive video tutorial with step-by-step guidance on all the topics discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Displaying customer's last contact date in Access
Using DMAX function on single forms
Using aggregate queries on continuous forms
Understanding Metek Help database fields
Adding fields to customer form design
Formatting fields with short date
Using NZ function for null values
Creating aggregate queries with max function
Using left outer joins in queries
Aliasing fields in queries
Handling non-updatable record sets
Setting record source for forms
Customizing field formats on forms
|
