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.