Download Your Free MS Access Invoice Template for Purchasing

Ms access invoice template free download for Purchasing

welcome to another tech help video brought to you by accesslearningzone.com i am your instructor richard rost in today's video i'm going to show you how to set up customer specific product pricing for your microsoft access order entry invoicing system i'm going to show you how to set up product pricing that can be unique for each customer so if you've got a specific customer who gets a special price on a product or two instead of a blanket discount for example you can set that up and i'm going to show you how in just a minute today's question comes from gianna from montreal quebec one of my platinum members gianna asks i'm using your invoicing template which is very helpful thank you you're welcome is there any way to set up pricing that is specific to each customer i have certain customers that get special discounts due to the overall volume they purchase it's not a blanket discount i'd like to modify the prices of certain products just for them well yes of course gianna there's a way to do pretty much everything in microsoft access you just gotta know how with this specific example since it's not a blanket discount like five percent off of everything or ten percent off of everything what i would say we should do is set up a second table that has a list of the products and the customers and their special price so we use a new table for this so let's see how this would work now before we get started there are some prerequisites there are some things you have to know before you finish watching this video if you're not familiar with query criteria the if function the immediate if function or the dlookup function then go watch these videos first you need to understand how these work before continuing so pause right now if you don't know what this stuff is i'll put links down below in the links section you can click on those or go to those pages whichever you prefer everybody watch that stuff you good you good okay here we go now gianna is one of my platinum members so she has access to the members only invoicing system which allows her to pick a product from a list and then hit the add button and it adds it up here into the invoicing automatically and her pricing is stored in a product table gianna i will address how to handle this specifically in the extended cut for members for everybody else the non-members i'm still going to show you how to do this you just won't be able to pick a product and add it automatically to the invoicing form that involves a bit of coding some vba which is why it's for the extended cut you guys still have an invoicing form in here there's just no way to pick a product you have to type it in but that's okay we can still make a product list with customer specific pricing which by the way that's one more video you should go watch if you haven't watched it already my invoicing video it's free it's on my website on youtube it's how we built this entire template okay so go watch that if you haven't yet okay so now we're back here in my tech help free template with the invoicing let's set up a product list so we'll set up a product table all right so create table design a product id that'll be my auto number a product name short text and a price that'll be currency save this as my product t my product table all right no primary key say yes and then we can put some data in here let's say we've got a phaser that's a hundred dollars we've got some triple food that's 35 and we've got a 3d chess board and that's 75 dollars okay just three products that's fine for now now we have to set up a cross reference table between the customers and the product so i'm going to take this product table and slide it over here so we can keep an eye on it all right here's the customer table there's our customers let me just slide this over next to it over here okay we're going to make another table now that brings those two things together this will be my customer pricing table so this table gets its own id customer pricing id that's the auto number we'll need a customer id now that's a number of type long energy that's a foreign key if you haven't taken my relationships video go watch that too i'll put a link down below you should know relationships before doing this stuff guess i should have made that prerequisite screen a little bit bigger huh so we know the customer now we also need to know the product right product id that's a number of type long integer that's going to point to the product table and now what's the custom price so price that'll be a currency all right let's save this customer pricing t all right primary key is this guy right here okay save it let's go into datasheet view and let's add some data all right so customer id let's say customer one that's me product the phaser product one all right now normally people pay a hundred dollars let's say i get it for 95. okay all right same customer let's say the 3d chess board let's say i get it for 60 bucks okay triple food it's a commodity no one gets a discount on that so no one gets special price on triple food okay let's go down to jim kirk customer two he gets phasers for 65 because he buys a lot of them right and let's say the 3d chess board um let's see that's product three so he gets it for forty five dollars okay see how this works you set your customers here you set your products here and there's the price so now all we have to do is set up a query where we list all the products and then we say hey is this product in the table for the customer that we're currently making an order for all right here's how we do that so i'm going to just take this and close that close that close that save changes sure now how do we know which customer we're dealing with well let's make sure that the customer form is open okay for us to get the customer specific pricing or we could base it on the order form whichever one you want i'm going to base it on the customer form because usually if the order forms open the customer form is open as well in fact with this database the way we have it organized you can't get to the order form without going through the customer form first so that's okay so unless someone comes in here and manually closes the customer form this should work just fine all right so from the customer form we're going to use the customer id off the customer form in a query okay here's how we're going to do it ready create query design we're going to bring in the product table okay close down the all tables we're not joining it to anything just bring in the product table so bring down all the fields if you want to and now we're going to create a custom field right here i'll use the zoom key shift f2 to zoom in so the text is a little bit bigger okay we're going to call this one custom price and i just typed in customer and hit backspace my fingers are just so used to typing in the word customer because i do it all the time for classes custom price we're going to use the dlookup function so it's dlookup what am i looking up the price field from customer pricing t that's the table what's the criteria customer id equals closure quotes and then we're going to concatenate forms customer f customer id but we're not done yet and the product id equals whatever the product id is in the current record see that right look up the price from the customer pricing table where the customer id equals 6 or whatever customer happens to be open on the form and the product id equals whatever this product id is over here for this record hit ok let's make sure this works first i'm going to save this as my customer pricing cue all right let's run it and see what we got okay look at that now the customer it's open is one okay so look at this there's my custom pricing see 95 for the phaser 60 for the 3d chessboard nothing for the triple food so that's null we'll deal with that in just a minute that's where the if function's coming in all right let's change this let me close this let's change to jim kirk now let's run that query look at that there's jim kirk's pricing 65 and 45. all right let's go to somebody else who doesn't have custom pricing and they're all null okay looking good so far all right let's go back to me design view now we have to deal with that null value so this field here is where we'll say if that price is null use the default price otherwise use the custom price and i'll zoom in for you so you can see it better alright here we go my price colon if iif is null custom price comma price comma custom price all right hit okay what does that do well it says if custom price is null this guy here then use the default price over here from the product t otherwise use that custom price value save it and now let's take a peek and there we go see 95 because it's got a value that one's null so it uses the 35 okay 75 all right but i've got 60. now you can format this as currency but sometimes with these calculated fields the format doesn't stick if you right click go to properties format currency all right save it close it close it and then run it again see it doesn't always stick with a calculated value but you can force it with the with the format function so i can come over here and say um final price let's call it is format my price comma currency just like that and then yes i've got lessons on format properties too now run it and now you can see it's formatted as a currency value now what i would do is make another query because you have to display all of this stuff for this to work if you hide the price for example then the calculations won't work but you can make a second query and then pop that up for your price list so close this one save changes yes create query design bring in customer pricing and what do you want to see for each customer the product name and the final price maybe sort it by product name okay save this as customer pricing 2q perhaps it's based on the first one though all right so now you just run that and there's this customer's specific pricing so now to see a list of this customer's pricing when you're making an order for them you can either put it on the order form or on the customer form either way come over here design view right make a button down here button drop it right miscellaneous run query customer pricing 2 right specific pricing next give it a name if you want to pricing button and then finish here's your specific pricing button for this customer save that and now you can open it up that easily see and if you want to add any more specific pricing for this customer just open up the customer pricing table and add it in here and yes in my full classes i show a lot more you can create a little sub form down here where you can pick the product from a list and give them specific pricing that way there's all kinds of stuff you can do i only have time for a brief introduction to it here but i show a lot more in the extended cut and in my access developer 28 class where i cover more specific pricing for example if you're putting in an order for a customer and you change the price here it'll ask you if you want to change it in the table so that's all covered in developer 28. okay want to learn more in the extended cut we update the members database where we have custom pricing showing in the combo box when you go to add an order you can see here richard ross that's me i paid three thousand dollars for a photon torpedo but will riker over here only pays hundred and one dollars then you can click on the add button and it goes right up here that's in the extended cut for members silver members and up get access to all of the extended cut videos gold members and up can download all of my templates from the tech help videos and if you want to keep learning even more than that microsoft access developer 28 if you change the price on the order it'll say you want to make custom pricing for this customer then it will take that price and add it to the custom pricing table plus i'll show you how to make a sub form on your customer form where you can see all of their custom pricing that's an access developer 28 i'll put links to all this stuff down below in the link section 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 you