Access Invoice Template for Communications & Media Easily and Efficiently

Access invoice template for Communications & Media

hi guys welcome to this session in Microsoft Access in this module I want to show you how you can create a customer invoice this is part of the shop database Series so first of all if I open this query sales if I just double click on that that is a query that's showing me every customer and the sales that they've made what I want to do is copy this query because I'm using it elsewhere so I'm just going to right click and copy and then paste it straight away and rename it so I'll just call this query invoice qy invoice and then okay to that one so I've got invoice now if I right click on that I can edit it so I want to change this slightly so I want this to be a particular customer so at the moment when I run this query it's a copy of it it's going to show me these people but want to be able to type a customer so if I just go in there and just do a parameter query and just type in enter name now the name in this example is first and second name so if I run that it asks me and then if I type Steve Saxton and run it just gives me Steve Saxton which is what I want so I'll close that save it first and then close that so now I want to BAS a report on this so I'm create report and I'll just pick this one and it ask me for the name again so if I do Steve Saxton it just show me the report which is very basic in what this looks like now you've also got dates in there and I haven't actually generated an invoice number but this I'll do that on a later session this is just to get this to isolate an individual transaction or several transactions by date so you can see that there's three there on the 10th and then the other two on other date so this is what I'm going to do now I'll just close this I will save it I'll just put rpt in front of it to rpt name that and then I'm now going to go back to the query invoice right click design view and then under the date I'm going to put another parameter box enter date so when I run it now wrong bracket square bracket when I run this now it should ask me for a name so I'll just do Steve Saxton again and then the date so 10th of March 23 and it shows me those three records the purchases for that date so if I save that close this down now let's just run that invoice again it's still going to ask me the same questions as a report Steve Saxton and then the date 10th March 23 and it gives me the same three bits of information but now I I need to make this look a bit more like an invoice so if I close this down what I'm going to do is Click onto the invoice and create a form a report myself so I've clicked on that I need to select the record that I want it to look at so query invoice that one and then the fields I need to position where I want them so customer name can go at the top in fact before I don't want it in the header I want it to do going grouping so I need to add a group so I'll have a customer header customer name and then I'll put that in there customer name in the customer header and then I can just push that up a little bit and then the products so if I bring the product stock across and quantity and date of sales let's have a quick look at that now this is a bit of a p pain while you're designing this cuz it's going to ask you for that all the time so perhaps I should have done that afterwards 10th of the 3 23 yeah so it's it's keeping them quite separate at the moment is just the three items so I need to tidy that up in design so if I just push this stock and then quantity over here and date over here and then I can bring this right up out of the way now we've got order value as well so if I if I bring the order value field underneath you got the order value there now at the moment I haven't got a a summary area so I've I've not got a group footer but I'll put that on so you've go down here more and then you've got with for section drop this little arrow down yeah and then you get your little footer section so my name and everything there I can just do a little sum in there so if I do an AB box so because there's three items I can just type in there equals sum and then order value it is that I need to look at sum of order value it's got to go in square brackets and then this will need to be formatted to pounds so I'll go to the property sheet format currency just type c and it'll put currency in there for you and then this one you can make this a little bit bigger this can be the invoice invoice total and then sit this wherever you want it to be basically and then you're bringing this up because this is going to take a bit of space now I want that to be bold so I'll go to format let's make it bold I'll make the custom name bold as well I don't need the label for that I just push that to the left hand margin like so and then let's have a look at what this looks like so now I need to go back to report design and view it will ask me for the name 20th 323 and I've obviously done a typo there so just go back into it run it again can't spell my own name Steve [Music] Saxton enter so it was the 10th of March 23 there we go so it brings all the stuff in you can see I've got this truncated here so I need to either get rid of the label and just move this around but that's the total for the invoice and then all these boxes are in the way so I'll go back into design and so we've got order value dat of sale don't need that label get rid of that quantity is okay order value I can just move that across a little bit there's a gap there but there's no Gap here so I might just bring this down a little bit a little bit of a space so there is a gap there just bring that down and then if you don't want these lines on so if I just highlight all of this stuff if I just come down the margin there you can go into into the properties area and look at getting rid of the the Border style so there you've got transparent as an option and then I'll do the same for this one border style transparent so you don't show The Border it's totally up to you whether you do this or not border style and then set that to transparent as well and then you can bring in the um date time and things like that off the top there you've got this information I'm on the report design you've got date and time you can choose which one you want click okay to that it sticks it in the report header and you've got Pages at the bottom there's only going to be one page here but I'm going to go for page one of one now I'm in the wrong place actually so I need this to be in the page area so pages and Cent will do actually but page number of number don't know why it went up there just cut that out put it down here where you want where you want it to go just paste it down there and then position it and then your logo if you've got a logo you can put that in there uh on the page and then any any any text that you want to put like this is your invoice for this period I can put that in there in the page header just type that in there this is just a text box this is your invoice and then position that wherever that you want that to go just put it there and then you have a look so I suggest you before you do the paret query probably just set this all up so you can see how it works so let's have a quick look so type in my name again Steve Saxton enter and then the 10th of March 23 so now it looks a little bit better so you could maybe put the date of these transactions at the top there as opposed to having it is separate lines but order value £164 total value £1 page one of one and that's the date and time and then this is your invoice where youve got the date and time there you can put some more information bigger information company logos you may want to put that up there so it sits at the top top but it's totally up to you but that's okay I'm going to save this and call it rpt main invoice so I can remember what it is click okay to that close this down and then you just have a look at the table sales and let's go for who else has had more than one thing Dave Jones has bought two things so if I run that report type Dave Jones I can't remember the date so I'll have to guess at the date but it's probably the same no not so let's have a look at the date Dave Jones 12th of March is was so go back into design on this one and then just view it again type Dave Jones and then the date is the 12th of March 23 and there you can see the two items for him and the total cost so what we need to do on the next session is is look at an invoice number because obviously there there's no invoice numbers here so I might have to redesign or add a field into the table for an invoice number but then I'll I'll sort that out and you want the invoice number to appear automatically on this invoice but that's all I want to talk about in this little session so hopefully that's of use thank you for your time time and I'll catch you on the next one