|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Inventory By Richard Rost 
5 years ago
Microsoft Access Inventory Management System In this video, I will show you how to manage inventory with Microsoft Access. We will track stock as it comes in, and as it goes out, and calculate the quantity on hand using a simple query. Garrett from Portland, Oregon (a Gold Member) asks: I love your Invoicing template. It's very helpful for managing my small business. Thank you. Is there any way to use this template to track inventory? I'd like to be able to know what my current quantity on hand is for any product. MembersMembers will see how to manage inventory using a slightly different method. We will store the actual quantity on hand in the product table instead of calculating it on the fly. This is a better long-term solution, especially for companies that process a lot of orders. It also allows for manual adjustment of the inventory levels. We will mark the order as shipped, and at that time run a query to adjust the inventory levels.
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, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, microsoft access inventory management, manage inventory, Can Microsoft Access be used for inventory? How do you maintain stock in Microsoft Access? How do you track inventory Access? How to Make an Inventory Database in Access, How to Create a Stock Control Database, How to Use Microsoft Access for Warehouse Inventory, Microsoft Access Inventory Template, Inventory Management System Database in Access, warehouse management system, warehouse management, stock, supply chain, warehouse
IntroIn this video, I will show you how to manage inventory in Microsoft Access by tracking stock as it comes in and goes out, and calculating your quantity on hand using aggregate queries. You'll learn how to set up product and inventory tables, use product IDs for proper tracking, link orders to products, and combine data with queries to calculate current stock levels. I'll also talk about handling unpaid orders, using outer joins, and the NZ function to deal with null values in your queries.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I am going to show you how to manage inventory with Microsoft Access. We will track stock as it comes in and as it goes out, and then calculate the quantity on hand using a simple query. Today's question comes from Garrett in Portland, Oregon, one of my Gold members. Garrett says, I love your invoicing template. It's very helpful for managing my small business. Thank you. You're welcome. Is there any way to use this template to track inventory? I'd like to be able to know what my current quantity on hand is for any product. Of course, Garrett, we can do inventory control in Microsoft Access. It's going to involve a few steps, so let me show you what you need to know first. Before we get started, here's a list of other videos I want you to go watch. These are all free. They're on my website and on my YouTube channel. Invoicing showed you how to set up a basic order form. It's got customers and all that stuff too. That's the video that Garrett is referring to. So go watch that first, definitely. You are also going to need aggregate queries, relationships between two tables, outer joins, and the NZ function. If you don't know what any of those things are, pause this video right now, go watch those videos, and then come back. This is the TechHelp free template. If you watch the invoicing database, you know exactly where to get this and how I created it. We've got our customer list, pick a customer, go to his order page, and there you can see there is an order for this customer. When we created this order entry database, I simply made the invoice so you can type in whatever you want: Superman playing card, then a quantity, and then a unit price. That's it. But in order to do inventory properly, we are going to need to set up a table of products, and each product is going to have to have a product ID. We need to know how to track each product when it comes in and when it goes out. We can't just go based on the name, "Superman playing card," because that's not good relational database construction. You could have two products typed in with slightly different names that really are the same thing, or two things that are different that have the same name. We're going to need product IDs to uniquely track each product. So let's close this, and we are going to create a product table. Create table design. We are going to start with the product ID. That's our auto number. Product name, short text, and a unit price. What does it sell for? That's a currency value. We do not need to worry about putting a quantity on hand in the product table for this example, and I'll show you why in a little bit. Let's save this as my product table. Primary key, yes, that's that. Let's put some sample data in here. Let's say we've got a shovel. That's product one, unit price $15. We've got a hammer, that'll be $12, and let's say a saw, that'll be $8. Simple product table. Save it, close it, and we're good to go. Now, we are also going to need to add that product ID to our order detail table so we know what each product is. Which one of those products from our table are we actually dealing with here? So we need the product ID. That will be a number, type long integer. That's my foreign key. I'm going to slide that up here, I like to keep all my IDs up at the top. Save it, close it. It's OK if you have products in orders that exist in the database already that do not have IDs. It won't affect what we are doing here. Of course, if you want to track inventory on all those, you'll have to go back and put the product IDs in. Now we need to add the product ID to our order detail form. Let's go back to our order detail form, design view. It should show up on the existing fields list now. Yup, there it is right there, product ID. I am going to click and drag and drop it over here somewhere, and we'll just make a little bit of room for it. We'll slide it right in there. It doesn't have to be very big. We'll make the product name just a little bit smaller. In this label, we'll just make this a little bit bigger. We'll call this product ID, then a few spaces and product name. You can share labels like that. It's all so they line up. Save that, close it. Let's open it up and take a look at it. Now for anything in here that has a product ID, we'll have to add it. Let's just go ahead and clear this order; let me just delete these items here. Let's add some new items from our new product list. So I'm going to have to open this up, so I can see what the products are. Can you pick the product from a list and just automatically add it to the order that way? Yeah, you can. It involves a lot of programming, and I actually cover that in the extended cut for the order entry system. Here's the database that the members get in the extended cut. We have a product list already with our products, unit cost, markup, all that stuff. If we go to a customer, go to orders, you can see right down here we can pick from a list of products, like Phaser Bank, and hit add, and it adds it to the order already. The product ID is actually saved in the table already. This is available for the members. Silver members and up get access to all this stuff. Gold members can actually download these templates. But this involves a lot of programming, so that's why it is covered in an extended cut. We are just going to have to make sure we put our product IDs in here. That's OK. I will start with the first one. We'll do product ID of one. It puts a zero in there. Get rid of that. The product name is shovel. Quantity is three, and they paid $15 a piece for them. It looks like my tab order needs to be adjusted. My tab order is off. That's OK. Let's go to the order detail form again, design view. We'll click right there and go tab order, I'll go auto order, because we put product ID in there last, so it shows up last in the tab order no matter where we put it. Save it, close it, and let's go back in there now. Let's say product two. Yeah, it's always defaulting that zero in there for the product ID. We can get rid of that if we want to. Let's just go back to the table real quick, the order detail table, design view, and let's make that product ID default to nothing. Sometimes you want that, sometimes you don't want a zero in there. One more trial. Let's see what it looks like now. Product two, much better, and that's going to be a hammer. Let's see if I use four of those. He gets a discount, so it's 11 a piece. He's not buying any saws. Now we've got product IDs in here with our products, and we've got our product table. Let's make sure we mark this order paid because we don't want to take unpaid orders out of our inventory. If we have multiple orders in here, let's say this one is just a test, and let's say that is not paid. This will allow us to track the stock that's going out. How are we going to track the stock that's coming in? We'll need a table to show when we receive inventory. Orders are inventory going out, so let's make a stock in table. Every time we receive product from our supplier, we put it in here. Create table design. Let's start with the stock in ID. That will be my auto number. We need to know what the product ID is. That is a number; that's our foreign key. The date received. I usually abbreviate RCVD for received, because the I before E thing always gets me and you don't want to have to be like one place you did IE, and another place you did EI, and you can't figure out why your code's not working because you spelled something wrong. So date received, that will be a date/time, and we can default that value to equal today if you want. Then we need the quantity received, and that will be a number. I'm just going to go with whole numbers. You can make this a double if you get fractions. If you order like one and a half cases of something, or whatever, I'm just going to default that to one. Save this as my stock in table. Define a primary key. Let's put some data in it. Again, knowing our product IDs, let's say we ordered 100 shovels today. So product one, we got 100 of them. Hammers, let's say we got 50 in stock. Let's say saws, we had 20 in stock. Let's say a week ago, we got 30 additional shovels. So I should show 130 total in stock when we finish our query. Close that. You can see how beneficial it is to pick from a list instead of having to keep this little table up over here, this little product table. Now we know our stock in, what we've received, and we know the stock that's going out, our order detail table. Here we've got product IDs and how many have gone out. We, of course, should make sure this order is paid. Now we're going to make a couple of queries to bring all this stuff together. Let's start with an aggregate query for stock in. Create query design. We're going to bring in the stock in table. I want the product ID and the quantity received. If I run that just like that, it gives me all the data. But I want an aggregate query. I want to group this by product ID. So turn this into an aggregate query, turn totals on, and we're going to group by product ID and sum up the quantity received. When I run it, I get a total, the sum of quantity received. All together for all of my getting stock from this supplier, or all my suppliers, I've gotten 130 total shovels. Save this. This will be my stock in query. Now we are going to do something similar for stock out. Create query design. Now we need to make sure that the order is paid, so bring in the order table and the order detail table. We are going to bring in "is paid" and set the criteria to true. We don't want to be taking unpaid orders out of our inventory. Next we need the product ID, but some of our items are going to have null product IDs. Even if you have something like training or service hours, you don't necessarily assign a product ID to it. If I run this now, you'll see some of them... well, OK, this order didn't have... let me turn off this criteria here. You might have orders that have null product IDs. Run. There we go. See some of these here? They're not paid, but the product ID is null, because you didn't put them in. Let's set this back to true. We're going to set the product ID to "is not null." That means it has a product ID. Now we just bring in the quantity purchased, and that will give us the quantity for each item there. Now we turn this into an aggregate query and set this to sum. Save this as my stock out query. This will give us a total of all of the products for all the paid orders, and what the sum of quantity out is. Now we just join together those two queries, and we can calculate the quantity that's left on hand: the total in minus the total out. One more query. Create query design. For this one, under queries, we're going to bring in stock in and stock out. I also want to see the product name, so I'm going to bring in the product table as well, and link that to the stock in. Generally, you're not going to put something on a stock out order if you don't have the stock in quantity already. If it's not something you already have, you're not going to sell it, usually. When I used to sell computers, sometimes I would sell a component that we normally did not stock, so it would go on the order, but it would not be marked as shipped until we had received that product. Usually, before you give the customer the product, it is in your inventory. I created a join here between product ID and this product ID, and that's fine, so we can get the product name. We also need to create a join between the stock out and the stock in, so grab product ID, click and drag it, and drop it right there. You should have two relationships here. Once these are joined, now we can do math on these. Bring in the product ID from whichever table, it doesn't really matter. You probably will have situations where you have stock that's in inventory, but you haven't sold it yet, so we want to make sure we see all the records from stock in and matching records from stock out. We're going to change this to an outer join, number two. Otherwise, we would only see records where we have sold that item. That's the whole reason you had to watch that outer join video. If you had, let's say, shovels in stock but you haven't sold one yet, if it doesn't exist in the stock out query, you won't see it at all and you won't know that it's in your inventory. That is very important. Now I can calculate my stock in and my stock out by just grabbing these values here. There's stock in. I'm going to rename these. I'm going to use an alias and rename this as "stock in." It just changes the name. If I run this, you'll see it's called "stock in" now. That's called an alias, and there's my 130 total. Stock out is going to be the sum of quantity over here. Stock out, and if I run that, there you go. In case you do run into a null value in here, that's where the NZ comes into play. Let's put NZ around this one, comma zero, so if you've got something like service, it'll just show up as zero, or something you haven't sold yet, for example. We can put NZ around this too, in case you've got stuff in here; you don't want to have null values. Better to have negative numbers than null values. Now we can calculate our quantity on hand. Quantity on hand is going to be stock in minus stock out. It's that simple, and then we can bring the product name in as well so we can see that. Save this as my stock query. Run that. There are your numbers. 130 in; sold three shovels, so I have 127 left. 50 minus 4; I have 46. 20 minus 0 is... so I had three of those, sold four of those, sold... let's put one more order in the system. Customer form. Let's go to James Kirk. Go to his orders. Let's just blank this one and let's say that we sold him five saws at $7 a piece; he got a quantity discount. Close it, close it, open up the stock query, and there you go. But wait a minute, it's still showing stock out as zero. Why would that be? Well, I don't think we marked that order as paid. So let's go to Jim Kirk's record, orders, and look at that; it wasn't paid. Now that it's paid, come back out here and run it, and there you go. There's your updated stock quantity. Let's say we also sell a couple more shovels, just to check. Let's go to the inventory orders. Shovels: let's say she buys seven shovels at $12 a piece, paid. Close it, close it, and check our numbers. There you go, ten of them sold now. You track your quantity in, you track your quantity out, you aggregate them all up to get totals, and you subtract one from the other. That's your inventory on hand. This method will work OK if you have a small business and you don't do tons and tons of transactions. If you have a medium-sized business or a large business and do lots of orders and inventory transactions, eventually this method will slow down your computer. If you have 10,000 orders in the system and 20,000 inventory transactions, every time you calculate your stock, it has to run through all those numbers. There is a different way to do this, and I will cover this in the extended cut for the members, where we actually store the quantity on hand in the product table as a field. That way, you have just one number to update, and you can just refer to your product table to see what the current quantity is. You have to use a little bit of VB code. When you actually mark the order shipped, it will then, at that moment, remove those items from inventory. If you want to learn more about how to calculate inventory, I show a better method in the extended cut for members. Instead of calculating the quantity on hand on the fly every time, we simply update the information in a quantity field. When you mark the order shipped, it checks to make sure it's paid, checks to make sure it hasn't been shipped already, then runs an update query to adjust the quantity on hand. That's all covered in the extended cut for the members. Plus, you get the full inventory, excuse me, the full invoicing video from before, where you can pick a product and hit "add" and it adds it to the order system automatically without you having to type in the order IDs. That's great, too. That's all included. All members get everything. You get all of the extended cut videos from all the previous stuff, not just this one. As of right now, Silver membership is $5.99. That's it. You get hundreds of extra videos. Gold members get the download templates. It's a great bargain and an awesome way to learn. Any questions, put them in the comments down below. How do you become a member? Click the join button below the video. After you click the join button, you will 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 build 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. Don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will 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 1 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. If you like Level 1, Level 2 is just one dollar, 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 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. Why is it important to use a product ID instead of just the product name when tracking inventory in a relational database?A. Product names can be duplicated or misspelled, leading to errors in tracking B. Product IDs look better in reports C. It helps in reducing the price of products D. Product names are always unique, so IDs are unnecessary Q2. What is the primary reason for not storing the quantity on hand directly in the product table in the basic method shown in this video? A. It is calculated from separate stock in and stock out transactions to keep data dynamic B. It increases database size unnecessarily C. Microsoft Access does not support quantity fields D. It leads to security vulnerabilities Q3. Which Microsoft Access feature is used to sum all quantities received for each product? A. Aggregate queries with the SUM function B. Update queries using VBA C. Crosstab queries with MAX D. Append queries Q4. When calculating the stock out (the quantity sold), what is a key filter to apply in the query? A. Only include orders that are marked as paid B. Only include orders from the previous month C. Exclude products with a high price D. Only include the first order from each customer Q5. What kind of join should be used to ensure you see all products in your inventory, even if none have been sold yet? A. Outer join from stock in to stock out B. Inner join between customer and product tables C. Cartesian join between all tables D. Self-join on the product table Q6. What is the NZ function used for in the inventory calculation queries? A. To replace null values with zero, ensuring calculations work properly B. To sort products alphabetically C. To calculate discounts for paid orders D. To join tables automatically Q7. Which field should be set as the foreign key in the order detail table to link products to order details? A. Product ID B. Customer ID C. Order ID D. Quantity Q8. What happens if an order is not marked as paid in this system? A. Its quantities are not subtracted from inventory B. The product is deleted from the database C. The order detail is ignored in all reports D. It doubles the stock quantity Q9. If you have orders for products that were not previously in your product table, what must you do for complete tracking? A. Add product IDs for those products B. Remove those orders from the database C. Increase the price of those orders D. Mark those orders as unpaid Q10. What is the main limitation of the aggregate method for inventory tracking as described in the video? A. It can become slow with a large number of orders and transactions B. It cannot track paid orders C. It requires manual calculations D. It does not work with Microsoft Access Q11. What improvement does the extended member method provide for tracking quantity on hand? A. Stores quantity on hand directly in the product table and uses VB code to update it B. Automatically emails stock levels to customers C. Deletes all unpaid orders automatically D. Adds a picture for every product Q12. Why might you want to use an alias in your query (like naming a field "stock in")? A. To make field names clearer and easier to read in query results B. To prevent Access from running the query C. To allow for multiple currencies D. To create subforms automatically Q13. When building the "stock in" table, what datatype is recommended for the stock in ID field? A. AutoNumber B. Short Text C. Currency D. Yes/No Q14. Why do you need to review videos on aggregate queries, relationships, outer joins, and the NZ function before building this system? A. They are essential concepts used for correctly designing the inventory queries B. They are required by Microsoft support C. Without them, you cannot build tables in Access D. These are only used for invoicing systems Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A 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 is focused on managing inventory using Microsoft Access. I will be demonstrating how to track stock as it arrives (stock in) and as it leaves (stock out), and then how to calculate your current quantity on hand with a simple query.The inspiration for this lesson comes from a question about my invoicing template and whether it can be adapted to monitor inventory. The answer is yes. Access is well suited to handling basic inventory control, though there are several important steps involved. Before diving into the process, I strongly recommend that you are familiar with some foundational topics: aggregate queries, establishing relationships between tables, working with outer joins, and using the NZ function in Access. If these topics are new to you, take some time to review them on my website or YouTube channel to ensure you get the most out of this tutorial. Let's recap the structure of the TechHelp free template, which many of you will recognize from previous videos. The system includes a customer list and an order entry page where you can quickly create orders by entering product names, quantities, and prices. While this initial setup makes for easy order entry, it is not suited for proper inventory management. To enable effective tracking, we need a dedicated products table. Each product must have a unique product ID, allowing us to consistently track inventory movement and avoid potential naming errors or duplicates that commonly occur if you rely on product names alone. The first step is to design the product table. This table should contain a unique product ID (set as an auto number), the product name, and a unit price. Importantly, there is no need to include a 'quantity on hand' field at this stage. Instead, we will calculate that value with queries. After establishing the product table, the next task is to update the order detail table by adding the product ID as a foreign key. This links the order items to their specific products, making accurate tracking possible. It is fine if some historical order data does not have product IDs at this point, but if you want a complete inventory history, you may wish to match them eventually. Once you have added the product ID to your order detail table, it should also be added to the order detail form, allowing users to select the correct product when entering an order. The ideal system would allow you to pick products from a list, but automating this process requires more advanced programming, which I cover in the extended cut of my order entry system. For the time being, you can manually enter product IDs when adding order items. Be mindful to update your tab order on the form after making changes so navigation remains intuitive. If you find default values, such as zeros, populating the product ID by default, adjust the table settings to remove unwanted defaults. With the basics established, you can now enter sample data for both your products and orders. When recording orders, make sure to only subtract quantities for paid orders, ensuring your inventory figures remain accurate. Tracking inventory movement requires not only recording what has been sold (stock out), but also what has been received from suppliers (stock in). To manage incoming inventory, create a 'stock in' table with its own auto number ID, the product ID, date received, and quantity received. You can default the date received to today for convenience. Populate this 'stock in' table with entries corresponding to each time you receive stock, using the correct product IDs from your product table. You will quickly see the benefit of picking products from a list rather than trying to remember or reference IDs manually. Once you have your stock in and stock out data, the next phase involves creating aggregate queries. Start by building a query to group stock in entries by product and sum the quantity received. Do the same for stock out, but only include paid orders and ensure that products without valid IDs are excluded. With these aggregates, you can create a final query to determine the quantity on hand for each product. This involves joining the stock in and stock out queries (using product ID), bringing in the product table to display product names, and calculating the on hand value as stock in minus stock out. To ensure you capture all products, including those not yet sold, use an outer join in your queries. To guard against null values and ensure your calculations work even when a product has not yet been sold or stocked, apply the NZ function to supply default zeros where necessary. Check your results by entering sample orders and marking them as paid. You should now see up-to-date quantities reflecting your inventory changes every time you modify an order or receive new stock. For small businesses, this method is clean and effective. However, as your order and inventory data grows to thousands of records, performance can slow down, since Access must recalculate totals each time you run the stock query. For more robust needs, I suggest a different method, demonstrated in the extended cut for members. In that approach, you store the current quantity on hand directly in the product table. When an order is marked as shipped, a bit of VBA code runs an update query, adjusting the product's quantity immediately rather than recalculating every time. This video also discusses membership options. Silver members and above receive access to extended cut videos, live sessions, and additional resources. Gold members have access to download folders and my code vault. Platinum members enjoy all these perks plus full beginner and some expert Access courses, along with lessons for Word, Excel, Visual Basic, ASP, and more. Remember, free TechHelp videos will always be available. If you enjoyed this tutorial, please subscribe to the channel for free, give the video a thumbs up, and leave your comments or questions. You can join the mailing list to receive email updates when new videos are released, since YouTube no longer sends email notifications. Be sure to check out my free Access Level 1 course if you are new to Microsoft Access. Finally, if you would like your question answered in a future video, submit it on my TechHelp page. 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 product table with unique product IDsAdding product ID to the order detail table Updating the order detail form to include product ID Adjusting tab order on the order detail form Setting product ID field defaults in order detail table Entering product selections using product IDs Creating a stock in table to track received inventory Entering inventory receipts into the stock in table Building an aggregate query to sum stock received Creating a stock out query for paid customer orders Filtering out orders/items without valid product IDs Summing quantities sold with aggregate queries Joining stock in and stock out data for comparison Using outer joins to include unsold inventory Aliasing field names in queries for clarity Handling null values with the NZ function in queries Calculating quantity on hand by subtracting outflow from inflow Displaying product names in the final inventory query Testing inventory updates after new orders or receipts |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access inventory management, manage inventory, Can Microsoft Access be used for inventory? How do you maintain stock in Microsoft Access? How do you track inventory Access? How to Make an Inventory Database in Access, How to Create a Stock Contro PermaLink Inventory in Microsoft Access |
