|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Time & Billing By Richard Rost 
5 years ago
Invoice Your Customers For Time Spent. Tracking. In this video, I'll show you how to create a simple Time & Billing add-on to the TechHelp Free Template so that you can track how much time you spend working for each client and bill them accordingly. Fletcher from Madison, Wisconsin (a Platinum Member) asks: I'm using your TechHelp Free Template for Invoicing. Can you add something where I can keep track of how much time I've spent working for my client so I know how much to bill them for at the end of the month? MembersMembers will learn how to add a time clock to the billing form so they can click START and STOP buttons and have the billable hours calculated automatically. We'll also see how to bill in 15 minute increments.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Links
Keywordsmicrosoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, time and billing template, law firm billing, accounting, desktop time and billing, billable hours, invoices, invoicing
IntroIn this video, I will show you how to add time and billing features to your Microsoft Access invoicing database using the free TechHelp template. We will create a work table to track time spent on client tasks, set up a form for easy data entry, add calculated fields to show billable totals, and use VBA to keep your totals updated automatically. I will also demonstrate how to filter unbilled items and link the time tracking form to each customer in your database.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I'm going to show you how to add time and billing to your Microsoft Access Invoicing Database. Today's question comes from Fletcher in Madison, Wisconsin, one of my platinum members. Fletcher says, I'm using your TechHelp free template for invoicing. Can you add something where I can keep track of how much time I've spent working for my client, so I know how much to bill them for at the end of the month? Well sure, Fletcher, a lot of people like to keep time and billing in their database, separate from the invoicing. That way, every time you do something for a customer, whether it's a phone call or in my case, development work or training online or some research, you can put that in your, let's call it, work table. Then at the end of the month, you can bill them for all the stuff that's in the work table that you haven't billed them for yet. Let me show you how I'd set it up. So this is the TechHelp free template that Fletcher is using. You can download this for free off my website if you'd like to. I'll put a link down below in the link section. So let's start by creating a work table, where we'll put all of the actual work items in here. So we've got the work ID, auto number. We've got the customer ID, so we know who to bill it to. It'll be a number of type long integer, a description, of course, what kind of work was done, whether it's research or whatever. I like to throw notes in there. Now, rather than start time and end time, I like to put, when it comes to billing, a start date time and then billable hours. So for example, I know I started at, let's say, 9 a.m. on a Monday, and when I'm done, let's say I've worked for three hours. Maybe I only want to bill for two, two and a half, whatever. So I could put it in the start date time so I know when I started. Later on, when I'm done, I can look at it and be like, okay, I spent six hours on this, but maybe you don't want to bill them for the whole six hours. Maybe you took a lunch break in the middle or whatever. So you know exactly when you started, and instead of putting an end date and an end time in and letting Access calculate that, because if you want to change the billable hours, then you got to go in there and figure, okay, I got to back this up to 4:30, then no, just put the start date in there and how many hours you want to bill them for. That's how I like to do time and billing. In the more advanced classes in the extended cut, for example, I'm going to show you how to do a little time clock. So you can click on start and then stop and it'll actually keep tracking that for you. But for now, this is just fine. Start date time will make that a date time field and the default value will be =Now whenever you start it. Put the current system clock time in there. Then we'll do billable hours. So when you're done, you'll type that in. Now I'm going to make that a number. I'm going to make that a double. The default value I'll put in there is one hour. Maybe you got a minimum billing of one hour and then after that you bill in half hour increments. You can set it to 1.5, 2, 2.5 and so on. Now, some people bill different rates for different things. Like you might bill a phone call at one rate, research at a different rate, online training at a different rate, development at a different rate. So put your hourly rate in here. That'll be a currency. And if you want, default that. Let's say you normally bill $100 an hour, put 100 down there. But you could change it or if you're billing for different people, different staff, maybe you're a law firm and your lawyers bill different rates than your paralegals do. So you could change that as well for each client. And then, of course, you want to keep track of "has been invoiced." Yes or no? Have you actually invoiced for this line item yet? Let's save this. This will be our work T or work table. I like to throw some sample data in here real quick. So customer ID one, that's me. Maybe a phone call. No notes for this one. The start date time defaults to right now, it gets thrown in there automatically. Let's put one billable hour in there at $100 and it has not been invoiced yet. Same customer, a little bit later on, some research. Maybe I did half an hour of research at $75 an hour. Now we're going to make a form to put all this into, but first let's make a query that will do a little calculation for us. So create, query design. If you've never done calculated query fields, go see that video first. I got a video on that. I'll put a link down below. Go watch that if you've never done calculated query fields before. Also, we're going to be using the if function, the immediate if. I call it the if function. Go watch that video too. Now that you're an expert in both of those things, bring in the work table. We can close that, bring in all the fields from the work table and I want to sort this by the start date time descending. Not in the criteria field. I want to put that in the sort fields. So when I open up my form, the ones on top, I want to see the most recent items on the very top of the list. Now I don't want duplicates in my query. If I run this right now, I'll get a duplicate and it says field zero. To avoid duplicates, we just simply hide it like that. Now if I run it, you don't see the extra copy of that field. Let's save this real quick while I'm thinking about it as my work Q, my work query. Now what else do I want to put in here? Well, I've got the billable hours and I know the hourly rate. Let's put in the line total, which is the billable hours times the hourly rate. So now I can see the total for each item. One hour at $100 an hour. We don't have to worry about formatting that. We'll format it in the form. We'll make it look all pretty in the form. What's next in here? I want to know how much I have not billed yet for this customer. So we're going to say unbilled is going to be if, let me zoom in so you can see this better. Unbilled: if not [has been invoiced], line total, zero. What does that say? Well, if this hasn't been invoiced, that means it's unbilled. Make this value the line total. Otherwise, make it zero because it's been billed. Let's take a look at that. Now if I mark one of these as has been invoiced, that goes to zero. So I can easily see at the bottom of the column how much I haven't billed this customer for. Let's save that. Now let's go build the form. If you haven't watched the video in which I build this, the invoicing video, go watch this video first because in that video and in the one before it where I build the blank template, I create this continuous form. It's a blank continuous form, so we don't have to keep reinventing the wheel. So let's modify this continuous form. Actually, let's copy and paste it. Copy, paste. Control C, Control V, for those of you who don't know your keyboard shortcuts. Let's call this my work F, my work form. Now let's design this guy, design view. First thing we do is bind it to the work query. So come into all, drop this down, and find the work Q. Now this form is bound to that query we just built. So we can still edit the data, but we also get our calculated fields. Very important. Let's change up the colors a little bit. It has to do with time and billing, so I want to make it green for money. Let's go to format. Drop this down. Let's go with that green. Let's go a little darker though. Let's go into the palette and drag this down about there. That looks good. Click down here. Click the same color. Go into here. Click the same color. Let's make it a little bit brighter though. Go up to that green. That looks good. Copy the back color over the alternating back color so those are the same. I hate that alternating back color. Now I keep these in here just for fun, but we don't need those. Let's get rid of those. Go to design and add existing fields. We don't need the work ID in here. The auto numbers don't mean anything for this form. We don't need to see it. The customer ID, I'd like to bring in a combo box for the customer. We'll do that in a second. Let's bring in description. Note something to put down here. I'll bring customer up here so I don't forget to put the combo box on top. I always do. Let's bring in what else we've got, start date time. That's going to go up here first. The billable hours we need, obviously. The hourly rate, whether or not it's been invoiced, and then our line total and our unbilled. We'll bring those over here on the right. This is a big jumbled mess, isn't it? Look at that. So I'm going to get rid of all of these labels. I know there's a faster way to do all this, but I'm kind of old school. Let's get rid of that. So I'm going to start date time over here on the left, then my description. There's a certain satisfaction I get from laying this out by hand. Billable hours will go here. That doesn't have to be quite that big. Hourly rate will go next to it. Now, because of that, now I have my line total and my unbilled amount, and let's slide that over like that. Now these two guys, you can't edit those, so I like to tell the user visually. Let's make those gray. Let's go with that gray right there. Here's my invoiced way over here on the end. Let's adjust the tab order so that these guys are all in line. Tab order. Auto. If you want, you can even take these two out of the tab order. Right click. Properties. Then go to Other, and then Tab Stop. Say No. That way when you tab along, it doesn't even stop there because you can't change those anyway. I'm going to delete that label for the notes. And you know what I'm going to do? I'm going to steal some fields from other places. I'm going to change this. The customer ID is a combo box, and it's bound to a field called customer ID. I happen to already have one of those on my order form. Since you watch these videos, you know how I did all this stuff. So, design view. I'm just going to copy that combo box. Copy. I'm going to slide back over here and paste it in. Now I can delete that text box. See that? So I already have a combo box built for the customer. Let's slide this down. Move these labels down a little bit. Slide the customer over here like this. May make that a little bit bigger. Let's make this white. Format. Wrong one. Drop that down, pick white. I like to pick the standard colors, by the way. I don't usually use theme colors. I'm going to do the same thing with that notes field. I've already got a pretty notes field right here on my customer form. I like this notes field: the yellow look with the sticky paper shadow in the background. Copy you. Copy and paste. The fields are named the same, so I can get away with this. It's notes on this form. It's notes on the other form. This is where you'll put the notes about what this particular billing is for. Now let's make these labels pretty. Start date time. Slide this one over. Description. Now we need more of them for the other ones. Copy. Paste. Paste. Paste. I know there are fast ways to do this, but like I said, I like doing it by hand. We've got this as the billable hours, so let's go "Hours" - HRS like that. Remember, these are just the labels. These are what the person sees, so we can put whatever we want in these. Hourly rate. Let's put the line total here. We'll put unbilled next to it. You know what? For unbilled, we really don't even need to see this here. I want to have unbilled down below here, but we'll move that in just a second. Slide that over. We'll put over here "Inv" for invoice. Slide you right there. Let's see what we got. Open it up. Looks good. A little bit of formatting you can do here still. Let's right click, design view. Let's format this guy. What do I want to see in here? Let's do mmm dd yy hnn like that. It will be in 24 hour time, but that's what I want to see. Looks good. Let's left-align everybody in here. I know people want numbers and currency values to the right. I like everything left-aligned. That's just me. It's my preference. I almost never right-align stuff. I think it looks a lot better. I never bill in cents so I can make the rate in here at least. Under format, let's go $0, just like that, without any decimal points after it. Let's see what we get. Looks good. These might be formatted with that. You might want to bill in fractions. Right click properties, and we'll make the format for these currency. That just gives us the two digit decimal values. I keep going back to the customer form. While I'm thinking about it, let's do this. Let's go to the customer form. Let's put a button on here to open up the work form. I'm going to slide this guy over a little bit and let's copy one of these buttons, copy paste. Why are you not copying and pasting? Copy paste. My keyboard has been doing some weird stuff lately. Copy paste. There it goes. I found it took a second. Sometimes my video recording software does weird things when I'm copying and pasting. Let's change this caption to "Work." And let's give it a good name so Alex doesn't yell at us. Let's go to work button here. Work_BTN for the name of the button. Now, you could use the command button wizard and drop a button down here, but I'm going to do it my way. Right click, build event. That will open up the VB code window. You might get a message up saying which builder would you like. The code builder. If you haven't watched my intro to VBA class, go watch that. VBA is not hard. Don't let it scare you. DoCmd.OpenForm. What form are you opening up? The work F. That's it. Now you can come in here and say comma, comma, comma. Where the customer ID equals the customer ID in the current form. That will open up just the work items for this particular customer. Don't let VBA scare you. It's not hard. Once you know how to do that, it's actually faster than using the wizards. It is, and you get better results. Now save your changes. Yes. Now if I come in here, open up work, I get just the work for this particular customer. I personally hate these little pop-up date pickers. I don't like them. They float over stuff. I turn them off usually, especially since this start date time right here is going to default when we want to create a new one to the current date and time. So if you want to change it, you can click in here and change it. But I usually turn off those date pickers. So I'm going to go under format and show date picker. I'm going to say never. That's just my preference. Again, you can do whatever you want if you like to use those things. There we go. Research, phone call. What's this one going to be? Court room time. Three hours. For that, I bill $150. See, there you go. Let's do some form footer totals down here. Again, I got videos on form footer totals. Go watch those if you've never done a form footer total before. Right click, design view. I am going to copy the total and the unbilled, copy, paste them down here in the footer. See how I do that? Copy them and then paste them down here. Now for line total, this is going to be equals the sum of the line total. And I'm going to make its name sum line total. And I'm going to do the same thing for the unbilled. Equals the sum of unbilled. Now you have to have that in the query for you to be able to do a form footer total down there. Let's make this guy, I knew I was forgetting somebody: sum of unbilled. Save that. Now if you go into work, you can see how much you have to still bill this guy for. Now if you click on one of those here, remember you have to move off the record for it to update the form footer totals. You can put a Me.Requery in there if you want to. Again, a little tiny bit of coding. I teach all this stuff in my developer classes. But the problem is that the calculations don't update until the record is refreshed, which means you have to move off of the record to another line item. But if you want to make it force it to refresh, whenever you change any of these things, all you have to do is go into that individual control, go to events, go to after update, and then in here, say Me.Requery. That's all you have to do. See, it's not hard. VBA, oops, I hit the maximize button by accident. It's hard if you're like me and you're clumsy. But now if I come in here, as soon as I click that button, it refreshes the calculation. See that? Down on the bottom. Look at that. See, 137.50, 487.50. You can do the same thing with these guys too. You can change just to two. You can have it immediately update down below. I do that a lot for all of my order entry stuff. After update event right there, Me.Requery. That way it doesn't confuse people. Same thing with the hourly rate. If you change that, Me.Requery, Me.Requery. Always put those in there because it's confusing. You get someone who doesn't know Access. You're used to Excel, for example. If you're used to treating this like a spreadsheet, if you type in one and tab off of it to the next one, you want to see your totals update. Well, Access doesn't work that way. Access only updates calculations in the form footer when the record is saved. And the record is not committed. If I come here and do that, see that little pencil? That means this record is dirty. Dirty means it hasn't been saved to the table yet. If I hit escape right now, nothing happens. It doesn't save it. If I come over here and put a six in there and hit escape, it doesn't save it. So if I hit six now and hit tab, now it updates because I got the Me.Requery in there. See? It's these little teeny tiny enhancements that learning VBA can take your database. It can take you from a beginner Access user to an advanced Access user with very little work. A couple little things to enhance your databases. It's all the stuff I teach in my developer lessons. Now, I know right here at the end of the month, I can open the stuff and see, OK, you got $975 that I have to bill you for. So I can mark these invoiced. Then I can come over here, orders. I can add a new order. Billing. Then you put in here like billing for March 2021. $975, whatever they owe you. You got to put the totals down here, what you billed them for, all that stuff. Now, you might want to have it so when you come into here, you only see the stuff that is not billed. How do we do that? Well, I put a little checkbox right here that says show all. Normally, when you click on this, you want to see the stuff that you haven't invoiced for yet. So watch this. Design view. I'm going to put a little checkbox in here. We're going to call this guy show all. Show all means I want to see everything versus the norm would be just showing me the stuff that's not invoiced yet. Make this guy called show all. Default value will be no. Now, tiny little bit of work in here. Right click, build event. Right here, I'm going to say, if show all, then do that. Show me all of the items for this particular customer. Otherwise, I only want to see the items for this customer that have not been billed yet. Got to do a little bit of SQL to do this. I'm going to copy that, paste it down here. Here's the where condition. If you've never done any SQL before, watch my SQL videos. Again, I'll put some links down below for you. So the where condition right here, customer ID equals whatever the customer ID is in the current record, three for example. And has been invoiced equals false. That's it. So even SQL isn't that scary once you know what you're doing. Save it. Close it. Close it. Customer work. Look at that. I'm not seeing anything because it's only showing me the stuff that hasn't been invoiced yet. Unless I click on show all, then it'll show me everybody. See that? So it's time to go add some more stuff. I'll see. I started today at, I'm going to probably make this a little bit bigger here. Let's make this bigger. Slide this out a little bit so we can see the whole thing. I'll put the AM and PM back on. Not a lot of people like 24 hour time. So let's go in here, format. Let's put the AM PM on the end there. Why not? Looks good. Let's see. I started today at 9 AM. We did some research. Six hours of it at $75 an hour. OK, and that's it for the day. Go away for the day. Come back tomorrow. Open up Mr. Rost. Go back to his work. See that? Yes, you could make a button on your main menu if you want to, to click on it to see everybody who has to be billed. The sky's the limit once you understand how to do all this stuff. But now we know how to go into the customer form, open up the work form here so we can see what we've done. Let's say the next day we come in here, we did a meeting. We met for an hour and a half. I bill $130 an hour for that. There we go. Now at the end of the month, I can quickly see down here how much I have to bill this customer for. There you go. There's your basic time and billing. Of course, lots more to come in the extended cut. Want to learn more about time and billing? In the extended cut for members, I will show you how to add a start and stop timer right on the work form. When you click start, the little timer will start up, and it will count up seconds. This will allow you to automatically track how much time you spend. If you take a break, you go get lunch, whatever, click the stop button. You can come back later, hit start again, start the timer up. I'll show you how to lock all the controls on the form so you can't manually edit them while the timer's running. That's important sometimes. Then I will also show you how to round up the hours billable to 15 minute increments. So if you've worked for an hour and five minutes, we'll round it up to an hour and 15 minutes. That's all covered in the extended cut for members. Silver members and up get access to all of my extended cut videos. Gold members can download this database. I also have a time and billing seminar available on my website. In addition to what's covered in the extended cut, I go over lots more, including billing all of your customers that have unbilled hours with one click. It will automatically generate invoices for them, and then you can batch print them or email them all out. That's all covered in my time and billing seminar. Also available is just a template. I'll put a link down below for you. How do you become a member? Click the join button below the video. After you click the join button, you'll see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I've built in my TechHelp videos, plus my code vault, where I keep tons of different functions that I use. Platinum members get all the previous perks, plus access to my full beginner courses and some of my expert courses. These are the full length courses found on my website, and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. These free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more. If you liked this video, please give me a thumbs up, and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted. Click on the Show More link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level One course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel. And if you like Level One, Level Two is just $1. And it's also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page, and you can send me your question there. Click here to watch my free Access Beginner Level One course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. Why does Richard recommend keeping time and billing records in a separate table from the main invoicing records?A. To keep billing history private from clients B. To avoid mixing billed and unbilled work, and to easily track and invoice work not yet billed C. Because Microsoft Access cannot manage both in one table D. So clients can edit their own billed items Q2. In the work table for time and billing, which fields are crucial according to the video? A. Customer ID, Description, Start Date Time, Billable Hours, Hourly Rate, Has Been Invoiced B. First Name, Last Name, Address, Invoice Number C. Project Deadline, Client Feedback, Payment Term D. Task Priority, Department, Project Code Q3. What is the main benefit of entering only a Start Date Time and Billable Hours instead of calcualting with start and end times? A. It makes it harder to track when work was done B. It simplifies billing when you want flexibility, such as not billing for lunch breaks C. It prevents clients from seeing exact work times D. It allows clients to approve times before billing Q4. When setting up the 'Start Date Time' field, what default value is recommended? A. "01/01/2020" B. The current date and time using the =Now function C. 12:00 PM every day D. A blank value to be filled later Q5. Why is the 'Billable Hours' field set as a double data type? A. To store customer phone numbers B. To allow billing in fractional hours, such as 1.5 or 2.5 C. Because currency fields are not available in Access D. To ensure only whole numbers are stored Q6. Why might you set different hourly rates for different types of work or staff members? A. To differentiate between client priority B. Because Access requires unique rates for every record C. So you can charge appropriate amounts for various service types or levels of staff experience D. To hide actual rates from clients Q7. How is the 'Has Been Invoiced' field used in the time and billing database? A. It indicates whether a client has paid B. It tracks which work items still need to be billed on an invoice C. It is used to archive old records D. It records who performed the work Q8. What is the purpose of creating a calculated field in the query that multiplies billable hours by hourly rate? A. To generate a description of work B. To calculate the line total for each work entry C. To filter only completed work entries D. To display dates in a readable format Q9. The 'Unbilled' calculation in the query uses an immediate if (IIf) function. What is its purpose? A. To identify which work items do not have customer information B. To show the line total if an item has not been invoiced, otherwise show zero C. To combine notes and description fields D. To restrict edits to certain users Q10. Why does Richard recommend hiding the work ID and using a combo box for the customer ID on the form? A. The work ID is unnecessary for users, and a combo box provides a user-friendly way to select customers B. Combo boxes are faster to use than text boxes C. Work ID is required for calculations D. Combo boxes increase database security Q11. Why are some fields, like 'Line Total' and 'Unbilled', formatted as gray and removed from the tab order on the form? A. To save ink when printing B. To indicate that these fields cannot be manually edited and streamline data entry C. To make the form more colorful D. To allow users to edit them more easily Q12. What does the Me.Requery command do when placed in the After Update event of controls such as Billable Hours or Hourly Rate? A. It deletes all the data in the form B. It refreshes the form to instantly update calculated totals like form footer sums C. It prevents users from changing any data D. It logs the current user out Q13. What happens to the sum totals in the form footer if you update a field but do not move off the record or force a requery? A. The totals update automatically B. The totals do not update until the record is saved or the form is requeried C. The form prints automatically D. The database warns the user of unsaved changes Q14. What is the benefit of adding a 'Show All' checkbox to the work form? A. It allows users to display both billed and unbilled items for a customer B. It enables exporting data to Excel C. It increases the database speed D. It hides confidential notes Q15. What feature is previewed for the extended cut for members, regarding time and billing? A. Adding a timer to record start and stop times for work, and rounding bills to the nearest fifteen minutes B. Integration with QuickBooks C. Emailing invoices to clients D. Generating 3D charts Q16. According to the video, how does learning a little VBA help Access users? A. It slows down the database B. It allows for custom enhancements like auto-refreshing totals and creating dynamic buttons C. It removes existing features D. It is required for basic data entry Q17. Which field or feature ensures you can see only the unbilled work for a customer by default? A. Filtering using 'Has Been Invoiced' is False in the where condition or query criteria B. Using an alphabetical sort on Description C. Removing the date field D. Grouping by customer address Q18. Why does Richard recommend turning off the popup date picker for the Start Date Time field? A. The date picker can obscure fields and is unnecessary since the field defaults to current date and time B. Access cannot support date pickers C. To prevent accidental date selection D. Because clients dislike date pickers Q19. What is the recommended way to access work items for a specific customer from the customer form? A. Use a dedicated button to open the work form filtered for that customer ID B. Manually search for work items every time C. Open the work table in datasheet view and filter manually D. Use macros to import data from Excel Q20. What is the main enhancement possible with the developer tips shown in this video? A. Ability to batch print all records B. Real-time updating of totals and customized workflows through VBA and event handling C. Import of data from incompatible file types D. Automated credit card payments Answers: 1-B; 2-A; 3-B; 4-B; 5-B; 6-C; 7-B; 8-B; 9-B; 10-A; 11-B; 12-B; 13-B; 14-A; 15-A; 16-B; 17-A; 18-A; 19-A; 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. SummaryToday's video from Access Learning Zone focuses on adding time and billing functionality to your Microsoft Access Invoicing Database. Many of you have asked how to keep better track of the hours you spend working for a client, so you can easily invoice them at the end of the month. Let me walk you through how to set up a simple, practical system for this in Access.First, rather than tracking time directly in your invoice table, it's best practice to create a separate work table. Each record in this table will represent an individual work item for a customer, such as a phone call, research session, online training, or any other billable activity. The work table should contain fields for a unique work ID, the customer ID to link back to who the work was done for, a description, notes for any additional information, the start date and time, the number of billable hours, the hourly rate, and finally, a field to indicate whether this item has been invoiced yet. For tracking hours, instead of storing both a start time and an end time, I prefer to use just a start date/time along with a billable hours field. This avoids complications when you want to bill only for part of the time spent or need to make manual adjustments. For example, you might start a task at 9 a.m., work for six hours, but only intend to bill for five to exclude your lunch break. By entering a start time and the precise billable hours, you maintain control over your billing. Hourly rates can differ between activities—such as phone calls, research, or online training—or between different personnel if you are, for example, a law firm with several staff levels billing at various rates. The hourly rate field should be set accordingly, with a default as appropriate, but always open for manual changes. A Yes/No field, "Has Been Invoiced," helps you keep track of which work items have already been included in an invoice. Once your table is designed, input some sample records so you can see your system in action. With your records in place, the next step is to build a query that performs calculations for you, such as multiplying billable hours by the hourly rate to get a line total. Additionally, it's helpful to use an immediate if function to calculate which items are unbilled. This field should display the line total if the item has not been invoiced, or zero if it has. From this point, you can build a form based on your query. A continuous form layout lets you view and edit multiple work records at a time. You can bring in a customer combo box to easily select which client the work belongs to, fields for start time, hours, rate, and your calculated totals. Since you do not need to edit the calculated totals directly, you can visually indicate that those fields are read-only, perhaps by using a different color. If you want to further enhance usability, adjust the tab order so the form follows a logical sequence, skipping over the non-editable fields. Additionally, you might want to copy nicely formatted notes or customer selection combo boxes from other parts of your database if you already have them set up to save design time and maintain consistency. You can add some finishing touches by formatting dates and currency the way you like. For me, aligning everything to the left provides a cleaner look, though that's simply a personal preference. Next, I recommend setting up a button on your customer form for quick access to each customer's work records. The button's code should open the work form filtered for just that customer. This is a great opportunity to experiment with a little VBA; opening a form for just a specific set of records is a task that VBA makes fast and simple. To complete the system, add footer totals to your work form. You can do this by creating controls in the form's footer section that sum the line total and the unbilled amount, letting you quickly see at a glance how much remains to be billed to each customer. There are a few quirks in Access to be aware of. For example, calculated fields in the form footer do not always refresh immediately when you edit records. To remedy this, use the after update event of relevant controls to requery the form. This will update your total calculations instantly after a change is made, which is especially valuable for users transitioning from spreadsheet tools like Excel, where calculations recalculate automatically. As you use this system, you can see how much unbilled work has accumulated for each customer, and at the end of the month, simply mark items as invoiced as you bill them. You can even further refine the form with a "Show All" checkbox, which allows you to switch between seeing all records for a customer or just those that have not yet been billed. This is accomplished with a small amount of SQL to filter the form's record source according to the value of the checkbox. If you wish, you can also group totals by customer or create buttons on your main menu for an overview of all outstanding work across your client base. For those interested in taking this system further, the Extended Cut for members demonstrates how to implement a start/stop timer for work sessions, preventing manual edits while the timer is running and automatically tracking billable hours. There is also instruction on rounding up billing increments, for example to the nearest fifteen minutes, and locking fields during live tracking. If you need more advanced features, the time and billing seminar covers batch invoicing, printing, and emailing invoices for all customers with outstanding billable hours. Remember, membership levels are available with a range of benefits. Silver members and above gain access to all extended cut videos and live sessions. Gold members get downloads including all sample databases and access to a vault of useful functions. Platinum membership includes all prior benefits, along with the full suite of beginner and expert courses across Access, Word, Excel, Visual Basic, and more. As always, my free TechHelp videos will keep coming as long as you keep watching. If you found this lesson helpful, please like and comment with your thoughts or questions. Subscribing to my channel is free, and you can enable notifications for updates. Additional learning materials, videos, and resources can be found on my website; make sure you explore those for deeper dives on topics like calculated query fields, using the immediate if function, and working with SQL in Access. If you're new to Access, my Level One course is over three hours long and covers the basics of building databases from scratch, and it is available for free. Level Two is just a dollar—or free with any channel membership. If you want your question answered in a future video, visit my TechHelp page and send it in. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListCreating a work table for time and billing entriesDesigning fields for start date, billable hours, and hourly rate Adding a "has been invoiced" flag to track billing status Inputting sample data into the work table Building a query to calculate line totals for billable work Using a calculated field for unbilled work totals Sorting work query results by most recent date Creating a continuous form for work entries Binding the form to the work query Formatting and rearranging fields on the continuous form Adding a combo box for customer selection Bringing in a styled notes field for work descriptions Applying currency and date/time formatting to form fields Configuring labels and display order on the form Implementing form footer totals for billed and unbilled amounts Disabling tab stops on calculated fields Linking the work form to the customer form with a button Using VBA to open customer-specific work entries Disabling date pickers on date/time fields Adding after update Me.Requery events to refresh calculations Using a checkbox to toggle viewing all versus unbilled items Filtering the form based on billing status using SQL Editing form design to improve usability and readability |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access time and billing template, law firm billing, accounting, desktop time and billing, billable hours, invoices, invoicing PermaLink Time & Billing in Microsoft Access |
