Streamline Your Operations with the Invoice Model for Management

Invoice model for Management

welcome to another tech help video brought to you by accesslearningzone.com i am your instructor richard rost this is the big one this is the one a lot of you been waiting for it's over an hour long it's invoicing building an order entry system and an invoice report it will also include a free template of everything that i build in the video which you can download for free off my website you'll find a link down below this video will show you how to build a complete order entry system where you can enter products prices send out invoices and lots more today's question comes from scarlet from atlanta georgia one of my platinum members scarlett says i know you have lots of lessons on creating an order entry system with invoices i don't have time to learn all of that or watch 20 hours of video can you show me how to put together something quick and simple i just need to be able to invoice my customers for a few products and my labor that's it i plan to watch all of those lessons eventually but i'm doing my billing now with word and excel and it's getting cumbersome help i actually hear this a lot scarlett and in fact a lot of people come to me and say that i just started a business but now it's starting to get rolling i've been using word in excel and it's just getting crazy what can i do to put this in access that's one of the reasons i started teaching that in my access expert series lots of people want to learn how to do order entry and invoices in access and it's not terribly hard but it's not super easy either now you're right in my full classes i go over lots of stuff all the different options collecting sales tax making quotations and invoices inventory management all that stuff so if you don't want to take the time to learn all that right now let me take a few minutes here and show you how to put together something simple that will look professional and get you started and you can always add features later that's the benefit of building your own database with access i've actually been wanting to put this in my free blank template that i have on the web so people can download that and start using it so let's get started now i'm going to be using my blank customer template as a starting point if you haven't watched the video on how i build this go watch that now i'll put a link down below in the links section now i'm going to be referring to some concepts that i've already covered in my other free tech help videos so if you haven't watched them at the point where i mention it then go watch that video you're gonna have to know some stuff like relationships combo boxes calculated queries criteria that kind of stuff i'll show you everything you need step by step right here and you won't have to do any programming or vba but there are some other concepts you might have to go refresh yourself on if you aren't familiar with them but don't worry i'll put links to everything down below all right let's open this guy up first thing we have to do is build a table to hold our order information so let's go to table design this will be our order t so we're going to start off with an order id that'll be our auto number we're going to put a customer id in here to store the customer number that's a number that's a related field if you've never done table relationships go watch the relationships video also i should mention at this point if you're going to be printing invoices there are some numbers you may want to hide from your customers you don't have to worry about that right now but as a beginning business if a customer sees that they have customer id number six they know you don't have that many other clients likewise if you have sequential order numbers or you use auto numbers for your order numbers and you put those on your invoices again a customer gets invoice number 100 today and then 113 three weeks from now he knows you're not doing that much business now i've got videos that address those topics they're a little more advanced again i'll put links down below about sequential order numbers and hiding customer ids but for the basic simple database you don't have to worry about that how about an order date that'll be a date time field and down here under default value i'm going to put equals date like that that'll default the current date in the date field now if you want to put the time in there as well you can use now equals now it actually makes some of the calculations a little more difficult later on when you do things like accounting you have to take into consideration that you know an order that comes in on december 31st at 5 pm is greater than december 31st at midnight so you have to be careful with your calculations if you really care what time of day your orders come in then put now there if you don't stick the date it makes the accounting easier how about is paid is this order paid that'll be a yes no value default to no of course and again yes in my full classes i cover taking partial payments toward invoices and all that crazy stuff but for a simple template today we just cared about whether it's paid or not how about a description that'll be a short text field and that's good to have like on your order list you know you could put like you know four computers or a basket of fruit or whatever and then the actual line items will go on the order later but this is like a brief description if you want to use it you don't have to and likewise i like to always put notes as long text or memo field in there why just so you can put you know delivery instructions or any kind of craziness that you have to put on the order itself now at this point you're probably thinking about what about bill to address ship to address yes that's all stuff you could definitely add to your order table and i do recommend it for the long run however to keep things simple today we're just going to use the customer's address on the customer table it is better long run to save that information in the order table as well and i know i talk in my beginner classes about not duplicating information but one of the exceptions is history if you want to save a history of your orders for example joe smith lives in new york places an order today six months from now he moves to florida if you update his customer record and you don't have his old address saved in the order then it will change where it looks like the order went so saving things like the customer's address billing information in the order itself is important and i cover this in my more advanced lessons but for now if you're just going to print off an invoice and not worry about it you'll be okay you're probably also wondering about an order total don't worry about the order total we're going to calculate the order total based on all the items in the order we're going to add them all up and use a sum function sometimes for really really big databases you may calculate it and store it in the order table just to speed up your reporting but we're not worried about that right now and you can add any other fields in here that you think are relevant for the order as a whole uh the due date when the payments do if you want to put that in there what the terms are um all tracking numbers any kind of thing that you want to put in here that has to do with the order but we're going to keep our template simple feel free to add whatever fields you want i'm going to save this as my order t my order table primary key yes there we go that's the auto number okay so now we have the order table saved and that's information on the order as a whole now we need information on the details in that order so let's create another table table design the details are basically the line items okay i call them order details so order detail id that's my auto number order id refers back to what order it's on do we need the customer id here no because these are line items on an order the order table has the customer information right we can go back and figure out which customer this order belongs to because we are in the ordered detail table which is related to the order table which is related to the customer table those are all one-to-many relationships and again if you don't know that go watch my relationships video very important stuff i have a whole seminar on relationships now in a full-featured database we would have a product table where you can pick your products and add those to the order we're not going to get that complicated since scarlett says she has a very simple needs right now so at this point you're just gonna have to type in the order information if you only have a couple of products you can type them in and their prices in the extended cut for members i will show you how to pick a product from a list but for now we just have to type it in so we'll have a product name that'll be short text then we'll have the quantity of that item purchased that'll be a number i'm going to make that number a double and i'm going to make the default value one why a double well what if you're selling things in fractions what if you're selling a pound and a half of salmon for example or what if you're billing for time and you're billing in 15 minute increments right you could bill for 1.25 hours all right double is my default go to for floating point numbers i pretty much always use double all right there's decimal and there's single there's a bunch of other ones i like to use just i pretty much use long integer and double most of the time okay now we've got the unit price that'll be a currency value okay you can default that if you want to i'm gonna leave it at zero now you're thinking what about the math there what about multiplying the quantity times the unit price to get a line total we're going to call it the extended price and we're going to calculate that in a query we don't need to save that value in the table if we can calculate it in a query okay next notes long text just whatever information you might want to put in there if you're billing for time for example you can put labor one hour and then in the notes but what you did you know gave a custom training session on excel for example okay let's save this order detail t i like to keep all my table names singular by the way and all my field names if i can it just saves headache later if you don't you have to remember okay was it order table or was it order t or was it orders t i just keep everything singular it's one of those nuggets of wisdom from my 27 years of doing this okay let's close that now i like to put sample data in my tables before i do too much work on other stuff so let's take a look at the customer list all right so i'm customer one james kirk's customer two let's put a couple orders in the system here so here's order t up there and let's put order detail t down below now this is gonna be a form with a subform when we get to building the forms but for now i just want to put some sample records in here all right so the first order is going to be customer one that's me all right today's date description let's put in here training no notes now down here order one product name training hour and a half at 30 an hour i remember those days okay now let's say order one also let's say while i was there i bought a mouse all right at seven dollars and i also um i got a cupcake from the little concession stand all right that was 350. okay all right next order comes in let's say it was customer two james kirk okay and he needed starship parts okay so down here now order two what do you need all right you need a phaser bank one of those at fifteen hundred dollars okay he needed some photon torpedoes i'll just abbreviate he needed ten of those at uh 3 500. and yes when i'm doing uh sample orders like this when i'm building a database i like to put in ridiculous numbers sometimes really really big stuff because you never know i built a database one time for a client and i was using sample numbers like you know 200 300 that you would normally use in an order system and they were having order totals come out in the millions and i didn't format the fields large enough on the forms and reports to display a number that was a million dollars or more and i didn't catch that until after the database was built and they're like hey we can't see any values so i had to redesign a bunch of stuff okay and he also needs some tribble bait right he needs 5.5 pounds of that right at 30 a pound okay so see how we're going with this see how the orders and the order details relate to each other order details there's order one that relates to that order which relates to customer one this just so happens to be the customer one and customer two happen to also have order one in order to but that's you know the next order could be customer six all right so again watch my relationships video if you're confused with with this at all okay you can always come back and finish this one now next we're going to build an order detail query because our order detail table has great information but i need to multiply that quantity times the unit price to get what i'm going to call an extended price you don't save that in the table and you could do the calculation in forms and in reports directly but i prefer doing it in a query because when this gets more complicated later on if you add things like sales tax and all kinds of other stuff it's better to have the calculations in a query again trust me so let's close this create query design we're going to bring in our order detail table close that bring in all the fields and then we're going to put in a calculated query column right here if you if you've never done calculated query fields before again go watch the video on that i'll put a link down below calculated query fields this is going to be a field called extended price colon set it equal to the quantity times the unit price okay and if you want to you can right click on that go to format go to properties go to format and put in here currency so this looks like a currency value where there you are all right save it this is going to be my order detail queue order detail query and now when i open this guy up there you go okay you can see the math is already done 1.5 times 30 45 dollars and so on check and make sure these look good they look good okay close that up now we're going to make the order form the parent form to hold the information about the order all right now if you've already watched the blank template video you know i've got a continuous and single form right here these are blanks i'm going to copy the single form copy paste this will be my order f this is the whole reason i did this so i don't have to keep reinventing the wheel design view all right this is order so let's make this guy green let's go with money right let's make this uh let's get that color green that looks good okay now let's bind this form to a table the record source will be order t all right close that let's bring in all the fields from the ordered table click shift click and then drag them all over here whoops i missed it ah come here there it goes sometimes it's weird sometimes if you don't get it exactly right it won't come over so it's not you it's that happens to me too we can close that field list now i keep these guys around so i can use them to format paint watch this click on that id there go to the home tab use the format painter click on order id see it's now it's formatted that's gray and that's black do the same thing with label here double click on the format painter it stays down click click click click there this guy we can do separately all right in fact i'm gonna format paint u like that there we go now i can get rid of these and let's just line these up make them look pretty now a lot of people say that you take too much time making stuff look good a lot of other people say i like to watch you make stuff look good so i'll try to keep it in the middle now this we're going to replace with the combo box so this guy's going to just slide over here for a minute okay order date can go there personally i find that making your forms look good makes the database easier to use not only for you but for other people who might not know access if you've got a well-designed form okay it's it's easier to work with okay a description can go there notes are going to go over to the side let's see order id now the order id here is just for you so i'm not worried about other people seeing it description um actually i'm gonna go a little bit later on this background just a touch let's go there yeah that's better okay order id order date right there and you can put spaces in your labels too description let's put the customer down here that's going to get replaced is paid can go anywhere you want i'm gonna put that just paid if you want to and then notes i like to put like i like to make my notes look like a little note field i do um i do that yellow or is the yellow i don't like using theme colors so you pick the yellow here and then we'll wash it out more colors that's better and then i like to give it that shadow effect yeah double click on it go to properties go to format find special effect and go shadowed yeah just like that maybe make that black or darker at least okay there we go i'm not going to spend a ton of time on formatting i'd like to show you a few things okay now the customer id we're going to pick from a list now the problem with the customer information if you look at it is you got two fields here richard ross okay and you might even have a company name field in here so what you might want to do is put those together with a concatenated field so that it reads easier inside of your combo box okay it's a little more advanced trick if you've never done string concatenation before again i got videos on that go watch those all right here's how you do it create query design literally for this box all we need is in the customer table we need the customer id because that's the feel that's getting saved in the order table so we need that okay then we're going to make our own little field over here we're going to call it l f last name first okay that's going to be last name and quote comma space quote and first name just like that here i'll zoom in so you can see it okay last name and a comma and a space and the first name all right that's string concatenation all right i'm going to save this as my customer lfq customer last name first q it's literally just the id and the last name first and you could sort these if you want to but the combo box will do the sorting for you too all right so get rid of that now we're going to make the combo box all right and again if you've never made combo boxes before i got videos on this this is called a relational combo box i'll put a link down below alright i want the values from another table or query next pick queries that customer lf that we just made bring in both fields next sorted by the lf next that's what it's going to look like now because this is based on a query you don't get that check box here that says hide the key field so you have to manually hide it just grab the width and make it zero just like that that's all you have to do because we don't care to see that number but i need it in the box to store the value all right next okay storing what value what is the linked value that customer id next and we're going to store that in the customer id field we're going to bind it to customer id next what label would you like customer and that's it and let's just format paint it now whoops wrong one there we go slide it up into here slide that over like that okay just like that let's save this close it and open it back up again see that's why i did that with the combo box because now i've got last name comma first in there that's a whole video on itself by the way let's left justify the stuff left a line i should say i always say justify it's a line left a line yeah align left okay justifies when it goes out to both margins and that looks a little better okay good all right so we've got the order information all set this is the information about the order as a whole okay and we can make that notes feel a little bit bigger here too okay now down below here is where the information is going to go about the order details let's make that next and again i got a continuous form blank template right here let's copy that guy copy paste this will be the order detail f all right let's design you and again we got to fix the colors first let's make this color the same as the other color we just used that guy there okay let's do the same with this and this but let's make the in here a little bit brighter let's go more colors and we'll make that just a little bit brighter like that now with continuous forms you got to change the alternate back color too i hate that alternate back color thing all right say that there's no way to turn that off in the template that i know of at least if you guys figure it out let me know all right let's make these labels black let's go with that and let's bind this now to our table or query we're going to use that query that we designed earlier the order detail q that way we've got that extended price calculation okay save it let's bring in the fields we need design add existing fields here they all are i'm going to bring them all in just bring them all in for now drop them down here in the form footer we're going to do some work with them here okay we don't need any of these labels chop them off now order detail id and order id we don't need them we don't need to see them i'm going to leave them in the form though i'm going to slide them over here like this and i'm going to make them hidden and when i hide something i like to make them red that way when i'm in design mode i can see them all right i know they're there but i'm going to go to their properties right click properties and i'm going to set visible equal to false i don't need to see them why the detail id is meaningless okay the order id is linked to the parent form the order form and the relationship between them the subform relationship will handle that linking of the fields so we don't have to have it there nor do we have to set a default value or any of that crazy stuff that's the one nice benefit of using a subform is access handles the relationship for you i want to have them on the field though in case i want to refer to them from some other form some other time all right if i make more stuff on here that refers back to the order detail item you might want to have it later right now we don't need it so let's get rid of these here boxes there these here boxes there that made sense okay let's put product name first over here let's make it as wide as we think we're going to have to make it and make that on the grid like so okay the quantity can go next and that should be about good i don't know what kind of quantities you're dealing with but make that as wide as you have to unit price can go next make that as wide as it has to be all right extended price goes next that's usually a little bigger than unit price and i like to make that gray just a little light gray just that indicates to the user that's a calculated value you can't change it all right now notes can go down here now i want that same note uh format that i had in the order form so i'm going to design view i'm not going to redo all that work right click on this guy go to the home tab format painter click on that guy oh i lost it hang on one more time sometimes let's see yeah see sometimes it doesn't work across forms there's an easy trick what you got to do is just take this guy copy it actually you know we can copy and paste the whole thing the field is called notes in both tables so watch this get rid of him copy paste it's the same field with the same name in both tables the trick i was going to show you is sometimes if you had two different fields let's say sometimes format painter doesn't work across forms you just copy this object over here then you can format paint with it it's a little bug i think in access sometimes it works sometimes it doesn't okay and this doesn't necessarily have to be that big we can make it like that okay shrink this up shrink this up shrink that up and let's fix our labels across the top quantity this is the product or item product service whatever you want to call it copy paste paste all right this is going to be the unit price and this will be the extended price and what's going to happen is i'm going to right align this stuff here so let's go select all of these and right align the numbers like that and that should let me line them up a little better let's do this get those right on the grid slide that up extended price and let's let's make this a little bit bigger senate price unit price and i'm going to select all of these right click size to grid i hate that stuff doesn't come in sized perfectly on the grid the microsoft team needs to fix that when you bring in stuff off the add fields box it should come in snapped on the grid in here okay let's take a look at what we got here save changes yes open it up there's our stuff now you're seeing all the details from all the orders that are in here because we have nothing to limit it the the form subform relationship will do the limiting let's put a total down here right for our order total that's just summing this guy up design view i'm going to copy this guy copy paste slide it over here like so maybe bold it all right now we have to change this just slightly the control source is going to be equals the sum of extended price again if you've never used the sum function i've got videos on that i've also got a video called form footer totals go watch that video too okay and this is the sum of the extended price i like to put up here the name sum extended price give it a unique name in case you need to refer to it later if you have to make this bigger make it bigger but we probably shouldn't have to let's see all right looks good there's our order total from that now it's time to put this inside of the order form are you ready it's pretty easy to do design view on the order form make some room grab this guy click drag drop that's it delete the label that comes in here slide this over like that make it tall like that close that up a little bit resize this stuff so everything fits nicely save it click on the sub form go to data and just make sure these two fields are set link master field link child fields those should be both order id access does that for you automatically save it close it now open up the order form boom there you go all right maybe make a little bit taller so we can fit some more stuff in here a little bit taller all right there we go save it close it open it back up again looks good now i like to turn off this inside set of these uh navigation buttons down here because this just gets confusing this is for the orders this is for the order details we don't need this we're gonna get a scroll bar over here if we get too many of them right if this is smaller well i can't do it here but that's what this extra space is for you so you get a scroll bar we don't need those let's turn those off order detail form design view go to product properties excuse me properties not product and we're finding on the format tab the navigation buttons turn those off and for scroll bars we just need the vertical scroll bar all right we do want the record selectors though because you wanna be able to pick a product over here for example if you need to delete a line item all right now save it open it back up again all right looks a whole lot better and again you'll get a scroll bar over here if you have to go up and down perfect so far one thing to take note of is our tab order tab tab okay it's jumping over here tab tab tab okay it's jumping around kind of weird down here let's see what it looks like okay the inside form is fine let's fix the tab order out here nope close right click design view this guy tab order auto order to start i want to go order id order date is next okay description is next and this just outlined for me i forgot to name that combo box until combo 10 we'll fix that in a second but combo 10 will go after description then is paid then notes then the order detail form that's fine and then hit ok and let's give this guy a name let's call this my customer combo you can call it customer id if you want to i like to call combo boxes combo i know that 99 times out of 100 that combo box is based on an id value all right so there we go there's our order form so the order entry is all pretty much set all right let's go uh pick another order here all right let's say james needs um he bought a uh 3d chess board for spock's birthday all right and that was 75 dollars all right there you go you can see the calculations are updating down here just fine another thing to take into consideration and note is if you change something up here okay the order total on the bottom will not update until you leave that record just keep that in mind again in my advanced classes i show you how to get around that you can actually put a recalculate event in each one of these so as i type something in here for example and hit tab it immediately updates down below that's more advanced i'll cover that in the extended cut for the members too but for everybody else just remember you have to leave the line item for the calculations to update okay time to make the printable invoice now we need another query so that we can bring together the order information the order detail information and the customer information because we need the customer's name and address too on the invoice so create query design we're going to need to bring in the order table the order detail queue so go to queries bring in that order detail queue because we want that extended price on each line item right that's in there then we need the customer information so go back to tables and go to customer t all right all three of these tables are related together now we're going to set these relationships equal to outer joins now if you've never done outer joins before go watch my video on outer joins basically what that does is that says i want to see all of the orders and the related information from these tables if it exists if you don't do that if you put an order in the system without a customer associated with it you won't see it if you put an order in the system without an ordered detail item you won't see it yeah generally those things never happen but they can alright you might put an order in the system and forget to attach a customer to it go to print and invoice and it doesn't work okay which might happen you might have a walk-in customer all right if you if you have a bakery and someone comes in and wants to buy you know dozen donuts or whatever you don't want to put a customer record in the system for them so you just put an order in and hit go i usually have a cash customer record but sometimes you forget to pick it okay so double click on these lines go to number two include all records from order t and the records from customer t where the join fields are equal and you'll see an arrow that says show me all of these and the related customer information if it exists double click on that go to two okay bring in all of the fields from the order table from the order detail queue i want the product name the quantity the unit price the notes and the extended price i try not to bring in the star from multiple tables because then you get all these duplicated values like order id exists in both of these tables so now you're dealing with order t dot order id and order detail q dot order id it just gets confusing we will have a duplicated value with notes because we've got notes in both of these tables excuse me both of these tables right there's a notes field over here that's okay we can i can deal with one of them all right notes will be in both tables that's fine not a problem from customer t just bring in the customer stuff that you want on the invoice so in this case i've already got the customer id i want first name last name i don't need email bring in address city state zip and country so we can print that on the invoice we don't need the rest of this stuff okay save this as my invoice r all right my invoice report or if you want to keep all your order stuff together let's do that let's keep all of our order stuff named the same let's call this order invoice right click rename order invoice are that way all my order stuff stays together and if i type it in the search bar order i can see all my related order stuff together i used to just call it invoice r but i've since changed my ways okay order invoice are open oh excuse that's an ah it should be a cue see i'm talking to you guys and i messed up already there we go it's kind of like when you're shooting baskets right you can hit it hit it hit it no problem and then as soon as you try to show off and explain to someone else hey look watch this you mess up i do the same thing with you guys i can do this in my sleep and then i'm talking explaining something to you and i make a stupid mistake like that okay so there's my order invoice queue right all the order information the order date some of this stuff duplicates all right you can see all the order information duplicates that's fine all right we'll deal with that at the at the invoice level but i've got all my line item stuff i got all my order stuff i've got all my customer stuff together in one spot okay now we're ready to make the report so let's go to create and then report design blank report i don't have any blank templates for the reports but that's okay i hardly make reports i don't make a lot of them first things first let's bind to the report open up the properties for the report go to record source drop that down the order invoice queue that we just made all right i'm going to save this before i forget about it order invoice r that's my report right down there in the bottom first one now the first thing that i'm going to do is i'm going to turn on my report header and footer i want a bunch of information to be at the top of the report and at the bottom of the report and that information is going to be stuff related to the order as a whole the details section is going to include the line items okay so right click anywhere in here and turn on the report header and footer it comes in gray i hate that change the color format that color right here just make it white standard color is white okay same thing here with the detail section it's got that thumb alternating background color i can't stand it get rid of that copy paste yeah i should probably add a blank template to my templates section in fact i'm going to do that right now i'm going to use this guy because i'm going to update my template with this stuff that i'm adding today all right save this and i am going to let's copy and paste this guy copy paste blank r that's got my blank settings in it okay perfectly all right so the report header is going to get all the stuff that goes at the top of the invoice let's open up the field list what goes up there well the order id the customer id the order date whether it's paid description um i'll put the notes up there too then we'll put the customer information up there so where's the customer stuff here we go first name last name address city state zip country all right all of this stuff is about the detail the line item stuff right the product the quantity the unit price this is notes for that line item see it's order detail cue notes versus order t notes all right once you've got all these guys selected grab any one of them drop them up here in the report header boom okay the rest of that stuff's going in the detail section down here don't worry about the page header and footer just yet in fact i'm going to shrink that up to nothing we'll use them in a bit all right this through that that's the detail stuff that goes down there okay that's how we're handling our grouping you could do grouping levels too you if you want to if you want to print out multiple invoices in a batch you'd set up a sorting and grouping level we're just doing one at a time so this is actually easier all right i cover sorting and grouping in a different lesson i'll put a link down below to the sorting and grouping video i warned you i'd refer to a lot lots of other videos in this video okay so order id can go there let's put the customer information all over on the right over here so there's the customer information there's the order date i'm going to select everything real quick and go right click size to grid it's one of my pet peeves okay is paid can go i don't know there that's fine uh description all right notes and you can make it so that these fields can grow and shrink by the way by default i believe can grow is on let's find can't grow yeah can grow is right here oh can grow and can shrink around it won't shrink though if it's got a label next to it and you also have to make the section shrink i got lessons on that too that's a whole different topic but this will get bigger if it needs to vertically all right that's the default behavior in any report actually let's move the order notes down to the order uh the report footer down here in the report footer let's put that down here that way the notes are all at the bottom okay now the customer make these labels all black by the way i don't like how they come in all gray like that black black i don't know who was thinking at microsoft that these would look good gray that's going to go away actually okay alright so this is the customer customer id came over here right justify that let's bold that all right order id and again we had that conversation about hiding this information if you don't want the customer to see it that's up to you let's left align this stuff okay customer id is over here right aligned and the labels we're just going to delete these labels and we're going to format this as like an address block so we're going to do first name last name address let's make this a little bit bigger all right city or whatever address fields you have for your location state and the zip and if you want to print this out and you can format this so it fits in a like a window envelope i had mine set so that i could print it out and fold it and through and like you know tri-fold it and it would fit perfectly inside of a window envelope that's just trial and error folks there's no you just got to line it up where you want it if usually it's over here on the left-hand side put that over here on the left we could do that let's do that usually window envelopes are over on the left so let's hit the customer information put it over here and we'll put the order information all around the right like that and we'll put this pay down here and then the customer information will be over here like this and it's just a matter of lining that up in the window one other thing i like to do up here let me save this before i go too far save let's slide all this stuff down like that and i'm going to take a label just copy and paste a label put it up top here like this put the word invoice in the label all right bold it and let's go really really big 28 point not even bigger than that let's make it all caps too and let's make it some font that looks demanding like times yeah there we go 48 point that's that looks like an invoice all right all right let's save it let's take a peek at what we got so far here right click print preview don't open it up normally just go to print preview always okay that looks good let's get in there let's turn all the boxes off it's another thing i hate is all the boxes that come in by default right click design view i'm going to select everything go to format shape outline transparent it turns all those dumb looking boxes off right click print preview much better okay let's see what the top is looking like we're only done with the top so far customer one richard ross you can use a concatenated field by the way if you want to put that together richard space rost that's up to you i already showed you how to do it this paid looks kind of silly hanging out over there by itself design view let's put this up here next to invoice yeah in my full classes i show how to put the word paid or not paid in red up here that's again in my full classes i cover lots and lots of stuff this is the quick overview all right and there we go okay now let's talk about the detail items first things first let's make room for the headers right here in the actual let's put the headers in the page header all right the page header so open that page header up a little bit now the page header will repeat on top of each page the the first page header comes in under the report header which is kind of silly but that's how they do it then on page 2 and beyond you don't get the report header but you will get the page header on top of each page so if you're making really really big invoices with multiple pages then you got to repeat this stuff okay so let's just cut these out cut and paste them up here paste all right so we've got the product i'll just put product because it could be product service whatever quantity all right unit price extended price and then the notes we don't need that label okay under these guys actually let's bold the let's build these guys here format bold let's put a horizontal line underneath those so let's go to design and find the line tool and draw a line right there looks good all right bring this up now just line these guys up underneath all right product quantity unit price actually let's we're going to write justify stuff in a second too get these lined up how you like them like that maybe quantity okay all right let's slide these over here like that and like that there's no yeah there's there's the little wizard type things that make reports that not nothing is going to be as good as you taking the time to lay these out by hand trust me but doing this for years nothing that they have ever come out with is takes the place of just taking your time and just doing this and making everything look nice all right now these notes we're going to slide up underneath the product name like that all right let's do that and leave a tiny bit of room there well you want to space your reports out a little bit more in your forms okay these order notes down here on the bottom put those over here like this maybe slide that across the whole bottom there that will grow and get bigger if it needs to so will this all right slide that up let's save it let's take a look at what we got right click print preview okay it's looking pretty good all right looking pretty good now there's a lot of extra space here and that's because that order notes field is there but there's nothing in it for each line item all right that's where that can shrink comes into play all right open up this guy's properties this can grow and can shrink now the detail section itself has a can grow can shrink but the can shrink is set to no so the detail section will not get any smaller than you have it set there alright change that to yes all right close it down and then open it back up again print preview and look at that notice all the extra space is gone now we didn't have anything in those fields let's put some stuff in there and order f let's say up here um this is an order note okay and for training let's say one hour of excel half hour of access training okay and then let's say the cupcake was delicious and chocolaty chocolate tea is that a word okay now if i close that come back here to order invoice right click print preview you can see there they are okay i want to move them in a little bit maybe indent them just a hair design view let's see let's do this let's uh let's slide these over just a bit like that maybe make them a little bit smaller let's go with maybe nine point all right save it close it let's see what this looks like yeah it's a little better let's take care of our page margins and and make sure that our invoice is using all this space over here that we're not using so let's set the margins to narrow which gives us a half an inch total right the left and right is both a quarter so it's a half an inch so now if i go to design view that means i can come in here let me turn off the property sheet and i can come right out now this is an eight and a half by 11 sheet of paper i don't care about the top and bottom but right there is eight and a half that means i can come in just shy of eight don't go over that eight because if you go over it or sometimes even just on it you'll get that warning message that the page is too wide so i'm gonna go just shy of that eight like one one little dot shy of y shy of eight so i can drag this stuff over to here now like that i can bring over this stuff let's actually just bring over all of these guys and i just moved it a little bit not planning to okay let's fix that that looks good unit price extended price right there all right bring that line over like that get that lined up oh i missed it click and drag there we go this stuff can come over more now like that maybe bring that just a touch back since i made it smaller and of course the order notes can take up the whole bottom save it let's take a peek at what we got right click print preview oh look at that starting to look really good right uh-huh need a total down here right put a total down the bottom underneath all that stuff right click design view okay where is that going to go it's going to go in the report footer maybe slide these notes over like this and we'll put the extended price down here so let's put another horizontal line i just put a line right across the whole bottom copy paste slide that in like there okay copy the extended price copy paste it in the report footer slide it over here bold it you can make it bigger if you want to i like to make these order totals huge not super huge but big enough so the user is like oh okay that's what i owe okay now i've got to change this just slightly just like before okay equals sum of extended price just like that sum ext price save it close it preview it and there we go say that and yeah with a full featured invoice you'd have more stuff down here like uh the subtotal the tax shipping and handling whatever else but this is just a simple invoice again as you can see this is the streamlined version right we've only been doing this less than an hour and we've already got we've gone from uh an order entry system order details we've got the queries built we got the invoice pretty much done all right maybe put something on the page footer down there design view what do you want the page footer now this is weird the page footer displays in here above the report footer but the report footer goes right after the detail section the last detail section the page footer is always on the very bottom of the page so here's one of my favorite things i'll put a page numbering thing on the bottom here delete the label take this guy put it over on the left like that and we'll do uh open this up let's go to format um make sure it's black double click on it we'll call this uh pg num like that and the p the control source is equals quote page space quote and the word page and quote space of and pages this is a special thing you can do to put the page one of three on the bottom down here there i'll zoom in so you can get a look at that page inside of quotes page is it page and pages are special reserved words they'll put the the page number and the total number of pages and then that will go right there on the bottom of your page let's take a peek at it real quick close that up print preview and there it is i get this stupid box again oh i hate that box i can't stand it click format shape outline transparent okay let's put something over here on the right on the bottom of the page like the subject to our terms and conditions or something like that or you know we accept make checks payable to that kind of stuff so let's just take a label drop it down here on the bottom in the page footer right over here all right like that right align it right make checks payable to abc corp right one two three main street etc okay put that in the bottom save it and let's take a peek there you go okay whatever information you want the bottom of the page goes right down there next we have to limit this so we only see the information for the order that's up on the screen because right now i'm seeing all of the detail items and it's looking like it's part of order one but it's not all right this is all the items from both orders all right but we only have the order section showing up once in the header and then all the detail items display so we have to limit this a little bit how are we going to do that well let's put a couple buttons in so that we can see our orders for each customer from the customer form all right so if you want to see the orders go to the customer form just like we have a contacts button here we'll make one to open up their orders then from the order form we can open it up to see each invoice alright so right click design view let's drop a button down here so it's going to be command button drop you right there form operations open a form next we're going to find order f next find specific data to display again this is another lesson i have the open a specific record all right open a form and find specific data go now we have to link together what fields are the same on both forms customer id there customer id on the order form link them together right there see that and again i got a whole lesson on this all right it's the open forum specific record lesson i'll put a link down in the link section down below next text right orders next what name do you want order button and finish and here's a button now to open up the orders for this customer save it close it now go to the customer form open up orders and now you'll only see my orders right filtered okay if i go to a different customer if i go to um let's say here's the customer list if i go to james kirk and i open up his orders there's his order all right maybe move orders over here like that save it all right so there's jim kirks now i need a button on here to print this invoice okay right click design view drop a button up here all right report operation preview report next now there isn't an option in here to only print a specific invoice there's no option in the wizard to open a report and find a specific record to display so there's two things we can do one involves some programming basically one line of code but i try to avoid programming in the beginner tech help videos i do have a separate video on it it's called open a report to a specific record i'll put a link down in the links section below but for this lesson we're going to use the second technique and that is we're going to filter a query to only show the records for the current order so for now we'll open up order invoice r next right print invoice like that actually i'm just going to put in here invoice because print invoice kind of tells me i'm sending it right to the printer i don't like doing that i like to bring it up and print preview mode and then give them the option to print it from there invoice button and then finish okay here's my invoice button put that up top like that okay now when i click on that button let's see what happens here's starship parts invoice again i'm getting them all okay so i have to limit my query somehow all right how are we going to do that we'll go back to the invoice queue that we built the order invoice queue right click design view all right in here i need to say order id which i don't have separate in here so they bring it in separate order id bring it down all right i avoid this initially so we don't have a duplicated value bring it to the front of the list here i'm going to turn off that show box so we don't see a duplicate value but the criteria again if you've never done query criteria i've got lessons on that too i'll put something down in the link section for query criteria all right this has to be forms now if you get a little helper up there right it's going to be forms order f order id and you can pick from those lists or just type it in all right this is forms form name notation and again i got videos on that too i'll put that down in the links section i told you i was gonna be referring to a lot of other videos but they're all free go watch them go learn some stuff right that's what they're there for now what this will do this will say only show the invoices where the order id is equal to the forms order f order id the current open order so now your invoices are only going to work from that form you can't just open the invoice but that's what we want we want to only show that one invoice all right so close that save it yes now starship parts give me that invoice boom there it is there's the starship parts okay if i go to somebody else if i go back to me all right go to my orders there's an order and you can have multiple orders of course and you want to add new order there you go all right print this one there you go there's my invoice okay that's it that's really all you need now something i normally don't do is make these tech help databases free downloads for everyone with this one i'm going to just like my blank database template i'm going to be updating my blank template to now include this order stuff as well so this what you see right now right in front of you the database we just built in this video is going to be a free download from my website find a link down below in the links section you can go there and grab it and use that just as you want and continue to modify it the way that you want to modify it that's the beauty of using accesses so you can change the database yourself so as you can see we were able to put together a pretty good order entry system in just about an hour all right that's not really that hard to do as long as you know what you're doing and that's what these videos are for so we are going to call this guy blank customer database version 1.4 now what i usually like to do with each version is change the colors up just slightly so let's go with let's go to blue yeah we'll go dark blue on the menu too there we go looks good save it close it open it back up perfect there's version 1.4 and why not let's call this the instead of the blank date it's not blank anymore is it let's call this the access learning zone customer database there we go oh look at that i'm so proud okay want to learn more in the extended cut for members we add an order list so you can see a list of all of your orders the order date the order total whether it's paid or not we will refresh the order total and all the calculations each time the quantity or the price is updated a lot of people are used to excel and they get confused if they type in a quantity and they hit tab and it doesn't update everything so we'll do that we'll copy the address info from the customer table to the order so each time a customer places an order we're storing where their addresses were at the time because if they move like i mentioned in the video you don't want to have their new address as to where this order was shipped and one of the most requested features is the product select box right down here all right a combo box that'll have all your products in it you pick one you hit add it adds it up to the invoice right here we go for the members there's the order list you can open up the customer from here go to their orders from there or you can open up the order directly from here right much easier way to find your orders and of course this is sorted by date so the most recent ones will be up top i added it so you can jump right to the customer's record by double clicking here anywhere you see a blue field you can double click you can update a quantity now and hit tab and it refreshes everything or the price right a lot of users are used to excel they get confused because it doesn't update and access until you move to the different record right access is record based so we just changed that we just added an event in here so if i put in here nine now it updates the price okay we copy the customer's address up here now i just did one address but if you want to do bill 2 and ship 2 that's fine just do the same thing twice but this way if jim kirk places an order for starship parts on january 14th and it's sent to that address and then he moves next month and then six months from now he wants to know where was that order shipped you've got the history in the database so that'll happen when you create a new order or if you change the customer for an order if you drop this down and say i want to change this to jean-luc picard and it'll ask you are you sure you want to do this hit yes and it updates the address information for you but we'll put that back to jim kirk okay yes there we go i don't think picard ever had to buy triple bait then we'll do one of the most requested features right down here a product combo box will add a product table real simple one and then you can pick what you want like a phaser bank hit add boom and it puts it right up here leaves you sitting on quantity so you can update it and then tab to the next one there you go that's the extended cut for members that's almost an hour long of additional material remember silver members and up get access to all my extended cut videos and we are quickly approaching 100 tech help videos so there's tons of material 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