Access Help Desk Template

Microsoft Access Help Desk Trouble Ticket Template

S  M  L  XL  FS  |  Slo  Reg  Fast  2x  |  Bookmark

          Only $29.99
          Members pay as low as $15

This template is the full database created in my Help Desk TechHelp videos, including the two Member-Only Extended Cuts. When you purchase the template, you get a full downloadable version of the database, plus you can watch the two Extended Cut videos to see how this database was built.

Videos

Members

Go here: Help Desk TechHelp

Description

Video 1 is a free TechHelp video. In this video, I'm going to show you how to build a trouble ticket system for a help desk using Microsoft Access.

Video 2 is the Silver Members Only Extended Cut TechHelp Video. We will learn how to create printable tickets, assign tickets to employees, filter tickets by status (open, closed, etc.), add a Show All filter, and display the count of open tickets, the count of tickets that need to be invoiced, and the total dollar amount of billable hours to be invoiced.

Video 3 is the Gold Members BONUS! Extended Cut where we will learn how to do automatic billing. We will click one button and the system will create invoices for all of the tickets that are ready to be billed. We will use VBA and some sweet recordset programming to accomplish this task.

Sample Database

If you'd like to download a sample copy of the database template, you can do so here. This is a non-editable version of the template which will let you see it work in action. The sample database requires 64-bit Access 2013 or higher. If you're still using 32-bit Office, read this.

Full Developer Database

Once you have purchased the full developer database template, come back to this page and click on the Download button below. This will give you access to the ZIP file containing the Template ACCDB file. The full version will run on 32-bit or 64-bit Access 2013 or higher (possibly 2007, but I haven't tested it).

License

For internal use only. This template does NOT come with a royalty-free license. You may only customize the template for your business needs and for use within your organization at one location ONLY. You may not resell or distribute any form of this template to others without express written permission. Contact me for additional information on obtaining a license to distribute if you plan on including this template in a product you are reselling. Additional licenses are required if you plan on using this template with more than ten (10) employees or in multiple locations within your organization.

Not a Finished Product

Please keep in mind that most of my template databases are not designed to be finished products that are ready to go in a working environment. My templates are meant as starting points for you to customize for yourself so you don't have to reinvent the wheel. This requires that you have a basic understanding of Microsoft Access development. To work on most of my databases you should have completed my Access Beginner series and the first couple levels of my Expert series at a minimum. Most of my databases require knowledge of SQL and VBA as well. Keep in mind that most of my templates are not like off-the-shelf software. They're starting points for developers to be able to modify and use as their own. It really is going to be in your benefit to watch all of the prerequisite videos so you understand the database and how it works moving forward. This is not like QuickBooks where you just install it and use it. My templates are mostly designed for people who already have an understanding of how Microsoft Access works.

Customize For Your Needs

If you would like to discuss customizing this template for your needs, and integrating it into your current setup, please see my consulting page for details. While I no longer accept custom jobs that are specific to a single user, I may include your features in a future version of this template if they have mass appeal. If you are looking for custom enhancements made to this template just for you, visit my Developer Network.

Technical Support

Please note that technical support is NOT guaranteed for any of my courses, seminars, or templates. If you require help with modifying this template, you may post a question in the Forums, however an answer to your question is not guaranteed. If this template comes with an accompanying Seminar, then you should purchase that Seminar to see how the database was constructed. If not, then you should have taken the suggested courses. Most of my templates are designed on a Developer level and you should have a thorough understanding of SQL and VBA before attempting to modify them. If you have a problem with one of my databases, I will only support the unmodified database exactly as it's downloaded from my site. If you have modified it in any way, it may not be something I can help you with. You can post in the Forums, and I may be able to help you, but if the issue doesn't exist in my unmodified database, it's not something I can support.

Questions?

Got sales or customer service questions about this template? Feel free to contact me. If you have technical "how do I" questions about this template, you discover a bug, or want to suggest a new feature, then please post your comments below.

Keywords

microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, job ticket, service ticket, helpdesk, help desk app, trouble ticket, ticketing, support ticket, ticket tracking, access ticketing, how to create a ticketing system in access, microsoft help desk

Comments for Access Help Desk Template

Age	Subject	From
4 years	Help Desk Template	Patrick Gray
4 years	Help Desk Template	Bill Olivo
4 years	Help Desk Template	Patrick Gray

Subscribe to Access Help Desk Template
Get notifications when this page is updated

Intro

In this video, I will show you how to build a basic help desk ticketing system using Microsoft Access. We will walk through setting up the necessary tables for tickets, details, priorities, and status, and then create forms for tracking, managing, and viewing trouble tickets by customer and priority. You'll learn how to add combo boxes for sorting tickets by priority and status, set up subforms for ticket details, use basic VBA to open new tickets, and design continuous forms for listing and managing tickets efficiently. This tutorial provides a solid foundation for managing help desk requests using a custom Access database.

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 build a help desk trouble ticket system in Microsoft Access.

Today's question comes from Bryce in Independence, Missouri, one of my platinum members. Bryce says, "I run a tech support call center help desk. Right now we're just using email to track everything and it's a mess." I bet it is. "When you show me how to put together a database to track tickets so you can get a list of what's open, what's closed, what still needs to be billed and so on. It would also be nice if we could sort them by priority. If this guy's whole system is down, he should get first priority. If that guy's speakers are making a funny noise, he can wait. That kind of stuff."

Well sure, Bryce, let's go ahead and run through this real quick.

First up, a couple of prerequisites. You should know relationships because we're going to have to make some different relationships between multiple tables. So go watch my relationships video first if you haven't already. It's free. It's on my website right there or on my YouTube channel. I'll put links down below in the description below the video. Click on those.

You should also know how to make continuous forms and relational combo boxes. That's where you use a combo box to pick a value from a different table. I also recommend you go watch my invoicing video. It's free again. A lot of the same concepts that we're going to cover now also work with invoicing. Whether you're doing an order form and the order details and relating that to a customer, or whether you're tracking vehicles, or whether you're tracking tickets and ticket detail items, it's all pretty much the same stuff.

Working with Access is like playing with Legos, like I always say. I'm just going to show you how to put different parts together. You can arrange them any way that you want. We'll do pretty much the same thing we did in the invoicing video here. I'm going to add a couple little extra things for the people who have already watched invoicing. But go watch that first if you can and then come back to this one.

So here I am in my TechHelp free template. This is a free database template. You can grab it off my website. If you watched the invoicing video, then you've got a copy of this already. I'm going to use this to modify because a lot of the groundwork's already done with databases already built. We've got tables and forms you can use.

Let's add the tables that we need for our ticketing. People always say, "Well, what tables do I need to put together?" Tables are important. Like I always say in my Access Beginner 1 course, you should lay out your tables first on paper. But these are pretty straightforward.

So let's go to table design. We're going to do our ticketing table first, the overall ticket. So we've got the ticket ID. That's our autonumber, our primary key. We have a customer ID, which is a number of type long integer. That's our foreign key, so we can relate this back to a customer.

We've got created date, the date that it was created. I'll use a default value down here of =Date. If you want to the minute to the second, put Now in there instead. Date is fine. Maybe a due date, which is optional. If it's "just get to it whenever," then leave due date blank. If it's like, "this guy has to have a system fixed by Wednesday, it's due then," then I put a due date in there.

Now, we're going to make a couple of helper tables. We're going to do priority and status. So I'm going to put a priority ID in here. That will also be a number. That will be the priority: low, medium, high, for example. That way we can sort them by priority and get the urgent ones done first.

Then we'll also do a status ID. We'll make a separate status table. That'll be a number as well. Status will be things like active, escalated, canceled, completed, invoiced, that kind of stuff, waiting on the client.

I used to do text before back in the 90s. I don't know how many times I had to wait on the client. You put something in there, you tell them what to do, you say, "Go try this and come back." Of course, this is before the internet was big. So it would always be, "I'll go try that and I'll call you back." I put waiting on client there. If it sat waiting on client for two or three days, assume it's good.

Then, of course, we have a description, which will be short text, and then notes, which will be long text. An overall description and then some big notes, whatever you want to put down here.

Let's save this. This will be my ticketT, the master table, the parent table, if you will.

Now we're going to make a ticketDetailT, just like orders have orders and order details. The order table is the overall stuff about the whole order: the customer, the date it was placed, that kind of stuff. The order details are the line items: he bought a mouse, he bought a keyboard. So our ticket detail will be all the different things that you did for this ticket, because you might have to do, you know, phone call today, on-site visit tomorrow, and then when it's all done, you bill the whole ticket. At least that's how we used to do it back in the day.

So let's close this and we'll create another table. Create table design. We're going to have a ticket detail ID. That's our autonumber and this will relate back to a ticket. There's our foreign key, primary key, foreign key.

Now we've got the service date time, if you want to put a date and time in there. I'll default this to Now, so that's to the minute. If you're on the phone with him, you type in, "He just called," boom, the time goes in there as well - date and time. Whereas the overall ticket, the date it was opened is fine.

Again, a description (short text is fine). If you want to put notes for each item, that's up to you; you can put notes in here too. And then the hours, how many hours are you going to bill for this? This will be a number, and I'm going to set this to type double.

Of all these different types that are in here, I pretty much almost always use long integer or double. Long integer for numbers that don't need a floating point, double for things that do. Those are my two go-to ones. Yeah, there's byte, integer, single, and decimal, but don't worry about replication ID. Just stick to long integer and double. For 99 percent of databases, that's all you need. You don't have to worry about single bytes unless you're doing a gigantic database with tons and tons of data and every byte counts. With today's hard drives, just trust me - long integer and double. Double if it's a floating point.

Default value: I'm going to make that one by default. Let's say by default, everything gets billed in 15 minute increments, so if you don't put anything in there, it's .25. .25 of an hour. See how that works? If it's going to be changed, put it in there. Every phone call's 15 minutes by default unless they change it, or you can even charge just a minute... it's whatever you want to do.

Now, you also have to be careful because "hour" by itself is a reserved word. On second thought, I'm going to make this "billable hours," because you might be on the phone with them for, you know, half an hour or only bill for 10 minutes. That's up to you.

So, save this. This will be my ticketDetailT, my ticket detail table.

Now I like to put some sample data in. Let's take a look at my customer table. We've got customers 1, 2, 3. I'm customer 1, Jim Kirk's customer 2, just so I know what I'm dealing with.

So, tickets. Customer 1 called on today, 1/9/22. No due date. We're going to make the priority table in a minute. We're going to go with low, medium, high, and urgent. So let's say this one is medium priority. We'll go 2. Status: we're going to have active, waiting on client, escalated, a bunch of other ones. Let's say active is 1, so put a 1 in there. Description: what's my problem? My problem is computer won't start. Notes: that's optional.

Ticket 2: Let's say it's for customer 3. Who's customer 3? Oh, Deanna Troi. She calls up. So 3. We're actually typing in one that's a couple days old, so this is from 1/7. Same priority, same status, and she has a "can't hear computer's thoughts." Yeah, I'm silly, I know.

So there's my two tickets that I have in the system. Let's put in some ticket details. So now, ticket 1: spoke on phone, gave tips, told them stuff to try, and this was a half hour. Then someone called in, or then Deanna called in, so ticket 2. She called back again - more chat, blah blah blah, 15 minutes. Then I called back in, so I'm on ticket 1. See how this works? Problem resolved, and then you'll change the status, whatever.

I like to have some sample data. There's my ticket, right? Ticket 1. And then there's ticket 2. See how this works?

If you watched my invoicing database, I go into this in a lot more detail: how this relates back together.

Save changes to that.

Now, let's put together the helper tables. Create, table design. Let's do the priority table first. We have a priority ID (autonumber) and a description. Save this as my priority table. What are we going to have in here? We've got low, medium, high, and urgent. Those are our priorities. That'll be in a combo box on the ticket.

Then we'll create another table, and this will be the status ID (autonumber) and a description. Save it: statusT. Yes. Put some data in here. What's our status? We've got active (brand new open one), waiting on client, escalated (if you got to send this up the chain; your level 2 techs will just look at the escalated stuff first), canceled (I don't like deleting tickets at all ever, so I don't let people delete tickets; they can mark them canceled - that way they're still in the system; we don't want our frontline users deleting data ever. Orders get canceled. Customers get marked inactive. You don't delete something). I have a whole separate video on why you shouldn't delete data. I'll put a link to that video down below. You should watch it. It's pretty good.

