Build a Microsoft Access App for tracking sales orders

org what we're going to create in this series is a simple version of a cash register application or a point of sale we're going to use microsoft access to build something like you would see on the computer at walmart at your self checkout in microsoft access there are four parts to a program there are tables forms queries and reports and we'll show each of these in eight simple lessons there's going to be eight parts to this application so this tutorial will take you probably an hour or two of full-time work on the computer we're going to create tables and so these tables will be for all of the information in our computer then we're going to create a few forms forms where you enter data and then we're going to create a query so we can have sorted data and finally a report where we can print it out so here's what the program will look like when we're done we're going to have a customer form where we can enter in the information for our customers we can scroll through the customers to see who they are we're going to have a products form where we can enter in different types of things that we sell in our company we'll have a vendors form so you can see all the different companies that sell us items are wholesalers the heart and soul of the program is called the invoice table or the form for the invoices it will let us choose to purchase items pick one of our customers and then we'll decide how many of each thing they're going to buy and it will give us a subtotal at the bottom of the page so that gives you kind of a preview of what we're going to see on our program so let's get started in this first video we're going to create the customer table i'm going to choose all programs and go look for microsoft office and choose access 2013. the first thing i'm going to do is start a blank database so i'll click here on the database icon for blank access forces you to save your file before you begin anything else and so we need to give it a file name and location i'm going to call mine sluter store you can name your something similar choose the folder pick a location and click ok click the create button the most basic building block for any access database is the table and you notice here that we have something called table1 already created and it's asking for us to add some information access assumes that we're just going to start typing in some data i'm going to actually change this by right clicking on the title table 1 and choosing design view table 1 needs a name i'm going to call this customer table and so this is basically a spreadsheet that's going to hold all of the names of our customers the first field name of a table usually is something called id in this case i'm going to call it customer id and notice it is an auto number so each number will be assigned automatically to our customers as we enter them let's click on the next line down since we're collecting data about our customers we're going to collect their name and their address and some things about their business relationship with us so i'll start by naming something called last name notice how you don't use any spaces in here a short text is a good type for a name it's only 255 letters long as you can see down here which is enough for most names so let's put in our first name also we'll choose short text let's do street address once more we can use short text what other kind of information do you store in your customer table things like their city and their state so city and state these are all short texts let's go to zip code for zip code you might be tempted to choose number but we're going to leave it as text in case they live in a foreign country or else we have leading zeros in our zip code let's explore some of the other types though that are here there is a type called time and date let's choose this one let's call it customer since and so customersense is going to be the day when they became our customer another format that we see is a yes no format we could call this free shipping customer or free shipping status and so if we check that box it means they are going to get free shipping from us i'm going to right click now on the customer tab up at the top push save right click again and i'm going to choose datasheet view now you'll see that this looks something like a spreadsheet now i can start entering information in here so the last name i just type in smith and i type in any type of name let's go with the address one two three main street and the city let's say phoenix and arizona and the zip code is eight five zero eight seven and what is this one customer sense now when we want to enter a date here there is a date picker icon here if you click this you can choose a date when this became a customer and so we have 215. let's scroll a little bit further to the right we have the option to either check for free shipping or no this is the free shipping status so let's give this person free shipping now you can see why this is called the data table view because we're creating a table of data now i finished typing in about 15 names so i have their name and their address their city state zip when they became a customer and some of these have chosen to be part of the free shipping club and some have left alone now you can sort your database here if you click on the words last name you can tell it to sort from a to z or you can sort it in reverse order so you can sort any of these by their city by their state by their alphabetized or when they were joined to the company and so sorting is one thing that you can do in a table let's save the table so right click on the tab and choose save let's right click and close it if we were to go and double click the table we can open it again we can enter more data if we want we can change data we can delete data for instance if we want to delete this person here i could right click on the front of the row and there is an item for delete record it says you won't be able to undo this information so are you sure click yes now if i sort by the id number you will notice that it skipped it one three four five and so id numbers are never replaced they are always one bigger than the last so they will never be another id for person let's close this and save the changes one other way to enter a data is to create a form i'm going to click on the customer table up to the create menu and click here where it says form this will create a form automatically that includes all of the data that i've entered already let's save this form and we'll call it customer form so i'm going to rename it click ok down at the bottom it shows that i'm viewing the records in sequence from number one through 14. so as i click these arrows the forward and the back arrow i browse before and after through my customers when i reach the end i come to customer 14 and now when i try to create a new one the 15th customer is asking me to enter new data so i can enter data here i have to go to the right click on the tab and choose form view then i'm allowed to create a new customer so once i've entered all the information i click another next record and a new one comes up now if i go and close this form and now i reopen the table by double clicking you will see that i have the new person in the list so in most programs you want to have users enter data in through forms and so that's what forms are created for you can also modify how your form is designed if you right click and choose layout view it allows you to adjust the width of each column you could also right click and choose design view inside design view you can change the format you can change the size of things but you can also adjust how things are formatted so for instance down here in this field called customer since i can come over to the right side here and choose a different format for my words i can choose things like medium date or long dates let's try what medium date looks like and then when i do form view you see the medium date now shows the abbreviation for the month inside here you can also adjust things like the the text alignment so instead of being left aligned you can center it you can change the font size if you want to have larger letters you can change the colors you can change the font name so all of the different things that you're accustomed to seeing on most word processors you can change in these fields lastly i'm going to close this save the changes and there you have the first lesson on creating customer data in your database in this video we're going to create a second table we've created a table for our customers next we're going to create a table for our vendors so let's go to the create menu click on the button called table and let's do a right click on the tab table 1 and save the table this time we're going to call this vendors table so these are going to be all the people that sell things to our business so that we can resell them to our customers inside our table tab we're going to change to design view and now let's add a few fields for our vendors first of all let's rename this id as vendor id and now our vendors are going to have a few information fields all of these will use a short text after you've entered your field names let's save our table next let's enter into the data sheet view and we can enter some information about who these people are who sells to us so that we can sell to others so you can see that i've typed in six different vendors i'm going to be a company that actually sells video games and computer equipment so i'm using apple and amazon as some of my people that are making things so that i can resell them to my customers let's right click on the tab that says the title and save let's close this and now i'm going to create a form so let's go back to the create button since customers is selected i want to select the other table vendors then click on the form button and you will see it creates the vendors table this actually is something i want to create is called the vendors form so let's save it and instead of vendors table let's call it vendors form remember that when you do form view you can enter in data you can browse through each of the records and so datasheet view and form view are the same way to see your data but with different front ends so this will do it for our second video here on creating our application to run our business welcome to a third video on creating our access database for our business remember our business is going to keep track of how many things we sell and who we sell them to in this video we're going to create a table that will show how many products we have in our inventory so let's go to the create menu and we'll start by creating a table i'd like to first of all right click on the tab and choose save let's call this thing our products table so our products are the things that we sell and so in our products table we're going to keep track of every item in our warehouse or in our catalog you might say let's switch to design view and let's name a few of these fields first of all this is called our product id and we'll leave it as autonumber the next thing we need is a name so let's call this product name so this might be some name like our car or phone or some computer that we sell who sells this who makes it and that's the third part we're going to come up with here it's going to call our vendor id we're going to leave that as short text for right now but we're going to come back to that and change it what else do you have in a product well we have a wholesale price the wholesale price is what they charge us when we buy it from our suppliers and so the wholesale price is always less than the retail price the retail price is what we sell it for and so this is what our customers have to pay when they come into our store now these last two items the wholesale price and the retail price should be renamed instead of the data type of short text let's change these to currency so we're going to keep track of dollars and cents in these fields so it makes sense that we name the field here so it's going to be a number but let's go to the vendor id this here is going to tell us which vendor is selling us this product so how do i make sure that this vendor id matches one of the vendors in our vendor table here's what we do instead of adding this field as short text we're going to change this to something called a lookup wizard a lookup means we're going to look it up in another table so i choose this and it says the wizard creates a lookup field which displays values that you can choose from how do you want your lookup field to get its values i want the lookup field to get values from another table so in our case we're going to take the vendor id from the vendors table remember we're in the products table right now but we're going to borrow a number from the vendors table let's go to the next button which table would you like to take your values from we're going to go to the vendors table and look up there so i'm going to choose next again when we do our lookup we have to choose vendor id because the vendor id in this table is going to match a vendor id in our vendors table so i'm going to add that now it's difficult to remember the id number for each vendor so i'm also going to tell it to use the vendor name along with the id that'll help us be able to search and find things that we are looking for now when we are searching for a vendor id how do we want to sort them i'm going to sort them by the vendor name now it says when you do a lookup when you choose a value from another table would you like to hide the key or like to show it they recommend hiding it i'm going to actually show the vendor id because it makes it more transparent to see how the program's designed let's go to the next button now it says when you select a row in the lookup field you can store a value in that field in your database notice that my vendor id is the field that we're talking about here this is the one that we're doing the lookup on and so i want this vendor id to match the vendor id in the vendors table so i want to make sure that this one is selected now what would the value of your label be i'm going to choose vendor id here so the label will stay the same i am not changing that now we can also tell this thing to enable this called data integrity what this means is if there is a vendor id in our products table it has to match with a vendor in the vendors table so there will be no orphan products like a product that we have in our warehouse that has no manufacturer so this means data integrity says one table affects another table let's finish now now it says we're going to create a relationship between these tables the table must be saved before the relationship can be created do you want to save it now yes we do now it doesn't look like anything changed here we did this whole lookup process but let's go ahead and close this table and now we're going to choose database tools to see what went on behind the scenes click on this button called relationships and you'll see that there are two tables listed in the relationship notice our vendors table and our products table the relationship is between this field here and this field here in other words a product in our list will have a vendor id associated with it and so the vendor name for let's say apple computer will have some products in it such as an iphone or a mac or an ipad and so the vendor id is associated with a company notice also the number here this says one and this is the sign for infinity that means that we have a one to many relationship one vendor can match many different products you'll see in a minute when we start entering data how this works i'm going to close the relationships tell it yes to save them now let's go open the products table double click it we can start entering some data for instance let's talk about that iphone the product name we're going to sell an iphone in our store but it's going to ask what vendor sells your iphone and here is the relationship now notice this list of vendors this matches everything that we've typed in in an earlier video so i'm going to say apple computer that's our vendor id number one what's the wholesale price let's say we buy them for 400 and we sell them for 650 and so we have the first product let's do another one let's say we're selling windows version 10 and our vendor in this case is going to be microsoft we buy this for forty five dollars and we sell it for a hundred and twenty so let's go invent some other products that we have in our store so you can see that i've entered eleven different products you can enter more if you like every product has an id of a vendor and so you can see who is selling each item the wholesale price is what we buy it from the vendor and the retail price is what we expect to sell it to the customers for so now let's do a save and close on this now just like we did before we created a form for each of our tables so we can enter in through the form view let's do the same here with our products so let's click on products click on create and choose form now each form has its product and its description and so we can enter and edit data just like we did with the other tables let's save this and let's call this thing our products form i'm going to go back to the vendors form open this up our vendors form is very basic it just shows the address for each of our companies but since we added products to our computer we're going to recreate this form so i'm going to go back to the vendors table click on create and form and you notice that the new form that it created is different the new form looks a lot like the old form it shows the address of the company however what is different is that it's added another table below this is a sub form so this takes all of the data from our products table and associates it with each vendor so for instance apple computer sells iphones ipads and mac cares so let's go to the next vendor in our list so way down at the bottom where it says there are six vendors i'm going to choose the next record and it shows us that office depot sells two different things let's go to the next record microsoft sells a few things to us and you notice that the associated records are all listed down here with what products that this company sells so this is an improvement over our existing vendors form so let's call it vendors form two let's go look at the old vendors form it's not near as useful so i'm going to actually delete this one i'm going to close it and i'm going to go to the vendors form in the index here and delete it and say yes it's permanent so the vendors form too is the improved version we can keep that one and that will be the end of our video here for adding products to our application welcome to our fourth video on access and creating a application for keeping track of our business i'm going to go to google images now and search for something called an invoice form what does an invoice form look like well you've seen whenever you buy something from a company they give you a receipt and so your invoice or your invoice form would look like this we have the customer name up here and then we have a bunch of line items in here about things that we purchased we'll probably have a date and probably have an invoice number let's look at some of the other examples that we would see on the internet here's another one it looks similar the name of the customer we have a specific list of things that we're going to do let's see what this one looks like another invoice that says we have an invoice number the same idea who was a sales person what did we buy and this whole list here will be maybe 10 maybe 15 maybe three somewhat some number of items that are on the invoice and then we calculate the total so this is what we'd like to create here in the next step of our program so now let's go into our program that we've been working on and we're going to start by creating a table called the invoice table so i'm going to the create menu and choose table first thing you do when you choose design view is that you have to save it so i'm going to call this the invoice table inside the invoice table we're going to have a invoice id number so each transaction will have its own invoice we should probably have the word date of sale that's probably important to know when did we do this so let's change this format to date time it's also important to know that who our customer is for this particular invoice so let's say customer id and now we already have a list of customers in another table we have the customers table and so the id is going to correspond to this table so it'll be another relationship like before we're going to choose a lookup wizard so that we can choose which table we're going to get this information from so i'm going to leave this check that says i want the lookup field to get the values from another table which table am i going to get this data from well i'm looking for customer ids so it would make sense that they are in the customer table which fields am i interested in looking at well i'm going to need customer id but i'd also just like to use the last name and the first name so i don't need any of the rest of the stuff how would i sort these customers it would make sense to sort them by last name now in this case i'm going to leave this check mark checked that says hide the key column we saw that using the id number was okay to see how the computer actually looks up these numbers but for most purposes it's probably simpler to leave that key hidden let's leave this as the name shows let's make sure integrity is checked so that way you have an invoice number that matches each customer and click finish let's save it now for these three items in our list this will match pretty close to what we see on the top half of most of the invoice examples that you would find at a business shows the customer name shows the invoice number and it shows the date all the stuff that we have down here in the details of the invoice we're going to leave for another table so for now we're just going to use these three let's close this table let's go see what happened in our database tools choose relationships we'll see that nothing appears to have happened at first we have the vendors and the products let's go show some more tables let's show the customer table and let's show the invoice table now you'll see that there's a relationship between two more tables here the customer table and the invoice table so this is a one-to-many relationship which means that one customer can have many different invoices but each invoice is exactly related to only one customer now this is only half of what we need for an invoice we need also a table of all the details let's save the changes here now let's create a new table this one i'm going to create and i'm going to call this thing the invoice details on the invoice details we're going to have a table showing the details for each separate invoice so first of all this is going to be called invoice detail id so every table starts with an id number the first thing i care about is according to which invoice am i dealing with so i'm going to say invoice id is next we're going to look up that from another table so i'll choose lookup wizard i'm going to choose pick from another table this time i'm going to select from the invoice table i'm going to say this is the only field that i'm interested in click next it won't matter how i sort this since there's only one field and now it's going to show the invoice id let's also always check this and finish let's save it now other details after we have an invoice id associated with this we're going to have a list of things that we sold for instance we're going to have a product what product did we sell to our customer so we're going to get that product from the products table so we're going to have to do another lookup this time we're going to get the lookup from our products and we're going to use the product name as well as the id let's sort by product name and let's leave it as this let's enable integrity and finish so once i figured out what the product is i want to know about how many of each of these products we're going to purchase quantity is a new field and this will also be a number so the quantity we'll choose number we'll add some more fields when we create the form but for right now this will work so let's save this let's close our table let's go check out the database tools and relationships we'll have one more table to add to this now so let's go to our invoice details and let's just drag this on to the screen and you will see that we have a relationship now between all of the tables in our program and so this invoice details table is the most complicated it's also one of the most important parts of the application it pulls information from the invoice id number it also is going to show the products that are on each invoice let's close this and we'll save the changes i'm going to save the next part for the next video it's the most complicated part it's how to create the actual invoice form we're going to create something that looks like this we've already created the tables that are going to work behind it but now we're going to create the form so let's do that in the next video welcome to another video in the access database application for our business at this point we're going to create the actual form for our invoice remember in our last tutorial video we were creating the tables that were going to go behind this scene so in this in this form we're going to create the place where we put our customers names and then all the things that they order so here we are in our database program we're going to create a form but we're going to do it a little bit different than we've done the previous forms i'm going to select our invoice table to start with and choose create look for the button called form wizard form wizard is going to allow us to add fields from several different tables we're going to combine things from our invoice table and our invoice details and our products table let's start with the invoice table this form will show the invoice id it will show the date of the sale and it will also show the customer id next let's do another check we're going to select the invoice details table on our form we want to show the product id and we're also going to show the quantity that we're buying lastly let's go to the table called the products table we want to show the product name and we also want to see the retail price and all of these things come from various tables and they will all show up on one single form so this is the magic of access it lets you combine several tables into one report this next screen shows what our invoice form will look like at the heading we're going to include the invoice detail the date and the customer information in the sub form or the small detail area we're going to include all the things for each line item let's click next there's two different ways to show your sub form i prefer the data sheet view and then finally let's click the finish button we get to have a customer create a purchase item so i'm going to click brand new down here blank record this is like if somebody comes up to the cashier and says hey i'd like to buy a bunch of stuff first thing we're going to do is select maybe the customer name so let's see cherry pits is our first customer the date of sale let's pick a date today is the 25th now what is she going to buy well she's going to purchase a product so let's pick one from the list she's going to buy an ipad how many does she need well let's put in quantity let's put in three now we would expect to see that over here we would have well we have the retail price but the subtotal so this form isn't quite finished we need to add some more things to it so let's go and save this and then let's go into the design view we haven't seen the design view yet but let's take a look at what it can do we're going to move some things around so that way we can see them better so for our invoice details i'm going to click right on the little gray dot in the corner and move it over to the side here and let's stretch the width here so we get some more space and scroll down a bit more now right after retail price i want to do a subtotal so i'm going to have to move the form footer down a bit to create some space now i want to add a calculated field in here so i'm going to go up into this row of items here under form design tools choosing design and then i pick this here called the text box and i'm going to click right here so this is a blank text box it needs some information first of all let's give it a title so the label on this will be called the subtotal so we'll know how much this person is going to have to pay for this item now this is a calculated field all the other fields have a database field name in them and this one says unbound in other words there's no information to be put into there we're going to have to program this one over here on the right side there's a section called control source there are two ways to fill in the control source one is you simply select a field name it's not what we want we want to go to the last item where there are three dots and click here this is how we calculate a field we're going to create an expression to create a calculated control for example we can add two fields together and check to see if one is less than five something similar to that so i'm going to start with an equal sign and then from these expression categories in the list i'm going to select the quantity so i'm going to double click quantity and you notice it adds brackets and says quantity i'm going to press shift 8 to get the multiply sign i want to do quantity times the retail price so double click on retail price so the price times the quantity should give us the subtotal it shows us here what we're calculating last thing is let's go and check to see if it works so let's go to form view and let's scroll over the subtotal shows up here so we can see that the amount that we're buying 11 x boxes are worth 240 dollars each or 2640. let's format these numbers now so they show currency so let's go back into the design view click on this square and let's go to where it says format the format is going to be currency let's try that again let's save it open up this form and now we can see the subtotal shows the money now the column headers are a little bit too wide in some places and too narrow in others so we can adjust that as well let's go into the layout view we can make the columns narrower so let's see how that looks now so let's go to form view and now we can see a fairly decent layout so let's try again let's go down to the very bottom where it says records let's create a new record let's say we're going to purchase something tomorrow let's give it a customer name jim smith and now we're going to enter some things he wants a cpu fan he wants 10 of those the calculation is automatically updated so we'll have him by windows 10 just one copy that's 120 dollars so as we add new items we find that as he purchases them the subtotal is calculated okay so now we have subtotals it would be nice to have a single total at the bottom of the page so that's what we'll do next welcome to the next video in our access database for our store what we're going to create in this video is a subtotal at the bottom of the screen and so you can see down here at the bottom that i have a order with a bunch of items seven items actually and the total bill is five thousand four hundred and twenty eight dollars let's add one more item to see if this actually works i'm going to add a gamer's laptop i'm gonna pick one and you should see the total update down here as soon as i choose a new level we have seven thousand dollars and so this video is going to show you how to create this sum function at the bottom of the screen this form for in voices has actually two different forms in one if we open this up this is the main form it's called and then this grid here is called the subform so there's actually two different forms in our database list and we're going to start with a sub form so let's close here and i'm going to open up the sub form and choose design view notice on the subform that we're going to have the product id the quantity the product name and the right and the real retail price then our subtotal at the end of the line is what we've created is a custom formula we're going to multiply the quantity we ordered times the price now we're going to put in something here in the form footer if you can't see the form footer like i can you can grab this bar and move it up or down to give it some more space so we can work in here we're going to do a sum of all the subtotals so you need to go up to here to where i have we have form design tools click on design and then choose this here called the text box a text box simply allows you to place your own custom text box on the screen so first of all let's give this text box a name over here it says name is text 18 we're going to call this the total price how about now the label needs to have something else let's go with total price on the label notice the text inside this field is unbound so we need to put a formula in here or tell it where to get this information so let's go back over to here where it says control source when i click on this line i have two options i can pick a source from the database or i can click the three dots to create a custom formula here's my custom formula you'll remember from excel that you if you want to add up things you use the sum formula so i'm going to choose equals sum and now i'm going to choose two things to add together remember we have a field called quantity i'm going to multiply that by the retail price so i start typing ret and we should see retail price close the parentheses here and click ok now when i choose save and choose form view you will see that at the bottom of the invoice item we have the total here of eighteen thousand dollars now if we were to go to the datasheet view this is what we're going to see on our order and you notice that there's no subform total at the bottom so this type of view doesn't have the actual subform that we're looking for doesn't have the subtotal but if we go to form view we can see it now one more thing before we leave this let's go to the design view again and check on the name what did we call this field the field name is called total price so that will be important okay now the next step is to take this number and add it to the main form so it's a little tricky and so you'll have to follow the steps exactly here let's go open up our main form let's choose design view here and once again we're going to be working down in the form footer so you can adjust the bottom here so you can see a little bit of the form footer notice there's two footers there's this sub form on here we're not interested in that one we already have the the quantity here what we want to do is add a sub total at the bottom of the page so once more i'm going to go back to the text box control click it and we're going to add a control about here let's call this thing the order total so that'll be our label and then for the name of this box instead of text 15 we could also name this as order total okay so now the idea is we're going to go and put in a custom formula into this field right now it says unbound so there's no numbers in it let's go click on our form builder up here under control source let's click on the field builder tool and we'll start typing in what we expect to be put here all we're going to do is we're going to borrow the num the name from what we got on our sub forms so we've already got a form field on there we're just going to borrow that so i'm going to say equals and i'm going to take this from the subform name so if i start typing the name of my subform i'm going to get the word invoice i-n-v-o-i c and so you can see that the invoice details subform comes up as one of my choices so i'll double-click that so we're going to get something from this sub form now i'm going to type the period and then type the word form again an exclamation mark which means we're going to get something from a custom created formula and the field name if you remember it was called total price so i type in t-o-t-a-l price and this formula i'll zoom in so you can see it has to be typed in exactly as it's written here so this means basically take the total price field from the invoice subform click ok if this doesn't show up in your field here means it didn't save it for some reason access sometimes needs to have the information typed in twice let's save this and let's go look at our form view and so now you can see that for this particular order we have a order total at the bottom so let's go look at the next order that's seven hundred twenty dollars and this one is twelve thousand dollars the last thing we should do for this is to add dollar signs so let's go back into our design view let's click on this field and go to our format format option and instead of just general we're going to change it to currency and now when we go back into our form view we should see dollar signs here so that was a little complicated we had to create a sum of all the subtotals and we had to put this on the subform and then from the subform we took the number and put it on to our regular main form so it's a little bit awkward so if you follow these steps exactly though you should get a order total at the bottom of your page welcome to another video for our access database store in this video we're going to create a query and we're going to create a report let's see what they are a query is something that you can do to check through and sort data that's in a table for example if i go to create and choose query design i get an option to show all my tables and do some sorting and filtering on them let's go look at our invoice table so i'm going to choose add and also add our customer table with these two i could go through and find all of the sales that were done in the last month or in the last week so let's add some things down to here so for instance i'm going to double click on invoice id the date of the sale and the customer's last name just those three so i'm going to ignore everybody else down here though in the invoice table i have date of sale i want to do a formula so where it says criteria i'm going to say everything that is greater than and use a built in formula in access greater than now which is the exact time of now today's date and then i'm going to back up with minus seven so anything that is greater than seven days ago or to the present and press enter now when i go look at my query data sheet view i'm going to see all of the orders that i've placed in the last seven days notice there's two orders here there's order number two and three so there's an order missing from this thing where did it go let's go take a look at our invoice table by double clicking on it and you can see that the first invoice in my system was done back in february 5th the current data in my computer is march 1st and so you can see that this is more than 7 days ago and so in our query it doesn't show up remember our query is only looking for items that are greater than now minus seven so the last seven days let's save this query let's give it a name let's call it orders in the last seven days so if you're a business owner you might want to go find this last seven days of orders so let's close both of these you notice i have something in here called queries now orders in the last seven days let's make a report on that a report is something that you reprint so let's go back to create while i have the query selected i just click on the word report and this shows up as a report that you would run from your printer we're not going to do anything special to that we're just going to save it and let's call this thing the orders in the last devon seven days report so in databases you have four things you have tables you have queries you have forms and reports and now you've seen all four of them or at least an example of them and that'll get you started on your database the next video we're going to create a control form so it'll be like the front end to our application and then we'll be done welcome to the last video here on our access database for an application to run our store we're going to create a main menu and that we can use this as a user friendly program we're not going to force our users to open and find forms they're going to be easily available to us this is pretty simple we're going to create something called a navigation form so let's click here on navigation and we have different kinds of forms so you can pick any one of these i'm going to choose the vertical tabs left and so i'll have like an index over here and then the forms and reports will show up in this section here's how it works first of all we'd want to have someone else to run our program to be able to enter new customers so i'm going to drag the customer form up to here notice the customer form shows up i'm going to actually add the ability to add a product so i'm going to click here and drag that up so we have customers products we should be able to add and remove vendors so i'll add vendors also our users will probably want to enter invoices that'll be the most common thing that we do at our store so i'm going to take the invoice table and drag that over notice i'm going to leave the subform alone the invoice subform is actually part of the invoice table form so let's just leave that there also we might want to run a report so i'm just going to drag this report over to here and now we have the report and so that's really it so if we were to go look at this as a form view when our users run this program they would click on these tabs and then they would be able to access the different forms that we've created and so now you see the importance of creating forms and we don't give the user access to any of the tables we don't let them go see those okay now that we have our navigation form set up we'd like to set the options so that the users do not get access to any of these forms and tables on the side nor can they right click and choose design view and go change the program so if we release this program to our users we want it to be somewhat locked down so the first thing you should do is save a backup copy of your database the reason why is because if you lock the database down you can't make any changes so let's choose save as and let's give this thing a new name so if i have sluter store back or backup 2 or something like that that will allow me to have a second copy of this so i can get in and change it again later if i need to so let's choose save now it's going to ask you to enable content and ask if you trust this program yes you do okay so to change this so that we can't get into some of the menus let's go to the file menu and choose options some of the options on the current database we need to first of all give it a title so name your name your application scooter store and then when we have the option for display form the first thing we want our users to see when we open this is our navigation form so that way we can get around in the program we're going to hide some things so let's go down a ways the navigation pane the navigation pane is all the things over here on the left so we don't want them to get into the tables and edit them so we'll uncheck that allow full menus no we don't want that either because menus are when you right click on something you can choose design view so click ok it says now you must close the program and reopen it to take effect so let's choose close and let's go back and open it remember i'm opening the backup version of this so now you can see that my menu is the only thing visible if i want to hide the top ribbon bar i can click this little arrow and now i have a very clean looking application and so our products can be entered our vendors can be entered and we can create invoices and do some reports so we're done now this is a very simple version of the program to enter data into a database about sales at our store you can imagine that if you had a real accounting system there would be all kinds of things about inventory and about sales events and about discounts but we just got the basics running here so this is your first access application congratulations you can consider yourself a database programmer now and go out and get a job

Channel: shad sluiter

Views: 9,245

Rating: 4.9452057 out of 5

Keywords: microsoft access, ms access, how to use microsoft access, how to use access, office 365, ms access training for beginners, ms access tutorial, access for beginners, access 2016 tutorial, how to use microsoft access tutorial, access database, database design, microsoft access crash course, microsoft access for beginners

Id: aV1XLxEcxaQ

Length: 52min 41sec (3161 seconds)

Published: Tue Aug 11 2020