Create a Database in Microsoft Access for Beginners

welcome to another free microsoft access video tutorial brought to you by access learning zone comm my name is Richard Ross in this tutorial I am going to teach you all the basics of building a Microsoft Access database we'll start a database from scratch build a customer table a query a form and a quick report if you've never worked with Microsoft Access before this is the perfect place to start now I am working with Microsoft Access 2013 but these steps are pretty much the same regardless of which version of Access you're working with I like Access 2013 and you can download a free trial version from Microsoft's website now there are a ton of different templates that you can use that access comes pre-configured with but I want to teach you how to build your own database so you're not limited by someone else's template that's what I focus on in my full class is building your own custom database now in Access 2013 you can build custom web apps which work online or traditional databases that work on your computer or on your local network we're going to stick with blank desktop database for now give your database a file name like Rick's database or whatever you want to call it and click create access creates your database which is a single file sitting in your Documents folder that's going to contain all the different objects that your database is going to hold the first object we're going to start out with is called a table tables store all the data in your database you can put all the information in one or more tables for this simple tutorial we're just going to stick with one table now tables are organized into different fields you can think of fields like columns in a spreadsheet each field has a specific type of data like first-name lastname address and so on access starts you off with one field they're called an ID each record in your database each row gets its own ID it's called an auto number and it's going to start at one and it's going to count up now we're going to add a couple more fields to our table so I'm going to click right here where it says click to add and pick the type of data you want to store in this field short text is less than 255 characters that's perfect for things like first name last name address and so on there are also values for numbers currencies date/time yes/no and lats more for the first field let's pick short text now it says field 1 we're going to type a name for this field in here I'm going to type in first-name without any spaces it's a personal preference of mine I don't like to put spaces in my field names now press ENTER and that creates that field called first name now let's add another field how about short text and let's call it last name again no spaces you can add pretty much as many different fields as you want I'll just add a couple more for class I'll drop this down let's go with number and I'll call this field num children I want to track the number of children each of my customers has maybe I have a kid's clothing store and you can abbreviate num children or whatever you want now let's go with currency and let's call this credit limit and one more how about short text and let's go with state the customers state I'm not going to bother putting full address in there now that we have our table defined we can start putting information into it some data into it for example I'll come back over here click on the first row under first name I'll type in my own name Rick tab Rost tab to children tab let's say I have a $5,000 credit limit tab and state is New York and that's it move to the next by just simply hitting tab and it brings you right down to the next row if you're familiar with Microsoft Excel you'll find that the data entry works almost the same and you can resize these columns like so to make them wider or more narrow I'll put another customer in Joe Smith one child $1,000 credit limit from pencil Alania and how about sue Jones no kids $10,000 credit limit also from New York so now I have three customers in my customer table now the data is saved in the table whenever you move from record to record so as soon as I finish entering in record one it automatically saves in my table but if you make design changes adding fields or changing things like that you want to make sure you save your changes by clicking on the Save button or pressing control s on your keyboard now since we haven't given our table a name yet Access wants us to name it I'm going to call it my customer T I like to end all of my tables in the letter T and there we go I have my customer T finished you can see over on the left hand side it shows up in my object list this is called the navigation pane or you can navigate between the different objects in your database if you decide to add more tables later on you'll see them all listed here under tables now tables are great but you don't necessarily always want to see all of this information for example let's say you only want to see customers that are from New York well one way you could do that is to filter this list of results by coming down here and turning off everything but New York now I only see the customers from New York and that's fine for simple things but later on down the line you might want to say ok I want to see all the customers that have 4 or more children with credit limits less than $2,000 who are from California and that's a lot of steps to go through every time you want to see that that's where queries come in a query is essentially a set of saved parameters to define what you want to see out of your tables now I'm going to turn this filter off by clicking on the select all that will show me all my customers again and let's make a query now that does the same thing that shows me just the customers from New York so I'm going to close this table save design changes yes if you've made any now let's create a query to go along with our table so come up top and go to create and then query design a blank query comes up the show table window appears you'll see all the tables in your database we only have one you have to add the table or tables that you want to your query so I'll pick customer tea click Add you'll see it appears in the background here then I'll click close next you select the fields that you want to appear in the query when it runs let's say I want to see the first name I'll click on it and then drag it down here and drop it in the first column then last name click and drag and let's say for this query I just want to see first name last name in the state so I'll bring state down you can double click on it too it's a shortcut and if I run this query now here's what I get for results come up here on the ribbon and click on run and there's the results I see the three customers that are in my database first name last name and state that's what I defined from my query so far now let's go back to design view click on that guy let's put a criteria on now see this row down here it says criteria well find criteria and comes straight across underneath state I'm going to limit my list of customers to only the customers from New York so inside of quotes put in New York that's double quotes that says show me all the customers first name last name state limit them to show me only the customers from New York now when I run my query you can see I've got the same results but I only have two customers now the customer from Pennsylvania does not show up so that's the essence of building a query and you'll have multiple queries in your database again generally they'll be more complex than this you might say show me all the customers from this stage with this credit limit and so on you don't want to have to go through and rebuild that every time you want to see that set of data that's why queries are so powerful now I'm going to save this control s again save let's call this customers from New York Q I like to end all of my queries in a queue and there's my query and now if I close the query I open up the customer table and see all the records or I can quickly rerun that query by just double-clicking on it and there's those results again so that's what queries are used for now working with data on the screen is okay but this format is not necessarily very pretty that's where forms come in forms lets you work with the data on the screen in a nice pretty interface now I could spend hours going over good form design and my full length tutorials I do there's lots of great tips and tricks and techniques I could show you for building really cool-looking forms but for now let's make a real simple one create click on your table the customer T then click on form access will put together a form for you that looks like this it's much nicer and cleaner for working with the data on the screen and here you see one customer at a time you can click on these objects here and resize them if you don't want the first name being that tall or the last name just like that you can move through the different records in the table by clicking on these navigation buttons down here on the bottom this is an example from my full access tutorial of the database that we build in class you can take forms and make menus out of them here we have a customer list form that shows all the customers we can click on a selected customer open up a full customer form like that and as you can see that's nice and pretty very easy to work with over here we have something embedded inside that form called a subform which is a list of contacts for that customer so as you can see forms make working with the database on the screen much much easier than just simply looking at rows and columns of data so that's what a simple customer form looks like now I'm going to save this as my customer F so ctrl s to save and then customer F for customer form now this little title right here can be whatever you want it to be that's just simple English double click on it and then just change this to customers or customer list or whatever you want to call it and that you can get rid of it completely by hitting delete and your keyboard which I often do because I don't like those the title and that little graphic that goes along with it so that's your customer form so now we have a table a query and a form and finally when you want to print information out of your database you use a report reports are generated for printing information now one of the coolest things you can do is you can create mailing labels which are basically a report right from your customer data so click on create click on the customer table come over here to the right there's all kinds of different reports you can generate let's do simple labels for now click on the labels pick the type of label that you want you can pick the manufacturer you can pick the product number if you buy a box of labels this number is usually printed on them I'll pick next specify the font the font size the color how do you want one label to look I'll hit next now you build a prototype label what do you want a single label to look like and then access copies it for all the rest of the data in your database so for example I would go first name then a space I'll press the spacebar on my keyboard then last name then enter now normally here's where I put the address the city in the state but we don't have all that all we have is the state then I'll press ENTER and let's say the credit limit at the bottom maybe you're printing these out to go on nametags but that's how you create your prototype label then hit next which fields you like to sort by maybe last name next what name would you like to give the report let's call this my customer labels are and then finish and access generates your report for you and there you go there's your mailing labels you can click the zoom in when you're ready you can print them by using the print button over here you can change the size the margins all kinds of different settings in here you can export this to excel to a text file to a PDF send it via email and lapse form when you're done click the close print preview and it brings you back to your database this is the report design view which we're not going to mess with right now so I'm just going to close that if you want to tweak or modify this at all you can make changes to it and here in design view so there are the four basic objects in your Access database your tables to store the data queries to display the data however you want as far as limiting the records or sorting it forms to work with data on the screen and make a nice pretty interface and then reports to print information out there are also other more advanced objects called macros and modules that I cover in my advanced classes now the important thing to note is all of this information is stored in one physical file on your hard drive mine is stored in C users Ric documents Ric's database accdb it's an Access database file I hope you enjoyed this short tutorial I literally just scratch the surface of Microsoft Access there's tons more that you can do to build a professional quality database in not a lot of time now the next step for you is to take my Access beginner 1 tutorial you can find it on my website at access learning zone.com and for everyone watching this video my full access beginner 1 tutorial is absolutely free of charge look in the description text below the video window and you'll find a link that you can click on that will take you to a free copy of my access beginner 1 lessons it's over two and a half hours long and it goes through everything that I covered now in a lot more detail plus a whole lot more on how to build databases in Access once again it's completely free of charge so visit my website right now and continue learning with access learning zone com

Channel: Computer Learning Zone

Views: 1,053,676

Rating: 4.8516951 out of 5

Keywords: create access 2013 database, create access application, create an access database, create database in access, create database in access 2013, create database with access, create ms access application, create ms access database, how to create access database, microsoft access create, ms access create, #microsoftaccess, #msaccess

Id: eQMc30gSPN8

Length: 15min 23sec (923 seconds)

Published: Tue Apr 09 2013