Then after canceled, we've got closed (in other words, it's done, customer's happy), then we've got invoiced (that's the final step, because you want to be able to go through all your closed stuff and then bill those customers for that time). Now you know everything's active, then it's closed, and then invoiced. That's the proper procedure. But you can mark them all this way. So there's our status tables.

Now, one of the nice things about having my blank template here, my default template, is that a lot of this work's already done. We can repurpose the order form and the order detail form for our tickets. Just use pretty much the same stuff that we already have built.

So let's go ahead and take the order form. I'm going to copy this and paste it. Control+C, Control+V. I'm going to call this my ticket form. We're going to edit this guy. Go into design view, and we're going to change this so we're working with tickets instead.

I'm going to delete the subform right now. Delete. Let's open up the properties. We're going to change the record source to the ticketT. When you do that, you're going to notice some of the fields are okay. Like description, customer ID, these are all the same. This one's the same. IsPaid we don't need. We don't have any yes/no values, do we? No, we can get rid of that. But notes, description, and customer ID are all in that table. That's why I like to keep the same field names between my tables. There's no problem with having a customer ID in multiple other tables and forms. Same with description, same with notes.

Now, order ID can become ticket ID. Just go to your control source, drop this down, ticket ID. Don't forget to change it there and up here in the name too: ticket ID. Change this guy: ticket ID. Notice that little green thing goes away.

Order date is now created date. Drop this guy down and go with created date right there. Copy, paste: created date. We have one more date too, so let's copy this guy, copy, paste, slide up there, down. This will be the other date, which will be due date. Copy. I forgot one too - we can put a closed date in there if you want to; now it's sometimes handy for metrics. Let's do that too. So closed... or this is due date. Let's put a closed date in there. I'm going to save this.

You should probably also track when it's closed. Let's go back to our table. TicketT. Design view. I'm going to put closed date (date/time), and I like to keep all my like stuff together. So I'm going to put this right under due date, so I moved it. Click, drag. I cover that in the Access Beginner 1. If you haven't watched my Access Beginner 1 class, watch it - it's free. It's four hours long and teaches all the basics.

Back to my form. Back to my ticket form. Design view. We need another date in here, don't we? So copy, paste. This will be now the closed date. Copy, paste.

Now, I'm going to save this invoice button. We're not going to do it now. I might do it in the extended cut for the members, but I'm going to make a button; we just click the button and it pops the end; it puts all this into an order, it pops in order for the person. We'll do that in the extended cut perhaps.

Description, I'm going to slide over here. There's the description. The customer can be right under that. Notes can go like there. These are overall notes up on the ticket. Looks pretty good so far. Slide this in. We're going to put the subform here for the ticket information.

Now I don't like my forms looking like each other, so let's change this background color just a little bit. Let's go from green, let's make our ticketing system yellow. How's that look? That's not too bad. I don't know if I'm happy with that. Let's go with dark blue. Yeah, let's go with dark blue or something else dark blue. I don't think so. Customers are what? Customers are light blue. You have it. You can go dark blue. Let's go dark blue for our ticketing system. I like to have everything kind of like, you know, everything gets its own kind of color. Like all the ordering stuff is green, all the customer stuff is that light blue. We have to change the foreground color on our labels here just a little bit. Make that readable. There we go. Not bad. Save it. Close it. Peek. Everything looks good.

I think the description needs more space though. Let's do a little rearranging. Let's move the dates out of here for a minute. Let's put description here. Slide customer ID up and description can come across that whole field. Yeah, that looks better. Then we can put our dates below it. Now let's put the dates back over here, and then the notes can go over here. You just want to make it so it's nice and friendly for your users. That looks a lot better. I like that better. We've got room out here for the ticketing. Save that. Let's see what it looks like now.

Good, good, good. Maybe bold this. Much better. Beautiful.

Let's do the details. So let's close this. I know we've got more fields to put on here - I'm getting there, thanks for raising your hand and reminding me.

Let's do the ticket details. So I'm going to borrow my orderDetailF. Copy, paste: ticketDetailF. Let's open you up to the same kind of thing: design view. Let's start with our colors. Change this background color first. Change these guys and make them white.

So this one has the ticket detail ID. We have to change where we're going to have data from here. Let's change this to the ticketDetailT right there. A lot of these fields change now. We've got the ticket detail ID, which is this thing here. That's going to be the orderDetailID, but here it's ticket detail ID. Change that, and again remember from the other class, we don't need to see the ticket detail ID, we just need to have it on the form. This is the ticket ID, that's our foreign key. We'll change that. Notes is the same.

Now up here we've got the service date time and the description. Let's do the time first over here. We'll change this one to service date time. Copy, paste. This doesn't need to be quite that big - let's slide it to about there. We'll call this date time. Change our background coloring. Next up is the description field, so I like this in here. We can get rid of that because we don't need extended price. This will be hours. Slide that out there. So this will be description and then hours. Copy, paste. I'll have to align that. Description.

Yeah, I often find it's easier to just repurpose all other forms from scratch all the time. This one will be the hours. Billable hours right there. Copy, paste. The label can just say hours. Now, you are going to be the sum of the billable hours. So this will be the sum of billable hours. I'll just copy that much of it there and put it here. Save it.

Let's close it and take a peek, see what we got. Looks good. That alternating background color... I forgot about that. Design view. Alternate background color: no color.

One more pet peeve that I have is this stupid thing. I've suggested this to Microsoft: they need to move this little box here - the date picker. Instead of blocking the next field, move it over the date field. That's what it should do. Now, for this, I don't think we need to be able to pick a calendar date. This is going to be something automatically filled in. But I'm going to turn it off: Show date picker: never. Save it. Close it. Open it up. Looks good.

In fact, I'm going to make this a tad wider. If we get a longer date in here, it won't fit, so design view. Let's shrink this down just a wee bit, and make this just a wee bit bigger. Save it. Close it. Take a peek. Okay. Looks good. Let's left-align the date. Format: left. Save. Close. Looks good.

We've got all of the fields for this one in here, right? We've got the description. How's our tab order? Tab order is fine. We've got notes down below if we need bigger notes. We've got the total on here. But this should not be formatted as currency; it was currency in the last table, so that's an easy fix. Click and click, and we'll just get rid of that format. Delete. Let's go decimal places: two. Save it. Close it. Open it back up again. Looks good.

If you want to force it to be in two decimal places, do this: go design view, pick both of these, go for the format, and go 0.00. That will make sure you've got that in there as a fix, like that.

Okay. Let's put this inside of this. We'll drop it right inside of there. So, design view. Close you up. Click, drag, drop. That's all you have to do to put a subform inside of that one.

If you don't know much about subforms, I do have a whole separate video. I just finished recording it. I had an older tip video on subforms - that got me by, but it was old, like 2009 old. So I just redid that as a TechHelp a little while ago. I'll put a link to the subforms video in the list of links down below too. You can go watch that if you want to and learn more about subforms.

I probably should have had them in the prerequisites, but I'm pretty sure that the subforms video is a prerequisite of the invoicing video. That's why I don't always list all of the prerequisites, because by the time you watch invoicing, you've got to watch its prerequisite. So I know - yeah, it was like the million other things before it.

Let's widen this out just so it's all uniform like that. We'll shrink you a little bit. I'm going to save this invoicing button for the members later. We've got some more fields to put up here too, don't we? Let's save this. Let's close it. Let's take another peek at it.

Looks pretty good. I'm liking it so far. There's ticket one. There's ticket two. See how the relationship is made automatically by that subform? I don't like the subform wizard. I never use it. But notice the link master and child fields change to ticket ID. When you drop in that subform, it saw that there was a ticket ID in both of these forms. Excuse me. And I forgot the G in "you too." Close.

Got a couple of combo boxes to make. Now, the fields that are missing are priority and status. So let's do this here. I'm going to take the notes and slide them over here. You want to take big notes about the entire ticket like that; nothing wrong with that. Maybe we can get about that big. Now I've got more room in here for more combo boxes. Let's move the description down and slide the dates up like that. Now I've got more room in here for more combo boxes.

This is all about aesthetics, and like I said, I've got whole other videos on form design aesthetics too. There's all kinds of stuff you can do to make your forms look cool. We've got two combo boxes coming. I do like the combo box wizard; the combo box wizard is a good wizard.

So I'm going to look up the values from table/query. Again, this is the relational combo box video that you should have watched. Let's do priority first: priorityT. Bring over both fields. Let's sort by priority ID. I want them in the order that I put them in the table: 1, 2, 3, 4. Next. They're going to show up in this order now: low, medium, high, urgent. You don't want these alphabetical. Let's leave it like this. I was going to say you might want to even show the key, but now let's leave the key hidden. That looks good. Next, store that in priority. The label: priority. Slide you out. Move you under there, and change that color.

One more: combo box wizard. We need to do the status the same way. Next. StatusT. Next. Bring them both over. This one we're going to sort by description. Next. Yeah, we'll do it like that, because they can happen in any order, really. Next. Store that in the status ID; that's the status ID of the ticket table. Status. Slide over there. Slide you under there, wait so we can read it. Oh, wrong one. Undo. Click. There we go. Save it.

Alright, let's close this. Let's open it back up again. Looks pretty good.

I'm thinking let's put default values in for these boxes too, because if we go to a new one now, we need a default priority and a default status. So let's go back to the table. Default status is going to be, where's my status list? Status 1 will be active, so that default value there on 1. And the default priority is going to be medium, 2. There.

I'm going to do it so that when you open up a ticket, it will start you on the description field. So let's go into design view again, and let's change the tab order. Let's auto order first. I always like to change my tab order. I've got combo 19, combo 21. I do this a lot. I'll build a combo box or drop a button in and I'll forget to give it a good name. Is combo 19 a good name? No, I can't tell what that is looking at it. Take a tip from Alex - Alex is my number one, and you have to put in here a valid name. Now, you could call this priority ID if you want to; I like to call it priority combo. The reason why is because a lot of people ask me "Why not just call it priority ID?" The reason why is because when you're doing programming sometimes, if it's priority combo, you know you can handle a combo differently. A lot of my tips and things I tell you to do, I don't always explain at the beginner level because you won't get it, but when you're writing VBA code, if it's priority ID, I know that it's a number of type long integer and I have to treat it like a text box. If I see this "priority combo," now I can know that I can access its columns in VBA; I can do all kinds of different things because it's a combo box. It's still that ID, but it's a combo box, and I can mess with it differently. I don't have to stop and go, was that a text box or a combo box?

So this is going to be my status combo. Let me just drop in random tips, that's what these TechHelp videos are all about, folks, just random stuff off the top of my head.

So now let's go back to what we were doing a minute ago. Let's go back to our tab order. Click, do tab order. There we are.

We're going to start the tab order with the description. But I still want the rest of this stuff in the tab order, but when we open up, I want to boom, start on description.

So where's description? Right there. Move to top: description. Now, after I'm here at description and I hit tab, I want to go into the subform. That's going to be the ticketDetailF, so that's going to be next. I'm going to type in this and then drop right in here to start typing stuff in.

But if the user clicks on any one of these things to change them, I want the rest of the tab order to behave normally. So ticket ID, customer combo is fine. Actually, let's go down across, then over to notes - how's that sound? So we're going to go to created date next after ticket ID, then due date, then closed date. Now I'll go tab, tab, tab, come over here. Customer ID's next, customer combo, priority combo, status combo, notes over here, and then we'll drop back to description.

So what I'm going to do is I'm going to take the invoice button out of the tab order. We'll see that in a second. And I'm going to make it so it cycles on the same record instead of moving to the next record. I hate that. I don't often change that as much as I should.

So let's make it so that this is not a tab stop, which is on Other, and then tab stop set to No. So as you're tabbing, tab, tab, tab, tab, tab, you'll never get there.

Likewise, for the form properties, find Cycle and change that to Current Record. That way, when you tab, tab, tab, tab, tab, tab, it'll just go back to the top of the same record.

Save it, close it, and let's see what I'm talking about. When I open it up, it started me right in description. If I go to a new record, for example, we're going to open a blank record from the customer form. If I go to a new one, and I type in whatever, and then I tab, that's going to put me right down in here.

In here, I'm just going to set it so that description and hours are the only two things that are tab stops, but let's continue. Tab, tab, tab, tab, tab, and these are off a little. Actually, they're right, but I'm going to take date time out of there, because usually you just want to type in stuff and have this be automatic.

But if the user does click up here, that's going to go tab, tab, tab, tab, and back to the description. That's exactly what I wanted.

Let's change this in design view so that you are no longer a tab stop. Save it, close it, open you up, tab - goes right into there. Same. So we're cooking with gas. We're good now.

Another thing I mentioned earlier: I don't want to allow users to delete stuff out of here. No deleting this, no deleting these. They can set hours to zero if they want to cancel something out down here, but you've got to mark this as canceled. How do I do that? Well, right-click, design view. We're going to go to the properties for the form. Go to data, allow deletions is No.

In here too, you've got to click once for the subform object, click a second time for the properties for the subform. Allow deletions there: No as well. So now they cannot delete anything from the ticketing system. Open it up. Try clicking on this and hitting delete - no, can't do it. Sorry, no longer valid; mark it canceled. Same thing down in here, click - can't do it, sorry.

So next up, we need a way to open a new ticket for the customer. What we're going to do is go to the customer form, right-click, design view. I'm going to put a button down here to open up that form and go to a blank new record.

Now, you can't do this with the button wizard. If you come in here, you can go Form Operations, Open a Form, next, pick the form you want, like the ticketF, next. All right, we can open the form and show all records or find specific data matching the current record. I could find the records for the current customer, but I want to go to a new record. There's no way to do that with this wizard.

So you can either write a macro, or you can use a little bit of VBA. I like VBA. I really don't like macros. You should learn a little VBA.

Now, I didn't make this a prerequisite, but I'm going to tell you now: pause the video if you can and go watch my intro to VBA video. It's free, again, absolutely free. It's about 20 or 30 minutes long, just to teach you the little tiny basics we're going to need, like just a couple of lines of VBA code. Watch this, it will give you all the background you need first, how to know where to put it and how to start it. I'm going to show you right now.

You don't really need to do this, but this is one of those things where, once you learn a little teeny-tiny bit of VBA, Access becomes so much more powerful. It opens up a whole new world of stuff, just with a couple of lines of code.

So here's what we're going to do. I'm going to borrow one of these buttons. Copy, paste. Slide it down under here. Let's call this one "New Ticket." Give the button a good name: double-click on it and come in here instead of command32, let's call this newTicketButton.

Now we're going to put some code in the button. Right-click on it, come up to Build Event. You might get a little window popping up saying what kind of builder do you want; pick the code builder. I talk about that in the intro to VBA. Right in here we're going to put a little bit of code.

I want to open up that form, but I want to open it up to add a new record. So here's how we're going to do it:

DoCmd.OpenForm "ticketF", , , , acFormAdd

That's it. That's all you have to do.

You can see some of the other open form commands up here. This one opens up the contacts for this customer. This one opens up the orders for this customer. We're going to do something similar with a ticket thing in just a minute, but I want to do this first. Save this.

Let's shut that, close this, go to the customer form. Click on New Ticket. Boom. There we are, new ticket. See? We're at new; all this stuff defaults. This was set as a default value in the invoicing video that we watched, right, this combo box? It's a default value right here: Forms!customerF!customerID. That's how to get a value from another open form. So the customer form is going to be open for this to pull that default value in. I have another video that teaches you how to do this. I'll put it down below. It's called the FormName video. It's in the links section down below.

This is why I have my regular course series, my Beginner 1, 2, 3, Expert, all those regular courses, because I teach you things in the proper order there. If you're just coming into this video and you haven't watched my previous stuff, then you have to go watch this other one first, then go watch it. It gets a little confusing, I know, but in order to do this, you kind of have to know stuff before it. It's like when you go to a college class - if you're going to take calculus, you have to know arithmetic and algebra first before you can learn calculus. It's the same thing here; there are prerequisites. You have to learn stuff before you can learn other stuff. This is a little more advanced. That's why in my regular courses, I teach you things in the order that I think you need to learn them instead of jumping around like this.

The TechHelp videos jump around and teach weird different stuff.

So now I've got a button that I can click on, boom, and then I'm ready to enter a new ticket. I'm going to slide this over here like that. Save it. When you move a form around and hit save (control+S), it saves its location. If I open it up again, boom, and opens up over here now. Yes, I've got VBA code where you can control exactly where the form opens up on the screen, but that's a lot more advanced.

Now I'm ready to say, okay, "Called for on-site service visit," tab, "Went to office," charging them three hours. "Called for follow up," charging for 0.25 hours. If you want to put notes on either of these things, like here, "Went to office," this is the notes that's bound to that. This is the notes that's bound to the overall ticket. In here you can put "five mile drive," whatever about that particular thing. The call for follow up, "said everything is working," blah blah blah. Over here, if you want to put, you know, directions or whatever, whatever that is pertaining to the entire ticket, that's the difference between those two notes fields.

So next we're going to make a form that lists all of the tickets in the system so your people can just open it up and see, "Okay, what do we have to do?"

To do that, we're going to make a query to bring everything together. Create: Query Design. Shut this down. We're going to bring in the ticketT. We don't need the ticket details in this, just ticketT and then, of course, its helper data, so we need the priority table, we need the status table, and let's bring in the customer table too.

This is the stuff you want to see on your summary form. I want to see the ticket ID, the created date, and the due date. I want to see the priority, but I don't want the priority ID, I want the priority description, but I don't want to call it "description." I want it called "priority." That's called making an alias. It's description, but it's description from the priority table, and I'm going to call it "priority" right here. If you run it now, you'll see what I'm talking about; so you just get the word medium. Now, this is not editable; you can't change it.

One of the big things that people always do is they make these big, gigantic, complicated queries with all these joins and they're like, "Why can't I change things?" You can't edit that. It's brought in from another table. If you wanted that to be editable, you'd have to bring in the ID and make a combo box out of it, which I'm not going to do. This is just for display purposes. When you see something on the store on the shelf, "This for display purposes only," this query is for display purposes only.

Same thing with the status now. Bring in the status description, but I don't want to call it description, now I'm going to call it "status" (alias: status). So, now instead of priorityT.description and statusT.description or whatever, I've got that, and it looks much nicer.

What else do you want in here? The description can come in; that's the actual description of the ticket, which I'll bring to the left. I'm going to keep all the stuff in the same table together. I want to see the customer. I'm going to bring in first name and last name together, but I want them displayed in the same field. So I'm going to say over here the customer is going to be: first name & " " & last name (that should get the field, because there's only one table in here that has first name and last name). Let's see. Yep, it worked. If you had multiple tables in here that had first name and last name, you'd have to say customerT.firstName, customerT.lastName. Since this table is the only one that has fields called first name and last name, Access guesses it correctly.

