Project Budgets
By Richard Rost
4 years ago
Assign Orders to Projects for Budgeting
In this video, I'm going to show you how to set up relationships between three tables. We already have a database with customers and orders. We're going to add a projects table for budgeting. Each customer has multiple projects. Every invoice is billed to a project. This now becomes a database with relationships between three tables.
Will from Everett, Washington (a Platinum Member) asks: In my business, we have customers. Customers have projects. Orders must be assigned to a project for budgeting purposes. I see in your Invoicing database how you can assign an order to a customer, but how could I assign that to a project instead? I don't understand how I can then tell what customer the order belongs to. I'm confused. How can I get the CustomerID into the order table if the order is assigned to a project?
Members
Members will learn how to show only projects for the current customer in the order form combo box. We'll display the budget info on the project form and show the budget remaining on the order form. We'll also discuss instances when you might want to store that CustomerID in the order table as well as the ProjectID (a little Database Theory 101 for you today).
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!
Pre-Requisites
 Recommended Course
Links
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, customers, projects, budgeting, billing, reverse lookup, budget query, project combo box, set default project, calculate budget overrun, amount left in budget, calculate budget remaining, budgeting database
Intro In this video, we'll talk about setting up a project budgeting system in Microsoft Access. I'll show you how to restructure your database by adding a projects table between customers and orders, so that each order can be assigned to a specific project for tracking budgets. We'll cover building the necessary tables and forms, modifying relationships, updating queries including invoice reports, and creating a query to compare project budgets against amounts spent. This tutorial is great if you need to link orders to projects and track spending for each one in your Access database.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we're going to talk about relationships between more than two tables. We're going to add projects to the mix. So we've got customers and orders right now. We're going to say customers have projects, and then projects get assigned to orders for budgeting purposes.
Today's question comes from Will in Everett, Washington, one of my Platinum members. Will says, in my business, we have customers. Customers have projects. Orders must be assigned to a project for budgeting purposes. I see in your invoicing database how you can assign an order to a customer. But how can I assign that to a project instead? I don't understand how I can then tell what customer the order belongs to. I'm confused. How can I get the customer ID into the order table if the order is assigned to a project?
Let's take a step back and examine how the database is set up. Right now, our TechHelp database has customers and orders. Each customer can have multiple orders. That's a one-to-many relationship. Each order has a customer ID so we know who belongs to that order. What you need to do is create an extra table in the middle called a project table. You've got customers, and each customer can have multiple projects. Then, each project gets assigned multiple orders.
We'll actually take the customer ID off of the order table because we don't need it there. We just need a project ID. We can follow it backwards to find out who the customer is. Let's call it a reverse lookup. So the customer leads us to the project, the project leads us to the order. Our finished database will be customers. Open up a customer record, you'll see their projects. There's the projects for that customer. Then each project gets assigned orders. There's an order from that project, so you get the project ID there now. If you need to know the customer ID, just follow it backwards.
Let me explain how all of this works. Before we get started, some of you might be wondering when you'd ever want to do this. A lot of construction companies, general contractors, companies like that, take on a large project like building a particular office building. That project has a budget—$5 million, whatever it happens to be. Then they will invoice for things for that particular project as those costs are accrued. You might make an invoice one day for some materials, then for some labor, then for other things. It all gets posted toward that project, and all of those multiple projects belong to a customer, your general contractor client.
That's why you might want to do this. But even if you don't do that kind of stuff, this type of relationship setup comes up all the time.
Even in my database as it is right now, I do have a relationship set up between three tables. Can you guess which three they are? We've got the customer, we've got the orders, and then we've got the order details in the middle here.
If we look at that in a relationship setup, if we go up to Database Tools, Relationships, I don't spend a lot of time in this global relationships window. I have lots of reasons why and I have a whole separate video on that. Watch my relationships video if you want to learn more about that.
Essentially what we've got here, if we add tables, we've got the customer, we've got the order, and we've got the order details. The order table is kind of like a junction between the two of those. Customer ID goes to customer ID here and then the order ID relates to the order ID there. That's actually a three-way relationship. You don't need to store the customer ID in each order detail item in each line item. We don't need it there because we can follow it back to find the customer.
Same thing if we just add another, a fourth table in here, which is the project. So we've got projects, projects have orders assigned to them, and orders have order detail items. What we're going to do is insert the project's table right there and see how to work that into our database.
I'm going to close this and say no to save changes.
By the way, before we get started, if you haven't watched my invoicing video yet, go watch this. It's free. It's on my website. It's on my YouTube channel. There's a link down in the bottom there. This video will explain how I built the database that we're going to work with right now. So if you have this database built, it might not be so easy to follow along with the rest of what I'm going to do today. That's how all learning is. You've got to learn how to walk before you can run. You've got to learn arithmetic before you can learn calculus. So go watch this if you haven't yet, and you can get a free copy of the database while you're there.
Also, we're going to need a teeny tiny bit of VBA, just a little bit, a couple of lines. If you haven't done any VBA programming, go watch my Intro to VBA video. It's only 20 minutes long. I'm going to teach you all the basics. I'm going to show you what you need in today's video, but it'll be a great help to you if you go watch that first.
Let's start out by adding a project table. Here, we've got customers, we've got orders, we've got order details. Let's add projects in there.
So, create table design. Don't need that property sheet to be that big.
So, what do we need? We need a project ID, and then we need a customer ID. Because each customer is assigned multiple projects. That's going to be a number of type long integer. That's my foreign key. Watch the relationships video for more explanation about that. I'm also going to put in here a project code because your client might give you a project code for whatever they want to call it. You might see it on their documents or whatever. You don't want to deal with your IDs because your IDs are just auto numbers. They're meaningless. Your project code might help you to identify the project or you could type in a description, whatever you want. Any other information you want about the project in here, such as the budget, you can put in this table. We'll talk about that a little bit later.
Save this as project table, primary key and let's go ahead and just close that. You could also index that project code if you want to make sure you don't get two project codes the same. That's all up to you.
Next, let's make a project form so we can get a list of projects for each customer. I've already got a continuous form right there. I'm going to copy and paste that guy, Ctrl+C, Ctrl+V, copy, paste. We'll call this project F. It will be my project form area right there. Right-click, Design View. This is where we'll put the project stuff.
First, open up the form's properties, find the record source. Assign this to project T.
Now we can add fields. I'm going to open this up just a bit. Let's add existing fields: project ID and project code. We're going to just use customer ID as a combo box. Bring these two things in over here, drop them like that. The only reason I keep these guys around is for some auto formatting. Watch this, click, click, click because that's an ID. Then I'm going to go click, click, click to format those guys. The border sets the background colors, all that stuff. So we've got the project ID there.
We're going to put the customer here in a combo box, and then the project code can go over there. Let's put the project code in front of it. I'll put the customer here. I'll copy this thing. Sometimes I cheat, I do this and then I just go in here and hit the space bar. This works. I was doing this for a while. Let's put code in there. I was doing that for a while, and it's easy when you're setting up forms that you're not going to use much. But if you modify this and work with this and edit this form a lot, it becomes a bit of a pain. So it's really up to you whether you want to use that technique or not.
So we need a combo box over here to pick the customer. Where can I get a combo box that's got the customers in it? I've already built one. It's on the order form. Look at that. There's a combo box right there that's got the customers in it. If you don't remember how I built this, go back and watch the invoicing video. I'm just going to copy that. Ctrl+C. Come back over here and then paste it in the detail section. There it is. Get rid of that label. Delete. Slide that over. We'll stick it right there.
Project ID. Project code. Got the customer. Close that up. Save it. Close it. Open it.
Let's put one in here. Let's pick a customer. Let's pick James Kirk. His project code is NCC-1701. Let's put another project in for Jean-Luc Picard, and his is NCC-1701D. Guess what? Anybody? The, of course. My Trekkies.
Then let's say Jim Kirk comes in again for another one, and this one is NCC-1701A because he blew the other one up.
Close that. That's our project form.
Now let's go to the customers real quick. Let's go to the customer form. We don't need an orders button here anymore because I don't want to go directly from customers to orders anymore. This is going to get replaced with projects. Delete this button. Now we're going to make a button to open up the projects form but only show the projects for this customer.
I have a video where I show this in great detail right here. It's "Open a Form to a Specific Record." If you've watched the invoicing video, I think this was a prerequisite for that one. So you should have seen this already. This is why I say you should watch the prerequisites. The prerequisites might have prerequisites. So you have to start from the beginning. And this is why I have full courses. I do beginner, expert two, three, four, and so on because I teach this stuff in the right order. When you're watching my TechHelp videos like this, you might have to jump around to a bunch of different videos. That's what the full course is for. You don't have to jump around. You can just watch stuff in order.
I'm going to grab a button, drop it right here. Cancel the wizard because I like to build these myself. Projects. We are going to call this the projects button, project BTN. Make sure it's the same size as Contacts.
Right-click, build event. That will bring up your code builder.
Here we are. Projects button click. One thing you could do is get rid of this order button click code. This is why I like to make sure we name our buttons. Alex beat me over the head for years to get me to name my buttons because I never named my buttons. You never refer to button code from anywhere else, but I didn't care if it was command 42. But now it makes perfect sense here. I can see this was order button, which I just deleted the order button, and Access won't clean up the code. If you delete a button, for example, it doesn't automatically delete that source code. So you can come in here and clean it up now and delete that.
Here's the template we need right there. I'm going to copy that and paste it there. This DoCmd.OpenForm "ProjectF",,, "CustomerID=" & CustomerID. Save it. Close it. Close it.
Let's open up James Kirk, go to Projects, and there we are. There's just James Kirk's projects. If I go back, Jean-Luc Picard has one project.
The default value of the combo box is set on the order form to be equal to whoever the open customer is. That's how we get that there for the next new one. So if Picard later brings in a Borg cube that he's working on, you can see that's automatically defaulted in there.
Now we have no way to get to the order form. So to get to the order form, we have to have a project selected. Let's put an order button down here on the bottom. It'll be basically the same thing.
Design View. Grab a button. Drop it down here. Cancel the wizard. There's a command button wizard for it, but I don't like it. Orders. I like to write my code by hand. You get the habit of it. You learn some stuff. This is now our orders button, order BTN. I know I say orders button, but I like to keep all my command button names, my text box names, my field names, my table names, all singular. Even though the label says orders, and I might say it's the orders button when I'm talking, I like to keep this all singular. Why? Because it's just easier when you're coding. You don't have to stop and remember whether it was customer table or customers table. So I just keep everything singular in the code. That's how I do it. You don't have to, but you'll run into this problem eventually, and then you'll slap yourself and say, why didn't I listen to Rich?
Right-click. Build event. I know it's off the screen, but it says build event. You've seen that before.
DoCmd.OpenForm "OrderF",,, "ProjectID=" & ProjectID.
But wait a minute, you might be saying there is no project ID on the order form yet. Well, yeah, we have to do that. We have to replace customer with project, but I just want to set the button up now. Save it. Close it. Let's see what happens.
Click on that button. It's asking for a project ID. Why? "Enter parameter value." Why does it say enter parameter value? That's a common thing that gets posted in the forums. People email me all the time. When you see "Enter parameter value," that means Access has no idea what you're looking for. It can't find a field called project ID. Nine times out of ten, you spelled something wrong, or it doesn't exist. In this case, it doesn't exist because we didn't put it there yet. This comes up so often, I have a whole video about it. Go watch that.
We can cancel, and it will give us an error. But that's okay.
So let's go to our order table. Let's just kill what's in here, delete all the order details first, then the orders.
If you have existing data in there, it's a little more complicated. You'll have to add the project ID field and then use some append and update queries to move things around. That's a lot more complicated. If anyone wants to see how to do that, I have videos on it. Just let me know. You just have to move things around. It's easier if you build this into the database to begin with. Trust me.
So the order table, right-click, Design View. We no longer need a customer ID. Now we want this to be the project ID. Project ID. I can do that because we deleted all the data. If you want to keep that field in there to hold on to your old data until you convert everything over, great. Save that.
Now we have to go to our order form and replace this customer combo box with a project combo box. Delete that. Go to my combo boxes right here. Drop that there. We're going to create a relational combo box.
Again, if you took the invoicing video, then you've seen this. I'm going to get the value from another table or query. We're getting a list of projects from another table, from the project table.
Next. Project table. Next. Bring over the project ID and the project code. We don't need the customer ID because we're not putting the customer ID in here, and we don't want to see the customer ID. But we need it in this table; we don't want to see it over here.
Next. Do you want to sort these records? Yes. Sort by the project code. Next. That's what it's going to look like. That's everybody's stuff. Don't worry about it.
We're going to save that value in what field? We want to pick a project and store it in the project ID of the order table. Next. What label do you want? Project. Then finish.
There's my combo box. I'm going to do some format painting: click on this guy, format painter, click on that guy, just to get that label colored. Slide you over. There's my project ID. Save it.
You might want to put it in the tab order in the right spot. Tab order. It comes in as combo 14. First, let's fix that. Open this guy up. I wish the wizard asked you what the name should be. ProjectCombo. That's my thing. I like to end combo boxes with Combo. Why? Because later on when you get into programming and VBA, you might want to know what's a combo box instead of just an ID field. So you can access the columns and stuff.
Put it in the tab order in the right spot. Grab you and move you right after description. Click and drag. Save it. Close it.
Now our project form should work. Oh, I don't know how many projects do I? Let's go to the customer list, go to Jean-Luc Picard, go to projects, and let's see what we have here for NCC-1701D. Orders. There we go.
Now the default project isn't populating in there. That would be nice when we go to create a new order; I don't want to have to pick this every time.
Right-click, Design View. Open this guy up, and we're going to set the default value on the Data tab. Default value: =Forms!ProjectF!ProjectID.
You get these little pop-ups. Kids nowadays are so spoiled. Back when I was learning Access back in the 1930s—no, seriously—back in the 1990s, probably 92, no, 94 I think. Access version 2.0, 94. That's the first version I worked on. We didn't have these little pop-up helpers. You had to just know this or keep Notepad up all the time with all your form names, field names.
This is getting a value from another open form. I have another video about that. If you don't know how to do that stuff, again, it's covered in the invoicing video.
Today I feel like—anybody remember In Living Color from back in the 90s—Calhoun Tubbs by David Alan Grier? He'd be like, wrote a song about it. You want to hear it? Here it goes. That's how I feel today. Oh, I have a video about this. You want to see it? Here's the link. I'll put a link to the Calhoun Tubbs video down below as well.
Close it, save it, close it. Open it back up again and let's go here and click Orders. Now we're ready. We've got the order, we've got the project already filled in for us. Put a refit in for new phasers and that's $20,000 and a new photon launcher. Those are expensive.
Close that. If you want to make a list and order list, you can do that too. Just like I did with the projects, this could open up another form that's got a list of orders in it. Instead of just opening up the order form, to add a new one, just hit the Add New.
You know, triple canon. Where do I come up with this stuff?
Now this particular project has two orders. There's this one and there's that one. That works though: customers to projects to orders. Want to put in something for another customer? Go back to James Kirk. So that's the project, and let's put one on the NCC-1701A project. New ship, right? He blew up the old one, so that's pretty pricey. See how that works?
Now, for your invoice, we also have to fix the printable invoice, the order invoice query. If we go into here and try to open up one of these and hit the invoice, let's see what we've got here. Yeah, it's going to be all wrong because this thing doesn't have the project information in it.
So let's close that. Let's open up the order invoice query.
The problem here is that the order invoice query still has order T linked to customer T directly, and that relationship no longer exists. Now we still need the customer T in here because we've got fields from the customer T on the invoice we need: first name, last name, and so on.
So let's add the project table. Fix these relationships. Order no longer relates to customer T, so you see that one back there? That relationship has to go. If we rearrange these, it'll make more sense: customer on the far left, project next to that, order next to that, order detail next to that. That's the way it should be lined up. Let me maximize this, make it easier to see: customers, the projects, the orders. I do them like this, a little stepped, so you can see that one's kind of under the other one like that.
That makes more sense: customer, project, order, order detail query, and the queries over here, order ID is set right there. So it knows which one to open. Save it. Close it. Let's open it back up again and let me change that because it's too big for that field. Let's make it a little smaller.
Invoice. Now it's correct. If you want to add the project code over here for the client, you can. That's very easy to do: Design View, maybe throw it right under here. Add existing fields. It's in the query now, so where is it? Project code. Actually, I don't think we brought project code in. We'll have to add it. Close this. Go back to the query, Design View.
Usually I only bring in the star from one table. That's just a general rule that I have. I don't like to bring in all stars because then you have like projectT.projectID and then orderT.projectID. It's the same field from two different tables, so I avoid that by not bringing in all the stars from multiple tables. Just bring one of them: projectID—oh, we already have projectID, you need the project code. Delete, you need the project code, which is only right there. Now save it. Close it. Right-click, Design View.
Now we can add the project code right there, and then we'll use a little format paint action, match that stuff up, slide it over, slide to the left. There it is. The older I get, the more I hate these little minor adjustments.
There we go. There's our project code.
Now you ready for some bonus material? You said bonus material? OK, let's do it.
So we got into this whole mess because of budgeting. Let's make a budgeting query. Let's make a query that shows the actual budget.
Go back to our table, to our project T, Design. When we set up a project, each project gets a budget, and that will be currency. This is so you can tell if your project is under budget, over budget, how much budget is left. Do we really need the gold plated faucets in the Enterprise lounge? I don't know. Or, excuse me, gold is worthless in the future, it's latinum.
Let's put some budgets in here. Let's say this budget is $500,000. This one is $125,000. This one is $300,000. This one is $50. Each project has a budget. Close this.
Let's build a query to show how much has been spent for each project. For this query, we're going to add the customer, the project, the order, and the order detail query. Why the order detail query? Because it has that extended price, which is basically quantity times unit price for each item. If you took the invoicing video, you know that.
Order detail query, orders, projects—why do I still have—this is still trying to link customer to project right there. Let me check our database tools, relationships. Add customer and order. At one point—and this might come up, I'm glad this came up because I wasn't planning on this, but this is good to see—instead of deleting customer ID, I just renamed it to project ID. At one point in the past, probably one of my past videos I did with you, I must have made a relationship between these two tables, and it's stuck in the system. Since I just renamed this, that relationship is still there. So we need to make sure we delete this relationship here in the global relationships. Save that. Close it. Now it shouldn't do that again when you make a query. It shouldn't create that ad hoc relationship. In my relationships video, I talk about this in more detail.
What do I want to see in this query? I want the customer ID, first name, last name, just to identify the customer, maybe the project ID and the project code, and the project's budget. For the order, I don't need every order broken down, but I need all of the order totals added up. The order total is gathered by adding all extended prices on one order. But if I don't bring in the order ID and just bring in the extended price, look what I get if I run this query now: every order and every extended price.
I can turn this into an aggregate query by putting the totals on, and then instead of grouping by this, I'm going to sum them up. Now if I run this query, look what I get. That's every invoice. There are multiple invoices for this project code, and they're all added up right there in sum of extended price.
Let's rename that; instead of sum of extended price, let's call this spent or amount spent or whatever you want to call it. We'll format it as currency. Sometimes when you put the aggregate query, the sum on there, it loses the format. We'll go to properties and manually format that as currency.
Save it, and let's call this ProjectBudgetQ. That's my project budget query. Q has nothing to do with Q from Star Trek.
So there we go. There's a project; that's how much we spent. You can see this project is way over budget, and this one's coming in under. I'm not seeing the other projects that are open because they don't have orders on them yet. If you want to make sure that you see them all, make sure that you set up outer joins here. Right, show me all of the customers, whether or not they have projects. Show me all of the projects, whether or not they have orders, and vice versa, and so on and so forth.
You might want to not see the customers that don't have projects, so we could probably make this one just an inner join. I just want to see just all the projects, the customers that have projects. There we go. These guys here don't have any orders.
Outer joins, by the way, I have a separate video for it.
That's how we can see all of the projects even if they're missing invoices. If you want this to show up as a zero, we can do this: put NZ around this guy and go comma zero like that. Now if I run it, you'll get zeros there. What's NZ? That's null to zero. It means if that value is null, make it zero. I have a video for that one too.
Do you want to have another column over here showing your overrun or amount left, whichever way you want to subtract it? Well, you can't put that in this query. Once you start aggregating like this, you lose the ability to do calculations here because this hasn't been figured out yet by the time this column generates. You have to finish this query before you can now take these results and put them into another query to do calculations on those. You can't subtract spent from budget yet until this query is done generating.
By the way, I forgot to mention aggregate queries. This is the reason why it's best to take my course in order. You have to jump around to a bunch of different videos. Aggregate queries let you sum up values, take averages, find the largest, the smallest, that kind of stuff. If you want to take a customer and all of their orders and add all of their orders up and get a sum and have all of those for every customer in a query, that's an aggregate query—a totals query, sometimes it's called.
To find our budget overrun, we can feed this into another query. Create another query: create query design. Bring in that query we just made, which is our ProjectBudgetQ. Bring in all the fields and make one new field, a calculated query field, and I'll call this Overrun: Spent - Budget. Or you can go the other way and say the amount left is Budget - Spent, whichever way. If your business generally goes over budget, do it this way; if you generally have money left, do it the other way.
Save this as ProjectBudgetOverrunQ. When you run it, there you go, there's your overrun. I kind of like having budget left. Let's do it the other way: Remaining: Budget - Spent. That looks better. There. That's how much of your budget is remaining. If it's negative here, that means you're over.
There's your budget. That works. Now you've got your orders summed up with your project, and the project can be linked back to the customer.
Did you have fun learning about budgets and orders and all that cool accounting stuff?
There's more for the members in the extended cut. We're going to do some more stuff. We'll start by discussing when you might possibly want to actually store that customer ID in each order. Sometimes, once in a while, depending on your use, you might want to do that. Generally, you don't have to. I generally try not to store values that I can look up, but there are exceptions.
Then we'll change the combo box on the order form so you only see projects for the current customer. That will be handy, because right now, if I come in here, go to a customer, then open up his projects, go to a project, and go to add a new order, I can drop this down and see projects from everybody. You might want to limit this list a little bit and only show the projects for that customer, so we'll talk about how to do that.
Then we're going to do an add new project button. We're going to bring in that calculated stuff into the form so you can see it right here. But that makes this form uneditable. The recordset's no longer updating, so you have to make a button to add a new project. This is all covered in the extended cut for the members.
Silver members and up get access to all of my extended cut videos, and there's lots now, like 260 or so, I think. Gold members can download these databases, and you get access to the code vault where all kinds of cool VBA code is. Plus members get one free class each month as well from my regular classes so you can take them in order and learn the right way.
Hope you learned something. We'll see you next time.
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. What is the main purpose of introducing a Project table between the Customer and Order tables in the database?
A. To duplicate customer information in orders
B. To provide a way to assign budgets and group orders for each project under a customer
C. To make the customer table more complex
D. To allow orders to belong to multiple customers
Q2. In the new relationship structure, how can you determine which customer an order belongs to?
A. By storing the customer ID directly in the order table alongside the project ID
B. By using a reverse lookup: follow the order to its project and the project to its customer
C. By assigning every order to a default customer unrelated to the project
D. By storing all customer and project information in the order details
Q3. Why is it generally not necessary to store the CustomerID in the Order table when using a Project table?
A. Because projects don't need to be connected to customers
B. Because the project table is used purely for budgeting and not relationships
C. Because you can determine the customer through the project, avoiding redundant data
D. Because Access does not support linking more than two tables
Q4. When creating the Project table, what is the purpose of the ProjectCode field?
A. It is used as an internal auto number primary key
B. It helps users identify the project with a familiar code or description
C. It serves as a foreign key to the customer table
D. It tracks the budget spent for each project
Q5. What is the recommended type for a foreign key, such as CustomerID in the Project table?
A. Text
B. Date/Time
C. Long Integer (Number)
D. Currency
Q6. Why should you avoid storing all fields from multiple tables (using *) in queries?
A. It increases database speed
B. It prevents the occurrence of duplicate fields with the same name causing confusion
C. It fills the table with unnecessary null values
D. It blocks the creation of join relationships in Access
Q7. When modifying the Order table and form to link to the Project table, what must be changed on the order entry form?
A. Add a button to open the Customer form
B. Replace the Customer combo box with a Project combo box
C. Add a subform for the project details
D. Remove all combo boxes from the form
Q8. What is the significance of setting a default value for the Project combo box on the Order form?
A. It prohibits entry of new records in the form
B. It auto-populates the project for new orders based on the open project form, speeding up data entry
C. It ensures the customer is always correct on the order
D. It restricts the order form to a single project
Q9. In the context of aggregate (totals) queries, what function was used to determine the total amount spent on a project?
A. Average
B. Min
C. Sum
D. Count
Q10. What does the NZ function do in Access queries?
A. Calculates currency values
B. Returns the next sequence number for a field
C. Replaces Null values with a specified value
D. Normalizes table structures
Q11. When would you use outer joins in a query in this context?
A. To show only records having matching values in both tables
B. To display all projects, even those with no orders
C. To prevent the display of customers without projects
D. To display all order details, regardless of project
Q12. If you want to see how much budget is remaining for each project, what should you do after creating an aggregate query to sum spending?
A. Add a currency format to the project name
B. Create a new query based on the aggregate query and subtract spent from budget
C. Add a calculated field in the Project table
D. Use a make-table query to export budgets
Q13. Why is it important to name controls (like buttons and combo boxes) consistently in Access?
A. To reduce the file size of the database
B. So that Access can automatically remove code for deleted controls
C. To improve readability and maintenance of code, and avoid confusion during development
D. So that forms will always open to the correct record
Q14. Why is it best to build this multi-table relationship into your database from the very beginning?
A. It cannot be changed later
B. Adding the relationships later can be tricky if there's existing data to convert
C. It will automatically create all necessary queries
D. It is required by Access for database integrity
Q15. What is the reason for not using the global relationships window frequently, according to the video?
A. It does not support more than two-table relationships
B. It automatically generates all queries
C. The instructor prefers working with relationships directly in forms and queries for clarity
D. It is too advanced for most Access users
Answers: 1-B; 2-B; 3-C; 4-B; 5-C; 6-B; 7-B; 8-B; 9-C; 10-C; 11-B; 12-B; 13-C; 14-B; 15-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on managing relationships in Microsoft Access when you need to connect more than two tables together in your database. In this lesson, I am going to explain how to add a Projects table that links Customers and Orders, which is a requirement in many real-world business scenarios like construction or contracting where orders are tracked by specific projects for budgeting.
The question we're exploring today came from someone who needs to assign orders to projects instead of just customers. The main confusion was how to determine which customer an order is connected to when each order is now tied to a project, and each project belongs to a customer.
Currently, a basic invoicing database links customers directly to orders with a one-to-many relationship. However, to suit this new need, we need to insert a Projects table between Customers and Orders. Now, each Customer can have multiple Projects, and each Project can have multiple Orders assigned to it. This means the Order table will no longer store the CustomerID directly; instead, it will reference the ProjectID, and you can determine the customer by tracing the ProjectID back up to the relevant Customer.
To make this system work in Access, you would design a Project table with at least a ProjectID, CustomerID (as a foreign key), and whatever other project info you require, like a project code or description. If your client gives you a specific project code, you can store it there instead of just relying on the automatically created IDs.
Once the Projects table is in place, you create a form so you can view and manage the list of projects for each customer. For efficiency, it is often easiest to reuse existing form controls like combo boxes to select customers, and customize the form to your liking for managing projects.
The flow now becomes: Customers have Projects, and Projects have Orders. When setting up the UI, for example on the Customer form, you replace the direct Orders button with a Projects button that opens the Projects form filtered for the selected customer. On the Projects form, you then add a button to launch the Orders form, filtered so that only orders for the chosen project appear.
Updating the database also means making changes to the Orders table. The old CustomerID field gets replaced by ProjectID, and if you are working with a blank database or are able to clear out old data, you can just rename the field. If there is existing data, additional steps are needed to manage the transition—using append and update queries to remap existing orders to the correct projects.
Next, on the Orders form, you swap the customer combo box for a project combo box so users can select the relevant project for each new order. It is also helpful to set the default value for this combo box so when you open the form from a certain project, it automatically fills in the right project.
There is a small bit of VBA programming involved in wiring up these buttons, but it is pretty minimal. If you are not experienced with VBA, I have a short introduction video that can help get you started with the basics.
Once these form and table changes are done, you need to adjust reports, such as the printable invoice. The invoice query should be updated so that it follows the new relationships: Customer to Project to Order to Order Detail. Rearrange your query and ensure it brings in any necessary project details, like the project code, so you have all the data you need for the invoice.
To support project budgeting, add a budget field to the Projects table. For reporting, build queries that summarize the total spent per project by summing up the extended prices from the order details. This is called an aggregate query. If some projects have no orders yet, use outer joins in the query to ensure those projects still appear, and use a function like NZ to show zeros instead of blank values. To calculate things like budget overrun or remaining funds, you feed the totals query into another query where you can subtract the spent amount from the budget.
Throughout the video, I reference related topics like aggregate queries, working with null values, and how to use default values from other open forms. If any of these ideas are unfamiliar, I recommend checking out my other foundational videos to build up your understanding.
In today's Extended Cut, I talk about when you might want to store the CustomerID in the Orders table anyway, even though you can usually look it up through the Project. I'll walk you through limiting the project selection combo box so it only displays the current customer's projects. Additionally, I demonstrate how to add a button to create new projects directly from the order form and how to bring calculated budgeting info into the project form, which brings its own challenges.
As always, Silver members and above have access to these longer Extended Cut lessons, and Gold members can download sample databases and access more detailed VBA code. Platinum members can take my full-length courses. Membership details are available on my website.
You can watch a complete video tutorial with step-by-step instructions covering everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Creating a project table to link customers and orders
Designing the project table with project ID, customer ID, project code, and budget
Creating a project form to display and manage projects
Using a combo box to assign customers to projects
Copying and modifying combo boxes for customer selection
Adding projects to customers using the project form
Creating a button to open projects for a specific customer
Passing customer ID as a filter to the project form
Removing the direct orders button from the customer form
Adding a button to open orders for a selected project
Modifying the order table to store project ID instead of customer ID
Updating the order form to use a project combo box
Setting up the combo box to select and store a project ID in orders
Setting the default value of the project combo box from the open project form
Fixing and updating the invoice query to accommodate the new table relationships
Joining customer, project, order, and order detail tables in queries
Adding project code to the invoice and query views
Defining and entering project budgets in the project table
Creating a project budget query to sum order totals per project
Using aggregate (totals) queries to sum amounts spent by project
Formatting query totals as currency
Handling outer and inner joins to show or hide projects without orders
Using the NZ function to convert nulls to zero in queries
Calculating budget overrun or remaining budget in a separate query
Feeding aggregate query results into another query for calculations
Displaying remaining budget or budget overrun for each project
Linking orders and projects back to customers through table relationships |
