Access Invoice Template for Retail Trade with Ease

Access invoice template for Retail Trade

hello guys thank you so much for tuning into this data text tutorial my name is AKO smila I'm a data based developer at Triumph hospital-based to stock in this powerful community in YouTube here tutorials that are so useful and are going to help you in your database development Journey we do tutorials on Microsoft Access and then also Microsoft SQL Server if you have not subscribed to this particular Channel if you're a new camera indeed why don't you subscribe hit the Subscribe button and then also turn on the little bell notification icon so that each time that I put in here any helpful tutorial YouTube will notify you so that you don't miss a video in today's tutorial we are going to start building from scratch how to handle invoices in Microsoft Access so how to I'm going to teach you how to develop a database that can actually help you track invoices or manage invoices in your business and then also how to use that same database to send invoices to your customers via email if you have not subscribed kindly subscribe CPT and just be with me as I take you through the tutorial okay guys so we start the tutorial by first creating a blank Microsoft access database so we'll do that right on our desktop here so right click here we go to new then we select Microsoft Access so we type the name of the project here so here I'll say invoice management DB okay so the name of the project is invoice management DB we quickly right click then we hit open to have it launched okay now let's enable content and we are good to go so the first thing that we will do is by changing the name that we have at the top here you can see that whilst this is supposed to be the title version only the name of the current project it has also attached uh to which the file route which is not something that we prefer so to get rid of the file route here we go to file the backseat view select option now here in current database you type the name of the project alone here so we say invoice management database so it must not necessarily be what we have in here can be any other name but then I prefer using exactly what I have here okay so now we are truly going to go here so we are first first we are going to create the product table so there has to be products in the system here because invoices are absolutely concerned with products so you go to create table design now here we say products number and product number is going to be Auto number let's do away with the species here so let us make this a primary key we first save the table right click here save so we save products okay so now we are going to say batch number so batch what is the batch level of this particular project so it's a short test and I'll leave it to 10. now so I'll say products products names those are short says and I'll leave it to 25. okay so now let us say product price so let's say the price another price here is going to be currency okay so there has to be some validation over here because you know since this one is price and the currency the value shouldn't be um the value shouldn't be negative so here we see validation room we say greater than or equal to zero because the default value is already zero and then in case this validation rule is actually breached what should be the error message so we said invalid price amounts and set so please enter again okay so invalid price amounts entered please enter again okay so these are some few fields that we shall be using to describe the products you feel free you check the description of this video for the link to the this particular project and you can edit it in any way feel free to modify and add varieties of fields I am just showing you the engineering okay so now we go to the data sheet view here okay so guys let us add some couple of products so here's a batch number then also a product so let's say again tools prices 20 so let's set it up products now let's say about Palm macro so the price let's say is 13.69 so another products so let's see Circle mods is 12. China products so another product let's say I is microphone and let's say it's 120 okay so uh these are some few products that will enter for now will be coming back later on to updates now I have an insert uh happened created your products table what we do is we create a form because you know that every table has to get a form so we go to create here from Wizard so now let's click this double arrow button to add all the fields now go to next Okay so let's again the table the form type is two column now we go to next so here let's say products frm so let's view this particular form okay so this is the form that you've actually managed to create let us get behind it and make some few improvements to it okay so the first thing that I would do is to set the length of the fuse here the same so I'll right click once selected I'll go to size and now hit I'll select why this okay so what this one has done is that it has actually extended all of them to uh the widest the one that has a widest size so here I will do away with this and I'll pull this one here Now record Property sheet Adda to make sure pop-up is set it to yes so we select everything here now phone size 12 is okay color is also black okay so now the next one I will do is to select this in our line right and I'll build it as well if we double click here record selector no navigation we can actually leave it yes so let us see the form okay so this is how the form is actually looking now we are not going to do much to the phone designs I have a lot of videos in my playlist you can check it out teaching how to create forms in Microsoft Access okay so now finally let us set the alignment and add some couple of buttons here so we go to design view again so alignment we set it to right now we can change the color here so let's say we are sticking to this Okay so okay so now we have this here you can use this to search any product of choice so you can see motor and here we have it shown okay so the next thing is that we add buttons to this particular form so here in the condition menu make sure design tab is selected click the button line the button objects no so the first button is going to be record operation add new so next then here I'm going to say add so let's hit finish let me extend it now the next button is going to refresh so we add it here so now we come to form operations refresh from later so here the name is change it to save finish okay so we resize it neatly here okay guys so now you can also feel free to add a button that can do the deletion for us so without not much we do let us add that button so click here record operations now delete record so test delete finish okay so now we have successfully set up our product form so this time around we can add a product with this particular form so let's try it out so add now the batch of the product so let's say so what is it what is the products here so let's say Arabian keyboard 12. the price let's say is 300 now we see so now that I received you come back here and refresh and I can see we have your Arabian keyboard neatly shown over here okay so now we are done with products so let's see if now the next table is going to be the customers table so we're going to create table design so here we say customer ID and customer ID is going to be Auto number so primary key so now we say customer name and full size so let us allow only 50 characters here so we can also track the dob so dob now I come to caption so the field name is DOB but the caption should be verb Beats or you can see traditionally date of birth okay so now customer ID customer name date of birth so let's take address of the customer address I will allow 50 now let us take the customer's phone number so this one is shortest and I'll allow only 12. so we can finally take the customer's email and then we also allow over here 50. now for the email we are going to set up a default value so for fuse without emails we are going to say and Microsoft Access should put this in that particular uh cell so we say as example sites.com okay so soon we'll view the table so that you see the effects of what you're actually done here now we are done right click you save save customers okay so let's click here to view this particular table so now here you can see that by default the email is at examplesite.com okay so this will be a tip to the data entry Personnel so now the first customer will be myself so I'll say smiler.com little bits so let's say 12. November zero zero which is incorrect address I would say kasoa so phone number you can actually put some random buildings there so let's see Crystal so let's say third June so 2002 now let's say we'll just say so here okay now let's refresh let's add one more customers where's the events Activa setting to face November 1994 so here we'll see so let's say it's still Accra okay so now let's type it's a random because there as the telephone okay so you feel free and then modify uh whatever that we have here modify it in any way that you actually prefer because the link is right in the description okay so now that we have this here so what we are going to do is we will need to create the form for the customer so the customers form so now because we already have this form here we just right click copy then Ctrl vintope so we change it to customers so there's a quicker way of creating forms because ideally you will have a lot of phones created to create and if you would have to be creating them one by one it is indeed going to be such a dow hearts and task so why don't you just duplicate so now that we have this one here let us see how to customize so go to design view now the first thing that you will need to do is to get rid of everything that we have in here so the buttons will be the same sorry so let's understand this here so here the color let's use a suitable color so this is okay now let's go to the product form also change it so we want to maintain this color throughout here okay so now the first thing I need to do is to change a record source so double click here so you can see that although it's a customer's phone but then it is told what the records on this product so we click here and we select customers okay so now once you click design add existing field you have the fields of the table here to add to your form so we hold the Ctrl key once we use the left Mouse button to select all these here so now we are moving everything we click bam now we leave it here okay so let us select size Wireless so over here too we extend it a bit so let me extend this one also now highlight everything here so let's say 12 it's okay we leave it at Black okay so now here we bring this one here so you can see that the button because each time that you copy and paste a form or you make a duplicate of an existing form buttons will gray out so to fix this you select all the buttons sorry select all buttons now you can either choose to go to this side or this side click here and now you will have it or you can just choose any of the designs here and it will actually change it to the original colors okay so let's change the caption of the form so go to property sheet format tab so it is no product this time around it is customized okay so let us preview the form okay good so guys now we have our customized forms also set up idly here so let us set alignment also so I will extend this a bit because the customer's name could be very lengthy left alignments okay okay so now let us neatly group the button also here so to change the designs of the battery go to formats quick Styles we have a lot of designs here but then I prefer using only what we have here to make things so simple for us in this video okay guys so now we close now we move on to the last but not least table which is the invoice table so now here we have to pay very much attention here so we go to create now go to table design okay so we are going to say order so here we say order ID so order ID we change it to the data type needs to be Auto number so let's make this one the primary key a full free okay so now here who is ordering the customer so we say customer ID so now this customer ID let's come to the customers table here there's going to be a relationship and you know the data type here is number so because we shall be building a relationship with changes over here to to number mind you you cannot have two Auto numbers in the same table here okay so now number now that we have this one as number so we are going to go now here so we say order ID customer ID then we say order dates so this one will be dates or time simply click here and choose that okay so by default by default the order date is supposed to be equal to dates so now these dates is a function in Microsoft Access whose output is a system time as a system date here sorry so it means that the outputs here will be 11 9 20 20. so in no time we shall see it so order ID customer ID order dates and now we will need the products so we say products okay so now here as we have the product here so we change it to number now here we come to look up so click here we select combo okay so welcome so Steamroller query is okay so now let's define the resource so go to the Builder icon products okay so we need the product number and then the product name so Ctrl s to save now we close okay so here column count is two but when you come to column wave we are not we don't want to there's no need showing product number so type zero colon one okay we hit the top key so now we are going to go let us see these table so right click we hit save now see voice order so order TV so we have order ID the customer ID the order date the products so we can also bring in here the order time or you can just choose to leave it as it is okay so now quantity so quantity of the products this also be a number okay so let's say the quantity will also be a number the default value zero now the validation rule is greater than or equal to zero so even quantity cannot be zero so let's make it one to average any kind of troubles so it's by default it's supposed to be one so we say it if this validation is good we say that c you entered and invalid quantity now we see okay so we are going to go at this phase so let us view the table here so now here you can see that the order date by default appears as this so before we start uh to enter values in here let us first Define the form for this particular um invoice table so we don't know much we do we duplicate a customer stable as such so right click copy and I paste so you say order fr okay so here in order frm design view so here data we change it to invoice or you can click here and select invoice okay so you can see that now these ones have all got some green flag in their Corners meaning that their record Source cannot be found in this table because you don't have any food like email or phone in the invoice table so select everything and get rid of them so first we choose to add existing Fields so here we select everything and we bring everything here bam okay so now here we'll bring the label here so we say order form and then Center so phones I'll use Erasmus so let's use 14 black is okay okay so now let's stretch it so right click size you go to wireless okay so let me bring this here now I change it back to this okay so the final thing will be the caption here so caption we change it to order so we change it to all this now the color here we set it to Black full size 12. okay so now let's see the order table sorry the order form so this is the order form as we have here now we shall be inserting another cell phone here that will show us the audience that a particular customer the current customer is making so before we do this we make another duplicates of the customers form so right click copy now you paste so here you see customer invoices okay so we go to design view again so this time round we need a form header so we right click here right click anywhere here now it's a form header or footer you bring it out here so sorry it was already there remove it so I click it again and now you can see I have the form header if you want to remove it click here now you just tap in here and it goes away so let us bring it back okay so what we are going to do is we change the color of the header also so this so this time round you will not need little bit who wouldn't need the address so I'll choose to show only so at ordering at ordering I will choose to show you this one so let me click here and paste now this form needs to be large so let me click and drag here okay so if you have a customer photo you can come and place it here and it will make your application look so good okay so now here let me stretch so now what we are going to do here is that the other form that we created here it needs to be added to this particular form so we click now we drag it here back so let me let us remove this we choose to place it here I'll right click special effects let me choose sank okay so now we are going to build a relationship between this form and this form so you highlight the sub form you go to data now Microsoft Access has been so smart and smartly has it it has built a relationship using the customer ID so now we are good to go let us go to the phone view so you can see that you wouldn't be typing a IDs of the customer what you just need to do that for each customer selected you click here products the date automatically comes unless the quantity is two now you save so this has been saved I'm going to add again so let's say the same customer this product so let's say 30 now we see so this has been saved now if you want to go to a different customer you can search here let's say Sandra our events so events now you can see the ID is now three so you move on and then add the products for this customer each and every blessed day that a person comes so this is saved go to add so you could because you will confuse that nothing is really showing over here because these buttons had not been made interactive enough but then we are still making progress so we see so now let us go to the invoice table and you can see the invoices that you've actually placed just now okay so let us make this button here the save button in particular more interactive so we go to design view now click here navigation I wouldn't show it so this time when I go back you can see there's no navigation here and this makes it look so beautiful so you go to design view once more okay so now let's select this button so we go to events now the on click events I will delete everything here I'll click the Builder icon code Builder so here I'll say dim and key so do CMD does refresh to cmd.c now you can see NK is equal to MSG box so record so let's say products added to cards successfully so DB information now we see added two cards okay so you close now we are done so let me close the form again and I will open it once more here so this time around uh let's say Smiler so let's see crystal ball products so this is the invoice the product that the person would like to order so 20 and I'll see you so product identity card successfully and as soon as you come here and refresh you can see it jumps right in here so the same crystal ball on other products so let's say choco mods three now you save okay so now you can see that if we hover the points all over these buttons a degree out to White which is not some which is not professional so let's go back and change it so go to design view so design view here we'll select all the forms now go to formats so hover color is set it to highlight press color highlight so over four color zero zero press four color zero zero so now we save it and this should solve our problem for us so this time around you can see that it is now fixed successfully here so by default the date will be today's date but then you feel free to change it if they need be but ordinarily this is how it's supposed to be okay so now if you want to place an order what you do is that what you do is that you quickly okay so now we are done with the order form the next thing is for us to do the sales calculation and then also show the cards here so with that we'll need the aid of a query so we're going to create query design so here in query design we are going to need the customers table we are going to need the invoice order table okay so now what we'll do is that we bring all the invoices here so if you run this query to give you all records in the invoice table so now because of this relationship we can choose to show the customer name address and the phone which you will need on the invoice so here we run and you can see but customer ID the associated customer name address in the form so let us make progress okay so now the most important thing is the calculation of the sales so let's go to the blank column and do the sales calculation so right click here you go to build now we see cost so you cost let's make it order costs so call on here so this will be the name of this blank colon now we are going to say invoice table dot quantity times so invoice dot quantity [Music] okay so now uh guys let us cancel this now you can see that we only have the products but then there is no price so we need to add a product table to this query so that we can fetch the price so we show the tables and our products okay so here we are going to build a connection from here to up to here so now you get a price here you double click to add and now you can see that for each product the price so this price was looked up from the product table here so for Gen tools is 20 so let's see whether it is correct so you can see the agent was indeed is 20 here so we already have the products and the prices and have been fetched for you in the order so as soon as the customer makes an order the system uh fetches and brings the price so now we are going to calculate order cost so we are going to say invoice table so dot quantity now times the price so times products dot price so okay now we run so here it is telling us that 2 times 20 is 40 which is correct so 30 times 13.89 is this so it's also this okay now let us right click receive this query so we say invoice query extended okay now also we are going to do a customized serial creation for uh a customer sales or orders on a particular date because on a particular day a customer will order about 10 5 any number of items and you can see that the order IDs here are different but then if you want to get all of them together on a report for a particular customer and then we have to create a unique Syria for a daily sales for each customer so to do that we still go to the design with the query it's so easy right click here so now you see sales code so we're going to say sales code some of you can also choose Syria so now for the in Excel code for every question on a particular date so we are going to say the day so the day of the order dates you concatenate that one with the months of the order date so the concatenates that one also so you can see that all these are all the arguments of dates so this one is also here we needed here of the order dates so basically what the whole of this is just identifying the teeth the person is making the sales now we just concatenate with the customer ID so each customer ID so the customer ID together with the dates so we hit ok now once we run so now let's read this error let's specify for customer ID could refer to more than one table listed in the query SQL query which is true so let's debug right click build so here and now what Microsoft Access need us to do is to tell access where customer ID is being looked at from so we say customers so customers dots customer ID which is okay now we run this query so now so let's take for instance um customer number one on this particular date or do limit so you can see that the sales that a person needs the order IDs are different but then they all have the same serial so we'll be able to show this so this will be able to be shown on the on a particular report or the invoice as the oldest place by the customers so when I report we are going to make use of this highly that is the reason I need to generate your sales code so let me go over the sales code because it's very much important so design view right click build so the date you fetch the day months and a year the income companies the customer ID then you run here okay so now that we are done let us create a very quick form editor sheets to be embedded in the order form before we create the invoice finally so we're going to create form so let me save this okay so I will need only order ID order it products quantity then so I will need other course then seal code yes so this form via data sheets next now so we say cards so cut now this is okay for us here Okay so what I'm going to do is alternate colors I will set it to White and now let's go back here design view so data now we shouldn't be able to add records here we should be able to edit records here also so you said all those ones to know so let's go back again sorry data sheets now we select everything color I'll make it 12. okay so guys now we are good to go but then the final thing is for us to show the order called the total so you click totals and now here select some so okay so having done this we close everything now you go back to your other form design view now let's stretch it here so we stretch it out here now Cuts will be dragged here sorry let me type c here okay so I'll close this okay so now let me extend this now you make sure you set a for this particular form horizontal should be both vertical angles should be both here too I'll make it a sank form okay so now let's see so let us do the relationship well here okay so now here we click here so we click here now we go to data so you can see the relationship is now order ID order ID so we need to use customer ID and then customer ID okay so once done you save so now you can see that it is giving you a track of all the orders that a particular customer has placed but then the sales code will be unique pay each day so now our order form so let's open customer invoices so we need to stretch this one across here so let's stretch it out here and now here to we set the anchoring to both so to both so now also once on the events the onload events of this form let us maximize it so we say maximize window so as soon as this form is open let me close and open again customary invoices so now guys you can see it is now looking so good so should you add one more product to smileless stock cut let's see Obama macro next place a different one nice let's say five three so here you can see it I jumped in here quantity is three and then the price so we could upload the price and this is uh so you can choose to add the price to this data sheet also here so a different customer and if you add a customer out there it will actually appear over here as well so let us add one more products for this so quantity let's say three now once we see it jumps in here if you make any changes it jumps in here okay so guys now progressively we are done with all the tables in the form now the Final Phase is for us to be able to print the invoices so before we get to that this needs to be a hyperlink so design view now cell code you go to format it's hyperlink is set it to yes okay so this is how it will appear so it's a hyperlink so when everything is done this will now be clicked to display the invoice report for us successfully so let us go and create an invoice it's very easy so now you open the invoice query extended you want to create report wizard so on the invoice we are going to need order ID so products we need quantity we need price they will need other cost okay so here move to next next so let's stand by the order ID nest okay now here you choose tabular for this report so let's preview the report here okay so this is the original invoice report so let us modify its design view so you click here shape our line so I will choose how to block so that it looks good now feel I'll choose transparent so let me select everything here color needs to be black one side needs to be 12. 12 is looking so big but then it's okay let's change this here okay so now now we wouldn't need this so we can do this also change it neatly so you can say the rightful so now the whole of this now you feel free to design your invoice so we are designing the invoice here so now let's click here alternate I didn't need it to be alternating and I report view so now this is my invoice okay so now let us add some couple of styles so I'll close the gaps so I'll close gaps here I'll close the gaps so we close here so it's okay to me because of my laptop screen so meaning actually this one was 14 supposed to be 12. okay now we click here so the size so order costs it's too big it's too big so let me resize it here now click here and fill it white okay so now we are going to show the total of the order cost here so we go to design totals sum okay so now here this is going to be the grand sum so let us bring a label here so we say grandson or you can say amount payable so a month payable so format painter we copy and print it here okay so we can actually bring it closer here so let me select size tallest okay so now the next instance is an invoice so we are going to say so we're going to say invoice we are going to say invoice Center align so let's choose 18. 18 is small 20 is okay big okay Okay so now you're going to say invoice so invoice let me bring this one here now I will set a logo browse so let's go to downloads I have a logo there so I'll bring my logo right in here so invoice Okay so now I have the invoice here so let us add so now we need so the invoice the sale code we'll bring it here and this is going to be invoice so invoice number so this is going to be the invoice number so we select so now size 14 so this is okay she feel transparent okay so invoice number now you're going to say so we're going to say Bill to so Bill to so which person is this bill going to so click here customer ID customer name address phone so click and bring everything right in here so a bill is going to I wouldn't need the labels so I wouldn't need the labels okay so now go to Center align let me bring this also here in Italy so we group this so guys we are almost successful with our projects so now click here extend this so now so now the next step is for us to change the size here this work is okay and it's black so black is okay shift fill transparent okay so now let me preview the invoice so now this is an invoice so it needs a lot of design in the game so we select everything here before double click here to show the property sheet select everything here now order sorry outline you set it to transparent and then also there's a couple of settings you need to do pop-up need to be yes and then also to Center is yes sizeable we set it to 10 scroll bars you don't need any screw bar here but you can choose to show vertical but for now let me show you neither of them okay so now there has to be some buttons here let me bring this one here so there has to be buttons so design I'll bring only the print button here so print I'll say prints so let's do two buttons print and press preview copy and paste so we'll show all those buttons here now this one will be friends preview and I'll just click keyboard line quick styles I will now select this okay so now right click build events macro so this one the command will be friends objects now you save it so here to right click you save prints preview okay now that we are done let us show it okay so as soon as you click print preview it opens to show your invoice so over here you need to do some couple of alignments here so go to design view once more so align to left okay so now once we are done so we'll be able to let us modify the build tool so it has to appear here okay so okay guys so now that you are done with this we close let's see if now we come to customer invoices design view now I know that this was a hyperlink so we are going to add a code I will go and open the invoice so we right click viewed events Mercury Builder so here we say open reports what's the report's name so you can click here we have only one reports invoice but the work condition is going to go equal to cell code is equal to end now you bring quotation inside you put apostrophe there and sell code and the same thing quotation inside with apostrophe so the cell code here you can close it in quotation now we are open to dialog mode so we say okay so now guys let us close and open our project finally to see our progress okay so now for smiler's orders on this day if we should issue the invoice we expect to see three items sum up to each 16.7 so here you can see that smiler's invoice has now neatly been opened here so let us open a different presence so Crystal 26363 items so now you can see the details and now you can see the order cost also I see so let's try once more so now let's say you want to add another item so click to relax so quantity three you see let's add one more so keyword let's see four now we see now as soon as you hit here it opens to show you the total and then so if you want to also put in the comma here then change the format over here to standard now there is a little issue guys so our celebration is not complete now if we print preview you know that print preview shows you how your printout will look like but here like the case that the image here the logo is missing sometimes this is an issue but then let me show you how to fix this to fix this and close everything now what you are going to do is that you go to create a blank form so design view this would be a very small form awesome so you save it I'll call it logo now this form I wouldn't need record selectors nothing in here I wouldn't need scroll bars so what I would do here is that I will insert only my company logo here so sometimes if you face that issue this is how to solve it now select the image size mode you select stretch okay so now you are way successful now that you have it here what you do is that you insert this one on the report but make sure that the outline of this particular picture you set into transparency so now go to our invoice reports so here remove this logo here we bring it right in here so remove this place it here outline you select transparent okay so now let us try it once more and see okay so here print preview and now you can see that the image is now showing neatly here so we try once more and now we have it neatly showing here print preview so now let me correct invoice so this is invoice so this is invoice so guys once more and now you can see that our invoice form is now so now we have been able to successfully complete our invoice creation or handling database so um you feel free you'll find a link to this file in the description below yeah feel free to improve upon it if you have any question a suggestion if you wish we had done it in any other way kindly feel free to put it in the comment section this is a community whereby everyone's need is actually very much important if you have any special consultation to do check down the description for my email or Whatsapp number you contact me then we talk it out appropriately guys feel free to share this video with your communities your friends so that the channel will continue to grow and thank you so much for watching in the next video I'll be showing you how to send invoices to your customers via may also stitching thank you see you in the next video bye