There we go. These are all the fields that I need for my ticketing table. I'm going to save this as my ticketListQ, my ticket list query.

Now I can use this to build a status form. How do we do that? It's going to be a continuous form. Let's borrow another continuous form. What do we have in here that looks good? Let's do the customer list form, this guy. That's a good form; we can repurpose this one. So, copy this guy (customerListF), copy, paste.

I built that in the blank database or the contact management one, one of the prerequisites for the invoicing video, like I said. All right. So this is going to be my ticketListF. Let's design you.

First off, let's work on the colors. Change the colors, people. I don't like them. These are always the same recent colors. That one. This background color, go back to that. Change the alternate background color (which I hate), no color. I've got to change the template to "no color." So, I can do it now.

Now let's set these guys up. Open this up, go to the All tab here, change the record source to the ticketListQ, so we have our helper data. Then we just have to change our fields around.

But first, a word from our sponsor. Who's the sponsor? Well, that's me. I'm my sponsor. Just a real quick promo. If you haven't yet watched my free Access Level 1 video, go watch it now. It's great. It's four hours long and covers all the basics. If you like all this stuff with the subforms, combo boxes, reporting, you name it, I've got 32 levels of Access Expert classes. There's nine Beginner, there's 32 Expert, and a bunch of Developer stuff with programming. The Expert series is what covers all the cool stuff - all the nuts and bolts and the nitty-gritty.

