Accounts Payable 2
By Richard Rost
9 months ago
Build an Accounts Payable Database in Access, Part 2
In this Microsoft Access tutorial I will show you how to build a payee query to display either company name or contact name for payees, design a bill table to track accounts payable details, and create a continuous form with a combo box for selecting payees. This is part 2.
Members
There is no extended cut, but here is the file download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, payee query, payee combo box, customer-payee relationship, conditional display company name, IIF function, IsNull function, string concatenation, bills table design, payeeID field, continuous form, binding form to table, combo box sort, payment tracking, form footer notes, self-join relationship
Intro In this video, we'll continue building our Microsoft Access accounts payable database by creating a payee query that intelligently displays either a company name or a person's name, setting up a bill table with all the essential fields to track amounts and payment dates, and adding sample data for testing. I'll show you step-by-step how to design a continuous form to view and enter bills, use a combo box for selecting payees, arrange and format form fields, and manage notes for each bill. This is part 2.Transcript Today's part two of my accounts payable series. If you haven't watched part one yet, what are you doing here? Go watch part one and then come on back.
Alright, so in part one, we updated our customer form so that customers can also be marked as payees or not. We set up a payee category list so we can say what category that payee belongs to. That's great.
Now, we're going to be building more forms and reports and such where I need to see what the payee name is. Who is the payee? Some of your payees might be people. In fact, let's pick somebody here. Let's do John Luke as a payee, and let's say he's our landlord. He pays him rent. But he doesn't have a company name. I was going to put Borg, Angther or something. No, we need a couple people in here that are payees that don't have names. We'll do Will Ryker too. Will Ryker is communications. So we've got a couple people in here who have company names and a couple people in here who do not.
I am going to make a query called my payee Q. It's going to have a list of their IDs and the name. The name that's going to display, for example, on a report or in a combo box.
Let's go to Create - Query Design. I'm going to close this stuff, and I'm going to bring in my customer table. We need the customer ID. Now, I'm going to bring in first name, last name, and company name separately because you might want those things separately. Later on, we might add more fields, like the address and stuff, when it comes to sending out whatever reports we have to send out. For now, this is fine. I can always add to it later.
Right here is where I'm going to put my calculation that determines whether to display the company name or the first and last name. Here's what I'm going to say: if the company name is null or is blank, then I want to display first name, last name; otherwise, display the company name.
I have a whole separate video that goes over this in a lot more detail and shows you how you can do reports that have both on there or just one, and you get rid of the empty spaces where you don't need it. If you want more details on this, go watch this video. We're going to use a simple immediate if function, IIF.
We're going to come into this field here. I'm going to go Shift+F2, zoom in, so I can see it in more detail. We're going to call this the payee name: and I'm going to say IIF(IsNull([CompanyName]), [FirstName] & " " & [LastName], [CompanyName]). If the company name is null, I want to see first name and last name. Otherwise, if not null, show me the company name.
I've got separate videos for how the IsNull function works, and this is called string concatenation. I've got separate videos on all of these things. If you don't know how this stuff works, I'll put links to all this down below in the description in the links section.
I'm going to hit OK. That will put that back there. Let's run this bad guy and see what it looks like. Perfect. There we go. There's the payee name.
Now, I'm seeing everybody in here. So, the payee query should probably say, okay, this person has to be a payee. Let's go to Design View and let's find "IsPayee" right there. I'm going to bring this closer to the front, and we're going to say the criteria for this is True. If I run it, there we go.
Let's add a sort. Let's sort it by the payee name. We'll come over here and find Sort and drop that down. I like to bring the sorted fields up to the front, so we'll do that. Let's run it. Looks perfect. There we go. There's our list of payees that we can now use in our queries and in our forms and in our reports. There's the payee name.
Let's save this as the payee Q. Let's go ahead and shut this down.
Now, we have enough information to create our table of bills. Every time we get a bill, we get a bill in the mail or it's emailed to you. Or do they still send bills in the mail? I think I still want to know how I get them. Every time I get a bill in the mail, I'm like, what are you doing? Don't you have a paperless option? But anyways, you get an email or whatever.
We need a place to store all that information: who you owe money to and when. Let's create a table, Table Design. This will be our bill table. We've got a BillID, and this is the AutoNumber. Who do we owe this money to? PayeeID. Now, you can call it the CustomerID if you want to. It's up to you.
It's okay to use different names that represent the same thing. For example, sometimes you might do a self-join relationship where you've got a person and then their parents so that you have the mother and the father. The table might be called PersonT and the person is a PersonID, but in that person's record, you might have a MotherID and a FatherID. Those both reference back to persons in the person table. It's a self-joining table, self-joining relationship. I have a whole separate video on how that works. I'll put a link down below if you're curious and want to learn more. But you can have basically the same item related to another table, but it's really the same thing.
So, a PayeeID is a customer. You just have to know because Access won't always see that and think it's the same thing. It might not make relationships and joins for you automatically. You'll have to manually join those, and we'll see how that happens soon.
This PayeeID is a Number of type Long Integer. Sometimes, maybe over here in the notes, you could put "this is a CustomerID" if you want. That's fine.
AmountDue is a currency value. Now, we've got a bunch of dates. We've got the BillDate - that's the date the bill was issued. You can put IssuedDate or InvoiceDate. I like to track the EnteredDate - the date/time that you actually put it in the system. This is really good if you've got a multi-user setup because you really want to know: when did Jane in Accounting actually put this in here? Because this wasn't in here yesterday and it's due today. I'm going to come down here for the Default Value and make that =Date(). It will put today's date in there. If you want it to the minute, put =Now() for the date and time.
Next up, we've got the DueDate. When is this thing really due? You could put a default value in here if you want, like 30 days after the bill date. That's completely up to you. I don't usually bother because I like to manually type those in unless it's something that I know on a regular basis.
Now we're also going to put a PaidDate in here. That's how we're going to determine when a bill is paid. If that value is null, that means the bill is unpaid. If there's a date in there, that means we paid it.
I do this slightly differently in my full payables seminar because we actually have a bill table and a payment table, so you could make multiple payments on a single bill. Let's say a contractor sends you an invoice for $5,000 and you send them a check right now for $1,500, and then a check a week from now for $1,000, and then the balance later. You can have three payments that are tracked to a single bill. It's a little more complicated. I cover that in the full payables seminar. For today, we're just going to keep it simple.
Any notes you want to put in here go in "Long Text." Save this as our BillT. Set the primary key. Yes. There we go.
I always like to throw a couple sample records in here. So, let's go to Datasheet View. Now we need to see what our payees are. I'm going to open up the PayeeQ and we've got a list of payees.
The first payee is going to be payee 1 to Computer Learning Zone for $100, and the bill date was, let's say, June 1st. See how nice the ISO dates are? You know exactly what that is. The entered date is right there. Let's say it's due on 7/1 and it's not paid yet.
Next one: we've got the rent from PayeeID 4 and that's $2,000. The bill date was 6/2, it was entered today, and it's due on 7/2.
See how this works? I usually only put a couple of records in the table directly. The rest of it I'm going to put into the form. I just like to have at least a couple of records here so I can see it when we build the form.
The form is next. I'm going to build this as a continuous form, just like the customer list here. That's a continuous form. I have a blank continuous form down here in my template. I'm just going to copy and paste that (Ctrl+C, Ctrl+V) into the BillF.
Open that up. There we go. That's all that's in there. Design View. Make it a little bit bigger.
First thing I do is bind this form to a table or query. We're going to open up the form's properties, go to Data, find the record source, and you can bind it right to the Bill table. Save that. Let me close that for now.
Now I'm going to go to Form Design, Add Existing Fields, and I'm just going to grab everything and drop it over here in the detail section for now. I'm going to click on the first one, Shift+Click on the last one, let the shift key go, and then click and drag this whole mess and drop it right there.
Now, delete all of these labels. Just draw a box that touches them all, hit Delete, and they're gone. You'll see why in a minute.
The BillID - do you really need that on the form? That's kind of up to you. I usually don't, but I leave them for teaching purposes so people get to understand how these things work. So, yeah, we'll keep that one for now. We can delete it later.
Now, I have this gray guy here just so I can steal its format. So, I'm going to click on that and hit the Format Painter. If you don't have it on your toolbar, it's under Format over here, and then the Format Painter is right there. I put it up on my Quick Launch toolbar because I use it all the time.
Now that I've got the BillID, we can get rid of you. This could probably be a little bit smaller, and I'm going to left-align it, which I think it already was. Slide that up into place.
Next is going to be the PayeeID, but I don't want the ID there. I want a combo box. I want a combo box with my list of payees in it. Fortunately, we have a list of payees right there, so let's just grab that.
Go to Form Design. Find the combo box. Drop it right here. The wizard starts up. Get the value from a table or query.
Next, where does your list of values come from? Queries - PayeeQ. That's why we made it.
Next, what fields do you want in the combo box? Really, all I need is the CustomerID and the PayeeName. Everything else at this point is meaningless.
Next, sort it by PayeeName. I know we have a sort in the query, but the combo box will override that.
Next, this is what it looks like. Now, you don't see the little checkbox there that says "Hide the key field" because this is based on a query, not a table. So we have to just manually hide that CustomerID.So grab this little bar here and drag it all the way to zero. If you're not sure if you're at zero, drag it past it so you make sure that the width of that column is zero. Make this as big as it needs to be. All right.
Next. Now, which field is the important one? What's the field that we need the data from? I need the customer ID. Next, and I'm going to store that in the PayeeID of the bill table. See how that works? We're picking a customer ID and we're storing it in PayeeID.
Next, what label would you like? Doesn't matter. We're going to delete it in a minute and then hit Finish. Delete that label. We can get rid of this guy. Now I'm going to slide that combo box right there and make it as big as you think it needs to be. That's probably good right there.
Let's save it. Let's take a peek at what we got. I'm going to go to Form View. Again, I have all these buttons on my Quick Launch bar. You can right click and go to Form View if you want. There we go. There's my combo box with my payees in it. See that?
Now, it's just basically moving these guys into place. We can get rid of PayeeID. We don't need it. Goodbye. We got the Amount Due. Let's see how wide these are. Let's take a peek. That's good. That's good. We can make these a little bit shorter.
I really don't like Layout View. There's this thing called Layout View that you can now resize in. I'm very iffy with Layout View. I've had it cause problems. Usually it only causes problems when you've got VBA in here and it messes things up. So I honestly stay away from it. That's just me, a personal thing. I know a lot of people love it. But I'll go back to Design View and resize it and make a guess. Then I'll take a peek at it again in Form View. That looks good.
That's just me. I know so many people tell me there is nothing wrong with Layout View. I use it all the time. I've had problems with it. Now we're just lining all these up like so. I can close this guy.
I like to have all my stuff left aligned. That's again another one of my preferences. If you want them right aligned, that's fine.
Now with Notes, we don't need to see the notes for every single record. So I'm going to take the Notes and move them down into the Form Footer like that. That way, it just shows up once on the bottom of the form. You will just see the notes for the record that you're on.
Shrink up the Detail section by grabbing it right there. I like to apply a specific format to my notes fields and watch this. I already have one and it's already called Notes. So I'm going to delete that one. I'm going to go back to my Customer Form. See this Notes? I got it. It's yellow with the shadow. I love that. That's just how I like to do my notes.
So I'm going to come in here, I'm going to click on this, and I'm going to copy it to my clipboard. Come over here, click in the Form Footer, and paste. There's Notes and it's named the same thing. So it's going to work as long as the fields are named the same thing. You can do that, and if not, you just change the name.
All right. So there's the notes for that bill if you want to put notes in there. Save it. Close it. Close that.
Let's open up our bill form and see if it's looking pretty good. Looking pretty good. If I put some notes in here, these are notes for the record that you're on. So Luke doesn't have notes. That one is for Computer Learning.
Now we just have to do our labels across the top. Design View. I like to be lazy sometimes, especially when I know that I'm not going to be making too many changes. So I'm going to get rid of this second label. I just stick everything in one label. Watch this - stretch this label all the way across.
Right here you've got your ID, and then just use the space bar. There's the payee, hold down the space bar. All right. Amount, there we go. Due, enter to. That was the bill date. This is the due date, and this is the paid date. For this one over here, just go back. Issued or whatever I have to call it.
It's good. Save it. Close it. Open it. Beautiful. Took a guess.
All right. So that's going to do it for part two, folks. Tune into my Rosane bad time, same bad channel for more. We got lots more to cover. If you're a member, you can watch it right now because I'm still recording, so I'm going to get them posted as soon as I can.
Once again, if you're interested in a complete payables database with lots more features, including recurring billing and all kinds of stuff, then check out my payable seminar.
But that's going to do it for part two. That's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part three.
TOPICS:
Creating a payee query to display correct payee names
Using IIF to show company name or person's name
Adding IsPayee criteria to filter payee list in query
Sorting payees by name in query
Saving and using the payee query for forms and reports
Designing a bill table with fields for payables
Setting appropriate data types for bill fields
Assigning default values for date fields in the bill table
Distinguishing between bill date, entered date, due date, and paid date
Using null values in PaidDate to track payment status
Adding sample records to the bill table for testing
Building a continuous form to display bills
Binding the form to the bill table
Adding and arranging fields on a continuous form
Replacing PayeeID field with a combo box for payee selection
Configuring a combo box to use a query as a source
Hiding key fields in a combo box
Storing the selected payee into the bill record
Formatting and aligning controls on the form
Moving notes field into the form footer
Copying formatting for notes field between forms
Creating labels for form fields in the header
Testing and reviewing the finished bills form
COMMERCIAL:
In today's video, we're continuing with part two of the accounts payable series. You will learn how to create a payee query that displays either company name or first and last name when appropriate, set up a bill table to track who you owe, how much, and key dates like when the bill was entered, issued, due, and paid. I'll show you how to build a continuous form with a combo box to easily select payees, organize your fields for better data entry, and format your forms for clarity and function, including how to manage and display notes for specific records. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the primary purpose of creating the 'PayeeQ' query in this tutorial?
A. To calculate the total payments made to each payee
B. To display an appropriate payee name for use in forms and reports
C. To show only company payees, not individuals
D. To generate invoices for each customer
Q2. Which function is used in the query to determine whether to display the company name or the first and last name as the payee name?
A. IFERROR
B. SWITCH
C. IIF
D. CHOOSE
Q3. What is the reason for checking if the company name is null when building the payee name?
A. To filter out all records where the company name exists
B. To ensure individuals without a company name display their personal names
C. To set all null fields to a default value
D. To prioritize alphabetical order in reports
Q4. What field is used to limit the 'PayeeQ' query so that it shows only payees?
A. IsCustomer
B. IsActive
C. IsPayee
D. IsCompany
Q5. In the bill table, what is the purpose of the 'EnteredDate' field?
A. To indicate when a bill should be paid
B. To record when the bill was physically received
C. To track when the bill was entered into the database
D. To store the payment method used
Q6. What type of data is best suited for the 'AmountDue' field in the bill table?
A. Short Text
B. Date/Time
C. Currency
D. AutoNumber
Q7. Why might you sometimes use different field or table names like 'PayeeID' instead of 'CustomerID'?
A. To make it harder for users to find data
B. Because Access requires unique names for all linked fields
C. To clarify the specific role or relationship of a field in different contexts
D. To enforce referential integrity automatically
Q8. How does marking a bill's 'PaidDate' field as null affect processing in this setup?
A. It indicates that the bill is overdue
B. It means the bill is not yet paid
C. It marks the bill for deletion
D. It signals a recurring bill
Q9. What is the suggested way to create a drop-down list of payees in the bill entry form?
A. Use a value list typed directly into the combo box
B. Use the PayeeQ query as the source for a combo box
C. Use a subform linked to the customer table
D. Manually enter payee names into a text box
Q10. When building the combo box for selecting a payee, what field is actually stored in the bill record?
A. PayeeName
B. ContactNumber
C. CustomerID (stored as PayeeID)
D. CompanyName
Q11. What is the benefit of moving the 'Notes' field for bills to the form footer rather than showing it in each record on a continuous form?
A. It hides the notes completely
B. It ensures the notes are visible for all records at once
C. It displays the notes for the currently selected record only, reducing clutter
D. It makes the notes easier to edit in datasheet view
Q12. What is the main concern the instructor mentions about using Layout View in form design?
A. Layout View is not available in Access
B. Layout View always causes data loss
C. Layout View may cause problems, especially when VBA code is present
D. Layout View cannot be used for combo boxes
Q13. What is demonstrated by using string concatenation in the payee name calculation?
A. Combining numeric values from different fields
B. Merging first and last names for display when no company name is present
C. Formatting currency fields for reports
D. Automatically sorting names alphabetically
Q14. Why might you manually set up the relationship between PayeeID in the BillT and CustomerID in CustomerT?
A. Access will always create the join automatically
B. They are stored in the same table, so no relationship is needed
C. Access might not recognize they represent the same thing if named differently
D. There is never a need to join PayeeID and CustomerID
Q15. If you want to track multiple payments on a single bill, what kind of additional structure is suggested?
A. Add more PaidDate fields to the BillT
B. Create a separate Payment table to link to BillT
C. Use Excel instead of Access
D. Add memos to the BillT describing each payment
Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-C; 7-C; 8-B; 9-B; 10-C; 11-C; 12-C; 13-B; 14-C; 15-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 TechHelp tutorial from Access Learning Zone continues the Accounts Payable series with part two. If you have not gone through part one yet, I highly recommend starting there. That way, you will have all the necessary groundwork in place before proceeding.
In part one, we made changes to the customer form to allow customers to also be marked as payees. We set up a payee category list so each payee could be assigned a specific category. Now, as we expand further, we will be designing additional forms and reports where I need to clearly display who the payee is. Remember, not all payees will have company names; some might be individuals. For instance, we might have John Luke set as the landlord who receives rent and does not have a company name. Others, like Will Ryker for communications, could be the same. So, our database should manage both individuals and companies as payees.
To make this possible, I start by building a query called PayeeQ. This query will list the payee IDs and their display names. These display names need to be smart: on a report or in a combo box, I want to see the company name if it exists, but if not, fall back on showing first and last name.
I use the Query Design tool, bringing in the customer table, and including CustomerID, FirstName, LastName, and CompanyName as separate fields. Later, we might add more fields like address details, but for now this suffices. The real trick is introducing a calculation that determines what to display: when CompanyName is either null or blank, the query should show the first and last name; otherwise, show the company name.
I do this with a simple Immediate If (IIF) function, using IsNull to check for a missing company name. If you are new to the IIF function or string concatenation, I have other resources available that cover these topics in detail, and you can find links to those on my site.
Once I add that calculation, I run the query to check its output. Now, all potential payees are listed, but I want to filter the results to only those marked as payees in the system. By using the IsPayee field and setting the criteria to True, I narrow the query accordingly. For clarity, I sort the query by PayeeName and bring that property up front for ease of access. This way, reports and combo boxes can use a well-sorted, concise payee list.
Now that PayeeQ is set up, it is time to create our Bills table. Anytime a new bill comes in, whether by mail or email, we need a place to store all those key details: who the bill is for, how much is due, when it was received, due, or paid.
To do this, I go to Table Design and start a new Bill table. This table includes an AutoNumber BillID, a PayeeID (which is a Long Integer and essentially refers back to the CustomerID), and an AmountDue field as Currency. I also set up several date fields: BillDate for the date issued, EnteredDate to record exactly when the bill was added to the database, and DueDate for when payment is due. By default, I set EnteredDate to the current date using the Date() function, which helps especially in a multi-user scenario to track who is entering data and when.
PaidDate is another crucial field. If PaidDate is blank, the bill has not been paid; if it contains a value, payment has been made. In my comprehensive payables seminar, I cover a more robust system where you can make multiple payments against a bill, but for this tutorial, we are keeping it simple, using just the PaidDate for tracking.
A Long Text field for any notes rounds out the table. After saving the table as BillT and designating the primary key, I like to seed it with a few sample records for use when building forms.
With the Bills table and PayeeQ in place, creating a continuous form comes next, similar to the customer list form. Using a template for a continuous form, I copy and rename it to BillF. Then, I bind the form to the Bills table by adjusting the Record Source property.
Adding fields is straightforward: I pull all relevant fields into the detail section. I typically remove the default label controls, resize the fields for clarity, and keep or discard the BillID field depending on the needs of the database users. For teaching purposes, I leave it on for now.
For the PayeeID, I want a user-friendly combo box instead of a number field. This combo box will use PayeeQ as its source to list payee names. When configuring the combo box, I ensure only the necessary fields (CustomerID and PayeeName) are displayed, and then I carefully hide the key field column so users see only the name. The combo box will store the selected value in the PayeeID field of the Bills table.
After saving and opening the form in Form View, I check that the combo box is working as expected. The rest of the fields, like AmountDue, BillDate, DueDate, PaidDate, and notes, are arranged for ease of data entry. I personally prefer to align all the controls to the left and place the Notes field in the form footer. This way, only the active record's notes are visible, keeping the interface clean.
When it comes to formatting Notes, I like to maintain consistency. I'll often copy the notes control from the Customer Form if it already has the look and feel I want, and paste it into my new form, making sure the control names match so the data binds properly.
Last, I tidy up the form labels at the top for clarity, using one large label across the top and dividing it with the spacebar for each relevant field name. It is a small shortcut that avoids having to create multiple labels for each column.
At this point, we have a working Bills form that allows for quick data entry, accurate association of payees, and organized bills tracking. If you are interested in more advanced features like recurring billing or multi-payment handling, consider looking into my full payables seminar.
That wraps up part two of the Accounts Payable series. 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 List Creating a payee query to display correct payee names
Using IIF to show company name or person's name
Adding IsPayee criteria to filter payee list in query
Sorting payees by name in query
Saving and using the payee query for forms and reports
Designing a bill table with fields for payables
Setting appropriate data types for bill fields
Assigning default values for date fields in the bill table
Distinguishing between bill date, entered date, due date, and paid date
Using null values in PaidDate to track payment status
Adding sample records to the bill table for testing
Building a continuous form to display bills
Binding the form to the bill table
Adding and arranging fields on a continuous form
Replacing PayeeID field with a combo box for payee selection
Configuring a combo box to use a query as a source
Hiding key fields in a combo box
Storing the selected payee into the bill record
Formatting and aligning controls on the form
Moving notes field into the form footer
Copying formatting for notes field between forms
Creating labels for form fields in the header
Testing and reviewing the finished bills form |
