Create Your Typical Invoice Template for Inventory with Ease

Typical invoice template for Inventory

welcome to another tech help video brought to you by accesslearningzone.com i am your instructor richard ross in today's video i'm going to show you how to manage inventory with microsoft access we'll 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 well 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 they're on my youtube channel invoicing shows 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 so if you don't know what any of those things are pause this video right now go watch those videos and then come back go on i'll wait i'll hold the class up just for you go on okay okay this is the tech help free template and 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 up a customer go to his order page and there you can see is an order for this customer now when we created this order entry database i simply made the invoice so you can type in whatever you want right superman playing card and then a quantity and then a unit price and that's it but in order to do inventory properly we're going to need to set up a table of products and each product is going to have to have a product id so we 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 so we're gonna need product ids to uniquely track each product so let's close this and we're going to create a product table create table design we're going to start with the product id that's our auto number product name short text and a unit price what does it solve 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 t my product table primary key yes that's that let's put some sample data in here all right let's see we've got a shovel that's product one unit price fifteen dollars we got a hammer that'll be twelve dollars and let's say a saw that'll be eight bucks okay simple product table save it close it we're good to go now we're also going to need to add that product id to our order detail table so we know each product what it is right which one of those products from our table are we actually dealing with here so we need the product whoops product id that'll be a number of type long energy that's my foreign key i'm going to slide that up here like to keep all my ids up at the top save it close it now it's okay if you have products in orders that exist in the database already that don't have id's that's okay it won't affect what we're doing here it's just of course obviously 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 right this guy we got to put the product id in here so let's go back to our order detail f this guy design view okay it should show up on the add existing fields list now yep there it is right there product id i'm 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 come here there you go right about there i don't have to resize everything on the form and this label will just make this a little bit bigger we'll call this product id and then a few spaces and product name can you share labels like that sure why not as long as they line up who cares all right save that close it let's open it up take a look at it all right there we go 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 and let's add some new items from our new product list so i'm going to open this up so i can see what the products are now 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 okay we have a product list already with our products our unit cost markup all that stuff but if we go to a customer go to orders okay you can see right down here we can pick from a list of products click on like phaser bank and hit add and it adds it to the order already and the product id is actually saved in the table already so this is available for the members silver members and up get access to all this stuff right gold members can actually download these templates but this involves a lot of programming so that's why it's covered in extended cut but we're just going to have to make sure we put our product ids in here that's okay so i'll start with the first one we'll do product id of one that puts a zero in there get rid of that all right the product name is shovel all right quantity is three and they paid fifteen dollars apiece for them and it looks like my tab order needs to be adjusted my tab order is off that's okay let's go to the um the order detail form again design view all right we'll click right there we'll go tab order i'll go auto order right 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 okay slide this over here okay 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 get out of there sometimes you want that sometimes you don't want a zero in there right one more try let's see what it looks like now okay product two much better and that's gonna be a hammer so he buys four of those at let's say he gets a discount so it's 11 a piece okay and he's not buying any saws okay so now we got product ids in here with our products 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 right if we have multiple orders in here let's say this one here's got just a test but let's say that's not one that was not paid okay now this will allow us to track the stock that's going out okay how are we going to track the stock that's coming in well we'll need a table to show when we receive inventory okay order is our inventory going out so let's make a stock in table so every time we receive product from our supplier we put it in here all right create table design let's start with the stock in stock in id that'll be my auto number we need to know what the product id is right that's a number that's our foreign key the date received and i usually abbreviate r c v d for received because this i before e thing always gets me and you don't want to have to be like one place you did i e the other place is e i and it's okay you can't figure out why your code's not working because you get spelled something wrong okay so date received that'll be a date time and we can default that value to equal today if you want and then we note the quantity received and that'll be a number now i'm just going to go with whole numbers you can make this double if you get fractions if you order like you know one and a half cases of something or i don't know whatever i'm just gonna go default that to one okay save this as my stock in table to find a primary key let's put some data in it all right so again knowing our product ids let's say we order 100 shovels today alright so product one and we got a hundred of them okay hammers let's say we got 50 in stock let's say uh saws we had 20 in stock and let's say a week ago we forgot let's say nine uh nine we got uh uh 30 additional shovels so i should show 130 total in stock okay when we when we finish our query all right so close that and 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 okay so now we know our stock in what we've received and we know the stock that's going out our order detail table all right here we got a product ids and how many have gone out and of course you have to make sure this order is paid all right so 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 alright so create query design we're going to bring in the stock in table i want the product id and the quantity received now if i run that just like that it gives me all the data but i want to 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 now when i run it i get a total the sum of quantity received so all together for all of my getting stock from this supplier right or all my suppliers i've got 130 total shovels okay save this this will be my stock in queue okay now we're gonna do something similar for stock out all right create query design now we need to make sure that the order's paid so bring in the order table and the order detail table okay we're 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 okay 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 so if i run this now you'll see some of them well okay this order didn't have now let me turn off this criteria here you might have orders that have that have null product ids come here and run there we go see some of these here they're not paid but the product id is null because we didn't put them in okay so 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 okay now we just bring in the quantity purchased all right that'll give us the quantity for each item there now we turn this into an aggregate query and we set this to sum save this as my stock out queue and this will give us a total of all the products for all the paid orders and what the sum of quantity out is okay now we just join together those two queries and we can calculate the quantity that's left on hand right the total in minus the total out one more query create query design for this one bring in under queries we're 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 product table and i'm going to link that to the stock in all right because generally you're not going to put something on a stock out order right if you don't have the stock in quantity already if it's not already if it's not something you already have you're not going to sell it usually usually when i used to sell computers sometimes i would sell a component that we normally did in stock so it would go on the order but it wouldn't be marked as shipped until we had received that product so you usually before you give the customer the product that's in your inventory okay now it 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 okay once these are joined now we can do math on these guys right so bring in the product id from whichever table doesn't really matter now 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 of the records from stock in and matching records from stock out okay so we're going to change this to an outer join number two right otherwise we would only see records where we have sold that item that's the whole reason i had to watch that outer join video right if you had like let's say shovels in stock but you haven't sold one yet if it doesn't exist in stock out queue you won't see it at all and you won't know that it's in your inventory so that is very important okay so now i can calculate my stock in and my stock out by just grabbing these values here there's stock and i'm going to rename these i'm going to use aliases i'm going to rename this as stock in all right it just changes the name and if i run this you'll see see it's called stock in now that's called an alias and there's my 130 my total okay stock out is going to be sum of quantity over here all right stock out and if i run that there you go now 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 got something like like surface it'll just show up as zero okay 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 have null values better to have negative numbers than null values okay 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 queue all right run that guy and there's your numbers right 130 of them in sold three shovels i got 127 left 50 minus four i got 46 right 20 minus zero is saw so i got three of those sold four of those sold so let's let's put one more order in the system all right customer form let's go to james kirk go to his orders let's just blank this one all right and let's say that we sold him uh five saws at uh seven dollars a piece he got a quality discount all right close it close it open up the stock queue and there you go oh 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 oh yeah 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 uh deanna troy orders all right shovels okay let's say she buys seven shovels at twelve dollars a piece paid close it close it and check our numbers and there you go ten of them sold now see not too hard right you track your quantity in you track your quantity out you aggregate them all up to get totals you subtract one from the other and that's your inventory on hand now this method will work okay if you have a small business and you don't do tons and tons of transactions if you've got a medium-sized business large business and you do tons and tons and tons of orders lots of inventory going in and out eventually this method will slow down your computer because you know you got you know ten thousand orders in the system and twenty 000 inventory transactions every time you calculate your stock it's got to run through all those numbers okay so 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 okay that way you have just one number to update and you can just refer to your product table to see what the current quantity on hand is you have to unfortunately use a little bit of vb code in here 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 than 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 on hand field inside the product table this this involves a very small amount of vba programming couple lines of code not much and then when we mark the order shipped it checks to make sure it's paid it checks to make sure it hasn't been shipped already then it runs an update query to adjust the quantity on hand okay that's all covered in the extended cut for the members plus you get the full inventory excuse me the full uh invoicing video from before where you can pick a product and hit add and then 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 of members get everything right you get all of the extended cut videos from all the previous stuff not just this one okay and as of right now what a silver membership's 5.99 a month that's it you get hundreds of extra videos gold members get to download the templates it's a great bargain awesome way to learn all right 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'll see a list of all the different types of membership levels that are available silver members and up will get access to all of the extended cut tech help 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 tech help 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 but don't worry these free tech help videos are going to keep coming as long as you keep watching them i'll keep making more if you like 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 now if you have not yet tried my free access level one course check it out now it covers all the basics of building databases with access it's over three hours long you can find it on my website or on my youtube channel and if you like level one level two is just 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 tech help page and you can send me your question there click here to watch my free access beginner level 1 course more of my tech help videos or to subscribe to my channel thanks for watching this video from accesslearningzone.com