Like I said earlier, I cover things in the right order so you're not confused jumping around between different videos. Just follow Expert 1, then Expert 2 (it's on relationships), Expert 3 (we learn a little bit of SQL, form for the totals), Expert 4 (we go over more form controls). So I teach you the things in the order you need to learn them.

Come to my website - there's the address right there. You'll find links down below in the link section. I've got a membership program you can sign up for if you want to. You get a 50 percent discount. If you want to learn more, you can sign up for the Developer classes; I've got lots and lots of those too, where I teach VBA programming.

Come to my website, AccessLearningZone.com. I know I've got this little short URL there - 599cd.com. That was my old thing, but I kept it because it's nice and small, and it works for all my links. So, come to my website. Check it out. All right, let's go back to the class.

I've got puppies to feed, so come buy a couple of lessons so I can feed my dogs.

So, where were we? TicketListQ. I am going to... let's see, customer ID - we're going to make this what we're going to click on to open up the ticket. So that's going to change to the ticket ID. Copy, paste.

Next up after that is going to be: the created date, the due date, the description (that'll be bigger, of course), the priority, the status, and I'll need one more for the customer (let's call it "customer").

In a situation like this, I really hate layout view. I almost never use it, but there are situations where it's okay. So I'm going to save this, close it, be very careful with layout view if you have VBA code in your forms; I've seen it corrupt forms before.

Open up the ticket list and you can see we need to change the list. Right-click, Layout View. Now you can resize these things a little bit. See how it's... already, I'm just trying to slide that to the left and it already ganked me. Come back in here... be very careful. Let's see if I can... yeah, it's going to be tough to... no. Okay, I don't like it. I don't like it already. I'm just going to stick with this for resizing stuff. I don't like what it did already, so I'm going to undo that. Undo, undo, undo. I'm going to undo that.... I'm going to undo that. Undo, undo, undo, undo. Okay, so you resize...

I'm going to go to design view. I'm telling you, I have bad luck with layout view. I really don't like it. Every time I try to move stuff around, it messes up on me. So slide created date over. Let's see what that looks like now.

Save it. Layout view. Okay, this can be smaller. About there. That looks good. Left align it. I'm going to left align everything while I'm at it. Left, you left, you left, you left. Okay. Design view. Slide due date over. Again, see - it didn't move on grid, like I like. I'm just going to make you the same size. That finger syndrome... come to that. Beautiful. Description's going to be nice and big. If I may have it, it's wider. Priority and status will slide over here. The client can go out to the right.

Save it. Let's check layout. Not bad. That's not too bad. These could probably be a little bit shorter. Let's save it. Go back to design view. Be very careful with your layout view - it doesn't go to the grid. Come on, Microsoft. When you resize these in layout view, they should stick to the grid. I'm going to put that on my notes for Microsoft. Microsoft has a feedback page now; there's Microsoft's Access Feedback Portal. A lot of people have stuff in here. Michael, by the way, he's got a great blog. I'll put a link to it down below.

Now we just have to change our labels around. Sometimes what I do, I've gotten lazy lately (I don't know if it's lazy or efficient; we'll call it efficient). I do this with one label now a lot of times. I'll go to the ID for the ticket ID, and just go: space, right, created, space, due date (or just due), right, description, easier than working with multiple labels, and then priority, status, and then the customer. Okay.

It's a little bit of a pain if you've got to move these things around, but just for this, it's fine. It works great. It's quicker to design it in the first place.

No, I don't want to open customer, I want to open ticket, and I think this is a macro. Right-click, Build Event. Let's see. Yeah, I built this early on with the first blank one. So, let's just get rid of it. Goodbye. I don't like using those macros. I use them for the very, very beginner people so I don't confuse them, but we're a little bit past that.

So, let's go into design, drop a button down here: cancel, open ticket. "Ticklet" (open ticklet) - is that like a checklist? Open ticket.

Now, right-click: Build Event. Oh, I forgot to name my button. It's Command18. Click. Stop. Go back. Alex is going to yell at us if we don't change the button name to "openTicketButton." Better. I still, to this day, forget. I've been working with Access for almost 30 years. I still forget to do that.

Build event:

DoCmd.OpenForm "ticketF", , , "ticketID=" & ticketID

That says the ticketID on the Ticket form is going to be the same as the ticketID on this form, whatever record you're on.

We're going to put the same code in this, which is going to be in the double-click event. I cover this in the invoicing video, I believe, or one of those invoicing video prerequisites. TicketID double-click, look at it; it's the same as this stuff up here.

I'm going to actually get rid of this. Now, I've got an If IsNull on there: Exit Sub. So if you're on a blank one, it doesn't try to open it. Not a bad idea; let's put that in here.

If IsNull(ticketID) Then Exit Sub

Put the same code in here. I can actually delete this because we got rid of customer ID.

I don't like having the same code in two different places, so what I'm going to do is make my own subroutine.

Private Sub OpenTicket()

Now watch this: I'm going to take this code, put it in here. In here, all I have to say is OpenTicket. Do that, and I can recycle it. Watch this. Copy and then paste. Now I've got OpenTicket in one spot. You don't want to maintain multiple sets of the same code in different places. If you're going to use the same code in two different places, put it in its own subroutine. This is all stuff I cover in my Developer Level 1 class, my beginning introduction to VBA.

Private Sub this, Private Sub that, save it, come back out here. See, a little teeny tiny bit of programming makes your life so much easier. Want to open up this ticket here? "Can't hear the computer's thoughts." Double-click, boom, there's your ticket. Close that one, open this guy up. Boom, there it is. Maybe I like to slide this one down just a little bit. Open up ticket one, double-click, boom, same.

One more thing to do, actually two more things to do. Let's do a little conditional formatting in here. Let's open this one up. Actually, let's make a requery button too. There are a few things to keep in consideration.

So you're working on this ticket. Right. Priority gets changed to high. She's having a fit. Now, when you close this, this doesn't - oh, it did update. Okay. It doesn't always update. In this particular case, it did. But if you come down here, for example, open this one up, change it to urgent, close it. It happens to be changing in this case. Not always. You might want a requery button down here.

I show in other classes, I can show you how when you close one form to requery another one, but we'll just do a requery button. Copy, paste this button. Let's change this to requery. Oh, and change the name, requeryButton. Right-click, build event, and it's just Me.Requery, that's it. Me.Requery.

That way, if you make major changes and things aren't updating, you can just requery it instead of having to close it and reopen it. In fact, let's put a button on our main menu: copy, paste. Ticket list. Right-click, build event:

DoCmd.OpenForm "ticketListF"

Close the main menu, reopen it again: ticket list. Boom. Open up this one. Boom, there you go. Change this to low. Close it. That's updated; that's good. If not, hit the requery button; it'll requery this listing.

You might not want the user to be able to add new ones from here. You might want to have them go into the customer's record first. So come in here in design view, open this up, change the data: allow additions: no; allow deletions: no; allow edits: no. Save that, close it, close it, open it up.

You can make it so you can double-click on the customer too and open up the customer's record. That's easy to do. Design view - I like to make it so that any field you double-click on does something. So I like to make it like this blue color, for example. Use the format painter, format paint over that. Now, we don't have the customer ID on here. The customer ID wasn't brought in here. It's not in the query, so let's go back to the query. This is our ticketListQ, design view. Let's add the customer ID. Save that.

Now, we can put the customer ID in here. Right-click, design view. We might be able to get away without doing it, but to be safe, let's add it. Customer ID - we can drop it right down here in the bottom, because we need it to open up the customer form. I'm going to make this a hidden field, or a hidden field: so I make my hidden fields red so in design view I can see that it's a hidden field then I double-click on it and make it visible: no. Save that.

Now, the double-click event on this guy: open it up, the event. Actually, it's text17, isn't it? Yeah, we didn't change the names of these boxes, did we? Let's see. Nope. Status - we have to fix that. Nobody yelled at me. You guys have to raise your hands in the back of the class and yell at me to change the names of these boxes. It doesn't matter really, but it does, because you don't want to be calling it in code later on and realize it's the wrong field name. Copy.

I changed all the control sources, but not the names.

So, this event on double-click, right there: Dot Dot Dot, code builder.
DoCmd.OpenForm "customerF", , , "customerID=" & customerID

Open the customer form where the customer ID on the customer form equals the customer ID on this form. That's why I had to have it on the form so I can reference its value. Now I can do this; I can open up the ticket list and say, "Oh, let's go see Deanna Troi," double-click, boom, customer. I need to take it for, boom, new ticket.

Now, we can filter this to say, "Let's see all Richard Rost tickets," by right-clicking and going equals Richard Rost. Or we can put a button over here - wrong one - we can put a button on the customer form to view his tickets. Here we go. Copy, paste. View tickets. How do we do that? Right-click, build event. Command32. See? I always forget this. I'm going to leave it in the videos until I get better at doing it so I stop annoying all of you because I can never remember to name my buttons.

ViewTicketsButton. Okay, right-click, build event, try this again. Here we go:

DoCmd.OpenForm "ticketListF", , , "customerID=" & customerID

That's why I also wanted to put it on there. I see a lot of people do this: they go Me.customerID, they saw it online somewhere. You can, there's nothing technically wrong with that, but you don't need it. Why am I putting it on there? Me is assumed, and customerID is a field on the current form, so why bother? The only thing you really need Me for is properties. Me.caption: that's the caption of this form. Usually for controls you want to reference with just the control name, like: firstName, for example. That's a whole different video. I've got a bang versus dot video; I'll put a link to it down below if you really want to watch it. The exclamation point is called bang, by the way.

Close this, close this, close this. Ticket list, go to a customer or go to a ticket. This looks good. Let's go to a customer (actually, let's start from the customer list), open up the customer, view their tickets, boom. Isn't that pretty? There you go, there's the basics of it.

Well, let's put a little conditional formatting in here. Let's go back to the main ticket list. You might want to sort this list by priority, by the way, so this is based on a query. Let's go to the query, ticketListQ, design view. Now, we're going to sort by the priority ID. What do the priorities look like? Urgent is 4, low is 1. So let's sort this by priorityID in reverse order. So we're bringing in priorityID now, and I'm going to bring the sort fields up to the front. Sort this in descending order.

You could just put the sort in the form if you want to, but if the form is based on the query, it'll get that sort: urgent, medium, low. Now, let's put a little conditional formatting in here. We can use priority to do the conditional formatting; that's fine. But it's just if you change the word then it gets tricky, so it's better to do the conditional formatting on the ID. So we need to bring the ID in here now too. I'm going to copy this guy (it's going to be hidden), copy, paste.

Open you up, and we're going to change this to the priorityID. Now, you're probably saying to yourself, "How am I going to do the priority conditional formatting if the priority is down here?" Well, we're going to use a weird kind of conditional formatting called expression-based. In other words, you put a little expression in here instead of basing it on the value that's in this field. I've got a whole separate video on this too; I'll put a link to that video down below as well.

Here's how you do it in a nutshell: go to conditional formatting. Click on your field, go to conditional formatting, new rule. Instead of "field value is," which is based on that field, I don't want to have it equal to the word "urgent" (that's what's in that field). I want the number, because I might change the text later in the table and it'll break my conditional formatting, but that value will always stay the same. So "expression is," and it's basically [priorityID]=4. Then we'll let go, let's say, red with white lettering and make it bold. But be careful, watch what happens.

Hit OK. It looks good, right? "Expression is: priorityID=4" - that looks fine. Save it, close it, take a look. It's not working. Why? Well, conditional formatting always messes with you. See, look what it did. It put the word "priorityID" inside quotes. We don't want that; we want this to be inside of brackets. Now I said you don't have to use brackets if you don't use spaces in your field names - 90 percent of the time that's correct, but with conditional formatting it likes to mess with you. Now, [priorityID]=4, save it, close it, and there you go: there's your urgent.

You can do medium and high and all the other ones. I'll just do one more for high.

Format, conditional formatting, new rule: expression is [priorityID]=3. What do you want high to be? Let's do light red like that and bold. OK, looks good. Might as well do the other ones too. Format, conditional formatting, new rule: expression is [priorityID]=1 (we'll skip medium, which will be just regular white, then go to low which will be 1), and we'll make this one, just gray. Now, let's do white background, gray text, that's low. OK, and they'll be applied in the order shown. That usually only happens if you have inequalities like ">100", that kind of stuff.

OK, save it, close it, open it back up again. There you go: urgent, medium, low. Let's see if this medium one changes; let's open it up, change it to high, close it up, now it's formatting as high.

That's kind of cool, and you can do the same thing if you want to give due dates in here. If you want to hide the stuff that's not active, whatever, you can come in here. Let's say one of these guys is closed. You can put a filter on, just say right-click, equals active, just show me the active stuff. You want to sort it by date, sort it by the customer, whatever you want to do - the sky's the limit.

In fact, in the extended cut with the members, I'll put a couple of little filter boxes up here to make it easier to do that kind of stuff. But there you go - there's your basic ticketing system. That took us what, about an hour to build? Not that bad. Tickets, ticket list, open up a ticket, come in here, type in what you got, type in more stuff, billing for another half an hour, click the invoice button, it invoices it, and marks it closed or marks it invoiced (which we'll do in the extended cut too).

So we've been working on this for about an hour now. We've got something pretty respectable. I've got a lot more I'm going to work on; it will be in the extended cut for the members. If you want to learn more about this kind of stuff, come to my website. If you want to learn more, I've got a lot more in the extended cut.

We're going to make printable tickets (right, this guy over here) - if you can print it out, you can give it to the tech, he's got to go on the road. You can put the address on here if you want to. All the stuff that's been done previously, we've got it.

Then we're going to assign it to employees, so you can put in here a list of employees and who is each one of these tickets assigned to. The person, then you can filter on his name, say "show me all my tickets."

Then we're going to do a filter by status box. You can drop this guy down and you can pick "show me all the active ones." That'll be the default when you open it: "show me just the active ones," "show me the closed ones," "show me the escalated ones," "show me the ones that are invoiced." We're going to add an "all" option - this right here is worth the price of admission, folks, this "All." That's a crazy little trick, so you can put that in the table and have it actually show up so you can display all of these guys.

Then we're going to have open to billing hours show up in here. In the customer list, you can see this guy's got one open ticket, this guy's got two tickets; we have to bill for a total of five and a half or four and a half hours. All of this in the extended cut for the members. It's 60 minutes long. A lot of my extended cuts are pretty long - some of them are 50 minutes, a lot of them are like half an hour; this one's an hour.

Silver members and up get access to all of the extended cut videos. Gold members can download the database that I build in the video. Sometimes I do this with my extended cuts - I had more than an hour for the regular extended cuts, so I made a bonus extended, extended cut for the gold members. I've got Silver, Gold, Platinum - another half an hour. What I do is automatic billing. In other words, it takes all of the tickets that are in the system for all of the customers; you click one button and it generates all of those invoices automatically. You click the "do billing" button, it creates an order for each customer that has tickets in the system, adds them all up, line items are ticket one, ticket two, ticket three, but you do it because you're invoicing them. Another 30 minutes for gold members.

So, come on, sign up, become a member. You get access to all this stuff, all my extended cut videos, and Gold members, in addition to the videos, can also download these database templates. So join up now.

Oh, and one more thing: I know a lot of you have said to me that you want some of these templates that I build but you don't want to sign up, you don't want to make a monthly commitment to become a member. That's fine. You'll also find this in the templates section of my website. Just go to the website, click on Templates (it's right across the top), you'll find it there too. You can buy this as just a standalone template instead of making a commitment to become a member. But you can also sign up for membership for one month and cancel. There's no minimum month; you don't have to be a member for a year. You can just sign up for one month, download whatever you want, watch whatever videos you want for that month, and then just make sure you cancel before your month is up. That's it.

Thanks for watching.

How do you become a member? Click on the Join button below the video. After you click the Join button, you'll 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'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll 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. 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. You'll be shown in each video as long as you're a sponsor. You'll 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.

But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.

Quiz

Q1. Which of the following is a primary goal of the help desk trouble ticket system described in the video?
A. To replace email as the way to track support tickets and organize them by priority and status
B. To send automatic emails to customers upon ticket resolution
C. To allow customers to submit tickets directly via a web interface
D. To integrate with phone support hardware automatically

Q2. What is an essential prerequisite recommended before building the help desk system in Access?
A. Knowledge of form macros exclusively
B. Understanding relationships, continuous forms, and relational combo boxes
C. Expert-level SQL programming experience
D. Knowledge of SharePoint integration

Q3. What is the function of the priority and status tables in the database?
A. To store user passwords securely
B. To provide selectable options in combo boxes for tickets' priority and status
C. To log all emails sent to clients
D. To store attachments for each ticket

Q4. In the ticketing system, what does the 'ticketDetailT' table represent?
A. It holds customer address information
B. It stores each individual service/action related to a ticket, such as calls or visits
C. It keeps a summary of open tickets only
D. It tracks software licenses

Q5. Why is it recommended to use 'billable hours' instead of just 'hours' in the ticketDetailT table?
A. 'Hours' is a reserved word, and 'billable hours' avoids naming conflicts
B. 'Billable hours' can only be stored as currency
C. Users cannot enter decimal numbers in 'hours'
D. 'Billable hours' automatically calculates overtime pay

Q6. What data type does the instructor recommend for most numeric fields in Access?
A. Byte for all numbers
B. Single and Decimal based on the situation
C. Long Integer for whole numbers and Double for floating point numbers
D. Text for simpler handling

Q7. What access property should be set to prevent users from deleting tickets?
A. Allow Additions to No
B. Allow Deletions to No
C. Record Locking to Exclusive
D. Filter On Load to Yes

Q8. In the system described, what is the purpose of a subform in the ticket form?
A. To display unrelated data from another table
B. To show and enter the details (multiple actions) taken on a single ticket
C. To collect user login information
D. To present reports for printing

Q9. How does placing fields like priority and status in separate tables benefit the design?
A. It ensures only one status per ticket system-wide
B. It makes it easier to maintain, update, and sort the available choices through combo boxes
C. It eliminates the need for queries
D. It allows track changes auditing automatically

Q10. What is the proper way to use conditional formatting for priority in the ticket list form?
A. Format based on the display text of the priority (e.g., "urgent")
B. Format based on the underlying priority ID to ensure consistency if names change
C. Format based on the customer name
D. Use formatting only for status, not priority

Q11. Why does the instructor create a query (ticketListQ) instead of using the raw tickets table for the ticket list form?
A. To enforce security permissions on tickets
B. To join and display related information such as priority description, status description, and customer names
C. To support web-based access only
D. To restrict edits to only completed tickets

Q12. When designing forms, why does the instructor recommend aligning the tab order so that the description field is first?
A. To match alphabetical order
B. To make data entry more user-friendly and efficient, as users typically begin with the description
C. To conform to Access default tab order
D. To reduce form load times

Q13. How does the system prevent accidental user deletions of important ticket data?
A. By hiding the delete button only
B. By setting Allow Deletions to No on relevant forms and subforms
C. By encrypting the database file
D. By disabling form load events

Q14. What is a benefit of giving sensible and descriptive names to form controls instead of generic ones like "Combo19"?
A. Generic names are easier to remember
B. Descriptive names make it clearer in VBA code what each control does
C. Form performance is improved
D. It is required by Access for all controls

Q15. What does the instructor recommend regarding code duplication when assigning actions to multiple controls?
A. Repeat the same VBA code block in each event handler
B. Place shared code in a subroutine and call the subroutine as needed
C. Avoid using VBA and use macros instead
D. Write all code in a module, not in forms

Q16. Why is using a query with joins between the ticket, priority, status, and customer tables advantageous for the ticket list?
A. It enables direct editing of all fields in the resulting form
B. It allows the display of user-friendly information, like names and descriptions, rather than just IDs
C. It automatically emails updates
D. It is required for VBA to work

Q17. What is the main reason for not allowing users to delete tickets or details, but instead marking them as "Canceled" or "Inactive"?
A. Deleting data is required for security
B. Maintains data integrity and audit trail for records
C. It makes the database file smaller
D. It auto-archives old tickets

Q18. When setting up combo boxes for priority and status, why is it useful to sort the priority list by the ID and the status list by description?
A. IDs are alphabetically arranged by default
B. Priority typically follows a logical order (e.g., low, medium, high, urgent), whereas statuses may not have a set order
C. Access requires queries to be sorted by ID
D. Description fields cannot be sorted in Access

Q19. What is the purpose of using default values for priority and status in the ticket entry table?
A. To force the user to always make a selection manually
B. To avoid null values and provide a sensible starting option for new tickets
C. To enable encryption of those fields
D. To ensure compatibility with SQL Server

Q20. What simple VBA command is used to open a specific form to create a new ticket?
A. OpenForm "ticketF" in Macro Designer
B. DoCmd.OpenForm "ticketF", , , , acFormAdd
C. DoCmd.ShowForm "ticketF", , , "New"
D. Forms!ticketF.Visible = True

Answers: 1-A; 2-B; 3-B; 4-B; 5-A; 6-C; 7-B; 8-B; 9-B; 10-B; 11-B; 12-B; 13-B; 14-B; 15-B; 16-B; 17-B; 18-B; 19-B; 20-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 video from Access Learning Zone covers how to build a help desk trouble ticket system using Microsoft Access. One of the viewers, Bryce, reached out and explained he's running a technical support call center with his team tracking everything through email, which, unsurprisingly, has become unmanageable. He wanted a database solution for handling tickets, organizing them by open or closed status, billing requirements, and especially by priority. For example, if a system is completely down, that ticket should be worked on before minor issues like poor sound from speakers.

Before getting started, there are a few prerequisites to keep in mind. Understanding relationships between tables is essential because this trouble ticket system requires several related tables. If you need a refresher, I recommend watching my free video on relationships available on my website and YouTube channel. Additionally, you should know how to set up continuous forms and use combo boxes that pull values from related tables. My free invoicing tutorial is also highly relevant, as building an invoicing system in Access involves many of the same concepts as a ticket tracking system.

Working with Access is much like working with Legos; once you know how the pieces fit, you can arrange them however you like. For this video, I'm starting with my free TechHelp database template. If you have followed my invoicing video, you should have a copy of this template already.

Now, let's look at the tables necessary for a ticketing system. Laying out your tables on paper, as I always advise in my Access Beginner 1 course, is a good habit. The structure here is clear. First, create a table for the overall ticket information—let's call it ticketT. This table's primary key is ticket ID (autonumber). It connects to customers using a customer ID (long integer), serving as a foreign key. The table also tracks when a ticket was created, with a suggested default value of Date (or Now if you require exact times), optionally a due date, and references to both priority and status through foreign keys. The helper tables for priority (like low, medium, high) and status (such as active, escalated, canceled, completed, invoiced, or waiting on the client) let you sort and manage tickets accordingly. Additional fields include a short text description and a long text field for detailed notes.

After saving the ticketT as your parent table, you need a related ticketDetailT to store event logs or actions tied to each ticket—similar to order detail line items in an invoicing system. Each ticket detail entry has an autonumber ID and a foreign key linking back to its parent ticket. It records the service date and time (defaulted to Now), description, optional notes, and the billable hours for that event. I usually use data types of long integer for IDs and double for hours billed, as those two types are suitable for most Access databases. By default, you could set hours to .25 for 15-minute increments, but you can adapt billing as required.

Sample data helps to visualize how these relate. If you check your customer table, ensure you have at least a few customers for testing. Add sample tickets for various customers with different priorities and statuses, and then tie corresponding detail actions to each ticket. For example, a technician can log a phone call, an on-site visit, or any follow-up required, noting how much time to bill for each.

The priority and status tables themselves are simple lookup tables with autonumber IDs and description fields. Typical entries in the priority table include Low, Medium, High, and Urgent. The status table includes Active, Waiting on Client, Escalated, Canceled, Closed, and Invoiced. It's important to note that I recommend never deleting tickets completely from the system. Instead, mark them as canceled or closed as appropriate. This keeps your records complete and accurate for later review or troubleshooting.

Because I'm leveraging my standard template, much of the ground work is already done. You can adapt forms built for orders and order details to manage tickets and ticket details. To do this, copy your order form and adjust it to use ticketT as its record source, updating field names and removing unnecessary controls.

Organize your main ticket form for usability. Place description, customer, and notes fields logically, and make room for the ticket detail subform to display associated actions. Add date fields for when tickets were created, when they are due, and when they were closed (the latter helps with tracking metrics). Give your form a distinct background color for ticketing, so users can visually distinguish between different types of forms in your database—color coding can make navigation easier.

For the ticket details subform, also adapt your order details form. Change its data source, relabel fields, and adjust controls so that you have service date/time, description, billable hours (set to show two decimal places), and notes available. Remove currency formatting if it was used previously, since hours are not monetary values.

Integrate your ticket details subform into your main ticket form by dragging and dropping. Use combo boxes for priority and status fields. The combo box wizard makes it simple to connect these fields to their respective lookup tables. When creating your priority combo, sort by the ID field so priorities show up in your preferred order. Similarly, for status, sort by description since statuses might not have a logical numerical order.

Set default values for priority and status—such as medium and active—so that new tickets start with sensible defaults. Adjust your form's tab order to streamline data entry for your users. I recommend bringing the description field to be the initial focus when a new ticket opens and arranging the tab order so that entering a ticket feels natural and efficient. Make sure your combo boxes and other controls have clear, meaningful names. For instance, use names like priorityCombo or statusCombo instead of ambiguous defaults like combo19 or combo21. This makes working in VBA and troubleshooting much easier in the future.

Disallow record deletions in both your ticket and ticket details forms to prevent users from inadvertently removing records. Instead, instruct them to set statuses like canceled or mark billable hours as zero if a detail item should be voided.

Add workflow support from your customer form. Place a button that opens the ticket form to a new, blank record for the currently selected customer. This cannot be set up with the standard button wizard alone—you need a bit of VBA. A few lines of code using DoCmd.OpenForm with acFormAdd will open the ticket form ready for new data. If you are unfamiliar with Access VBA, now is a great time to check out my introductory VBA lesson, which covers exactly the basics needed for tasks like this.

After setting this up, opening a new ticket from a customer's record becomes straightforward and keeps your workflow efficient. The default customer ID carries from the open customer form into the new ticket record, and everything is set for immediate data entry. This approach offers a cleaner, more reliable workflow than trying to hack it together with macros or more complicated wizards.

To help your technicians see their workload at a glance, build a summary query that ties together all the relevant ticket information using joins to your customer, priority, and status tables. Pull fields such as ticket ID, creation and due dates, priority and status descriptions (with user-friendly aliases), ticket description, and a combined customer name. This query is for viewing, not direct editing.

Create a continuous form based on this query for easy ticket listing. Repurpose your existing customer list form if you have one. Adjust field sizes, alignments, and labels so priority tickets, overdue items, and other significant details are clearly visible to your users. I tend to avoid layout view and suggest using design view for more granular control when moving and resizing controls.

Since ticket priorities and statuses are central to workflow, conditional formatting is a great tool here. For instance, if a ticket is marked as urgent, use expression-based conditional formatting on the priority ID field to highlight the ticket row in red, making it stand out. Doing this on the ID rather than the description preserves the rule even if you rename priority options later.

Enable quick access to tickets and customers by adding code to buttons or double-click events, allowing forms to open directly to the relevant record with a single click. Always consolidate frequently reused routines—such as opening a ticket—to a single subroutine in your VBA code for easier long-term maintenance.

Consider refining user permissions: for example, disallowing new tickets to be added (or tickets to be edited or deleted) from the main summary form. Direct users to initiate tickets from customer records instead for better structure and control.

Offer filtering and sorting—users can narrow the list to active tickets or reorder by urgency. In your query, sort by priority in descending order so urgent problems appear at the top. You can also add features to show only active tickets or to sort and group tickets as needed.

Many of these concepts are further expanded in the Extended Cut of this lesson, available to members. In the extended portion, I cover creating printable tickets, assigning tickets to technicians, advanced filtering (such as dropdowns to show tickets by status or assignee), and even implementing automatic billing where tickets for multiple customers can be invoiced at once. There are also advanced tips for customizing the template further, such as tracking billing hours at the customer summary level.

If you are interested in continuing to develop your skills, my website offers a full, structured course curriculum, including Beginner, Expert, and Developer classes for Access—as well as Word, Excel, and other Office topics. Membership options are available with different benefits, such as access to extended cut videos, downloadable templates, and priority support. For those who prefer not to commit long-term, templates can be purchased directly as standalone downloads.

Comprehensive, step-by-step video tutorials with everything mentioned here (and much more) are available on my website—see the link below to learn at your own pace.

Live long and prosper, my friends.

Topic List

Creating tables for a help desk ticketing system

Designing the main ticket table with appropriate fields

Creating helper tables for priority and status

Establishing relationships between tickets, customers, priorities, and statuses

Building a ticket detail table to log multiple actions per ticket

Adding default values for date, priority, and status in tables

Entering and working with sample data in related tables

Repurposing invoicing forms for ticket entry and detail tracking

Customizing form layouts and aesthetics for ticketing

Using subforms to display related ticket detail records

Setting up combo boxes to select priority and status values

Configuring form tab order and tab stops for better data entry flow

Restricting users from deleting ticket and detail records

Adding VBA code to open a new ticket form from the customer form

Setting default values in forms based on current customer

Building a query to summarize tickets with related data

Creating a ticket list form based on a summary query

Using VBA to open specific ticket records from the list

Implementing code reuse with subroutines for opening records

Adding a requery button to refresh the ticket list

Setting form properties to restrict editing, adding, or deleting in the ticket list

Linking customer information to tickets and enabling quick navigation

Creating code to filter ticket lists for specific customers

Sorting ticket summary by priority using query design

Applying conditional formatting on the ticket list by priority

Building forms for efficient ticketing workflow and navigation