Microsoft Access 2013 Tutorial Level 1 Part 02 of 12 - Planning Your Database

welcome to Microsoft Access 2013 beginner level one brought to you by access learning zone comm right here on YouTube this video is lesson 2 of 12 plus an introduction if this is the first video you're watching in the series click on the link shown to start the course from the beginning otherwise we'll start lesson 2 now in lesson 2 we'll discuss planning at your database what tables do you need what field should go in each table what do you want your forms and reports to look like the first thing you should do when planning a new database is to sit down with paper or a whiteboard and to figure out exactly what you want your database to do plan this out in advance make a list of all the features that you want included in your database what kinds of data do you want the store in the database what do you want the different forms and reports to look like and what other features do you need do you want the store customer information name an address for example track correspondence with those customers so every time you talk to them that's stored in the database you want to generate mailing labels on a regular basis perhaps to a certain set of customers do you want to create quotes and invoices and print those out store order history keep track of everything your customers have purchased do you want to track employee timesheets clacking them in clocking them out calculating the amount of hours they've worked in a week do you want to maintain a product inventory no how many of each unit we have on the shelf and do you want basic accounting features accounts payable accounts receivable and so on these are all things you should write down before you even start building your database and access know what you want the database to do now once you've figured out what you want your database to do write down what kinds of tables you think you'll need remember all of the data in your database will be stored in one or more tables you want all of one kind of information in the same table for example all the information about your customers we'll go in the customers table all of the information about the orders placed will go in an orders table I'm going to track employees and products in their own tables employees and products are two completely different things so they'll each get their own table now customers and orders might be related to each other customers can place orders for example but they're not the same thing an order if you think of it as an entity is different from a customer customers can actually place multiple orders so you would not want to store order information in the customer table because you wouldn't know how many customer fields to have to track those orders now we'll talk a lot more about relating tables together in future classes but for now try to think of each discrete item or entity going in its own table now that you've got an index card for each table that's going in your database take each index card for example customers and write down a list of all of the fields or the types of data that you want the store in that table remember each specific bit of information is considered a field in your table if you were putting this into an Excel spreadsheet each field would be a column of data so for example in my customers table I've got basic information like first name last name you can put middle name and all that stuff in there too if you want that address city state postal code or zip code phone number fax number and then some other types of data is this customer on my mailing list how long have they been a customer the customer sends field what's their credit limit email address and finally some notes make sure you're as specific as possible when defining your fields it's easier to put two fields together later than it is to split one field apart for example if I just had a single field called name and I've got a value in there like Joe Smith it's much more difficult to try and figure out where the first name is and where the last name is so if you ever think you're going to need to generate a report later for example like a letter that says dear Joe you want to make sure you split that information up into multiple fields while you're building the table obviously you don't want to go crazy you want to break things up too much for example here we just have address city state and postal code now I have worked with companies that break that address field up into the number on the street then the street name then the street type is it a drive an Avenue a circle for example it's completely dependent upon your business's needs however keep in mind you want to make these fields as specific as possible without going overboard now also take note that I did not put any spaces in my field names I've got capital F first capital n name with no space between them access databases are easier to work with later on when we start getting into programming and macros if you don't have spaces in your field names you can if you want to but take it from me don't use spaces I'll explain in much more detail in future lessons now again you'll notice that I don't have any information in the customer table about the customers orders I've seen some people build databases where they put order information in the customer table order amount one order amount to order amount three and so on where does it end how many order amount fields do you need in here right now this customer is limited to only three orders and that's bad database design as a general rule of thumb if the customer has more than one of some type of thing put it in a different table here that thing is an order you could have one order he could have zero orders he can have 500 orders hopefully right so you want to put order information in a separate table the order date the sales rep the order total sales tax collect and whether or not the orders been delivered and of course this is just a sample list of fields you may need many more this way you can have an unlimited number of orders per customer if you put the fields for the orders inside the customer table you're limiting the number of orders each customer can have likewise to go the other way you don't want to have data regarding your customers in your order table there are some exceptions and we'll talk about those in future classes but generally the order table should have all order information now if this seems a little confusing don't worry about it we're actually not going to work with multiple tables until we get into the expert series so we've got a lot of learning to do first before we get to that point for now I just want you to recognize that different types of information in your database will go into their own tables for today's class we're going to focus solely on the customer table but later on we'll add some additional tables like contacts employees and eventually orders the next step after you figure out all the tables that you need is to get some paper or whiteboard and to draw out the way you want your forms to look on the screen it's easier to build forms if you have a roadmap a template that you can see exactly what you want your finished product to look like even if you don't know how to do something if you don't know how to build what your brain wants to build write it down draw it on paper and eventually you'll figure it out it's easier to build something if you have a blueprint now as you can see I'm no artist but I do sit down and sketch out what I want each one of my forms to look like when the database is finished here for example I want a main menu with a couple of different buttons on it I want to click on this button to open up the customer form the second button open up the order form and employee form and so on then I'll draw the customer form what I want that to look like I want the customers info on the top name address and so on a picture over on the right there untagged history below that so I can see the last couple of things we talked about and the dates that we talk to each other even if you have no idea how to build this stuff right now draw it on paper anyways remember also to keep in mind the skill level of the average user of your database if you're building the database for yourself you can make it as complex as you want if you're building a database to be used by people who don't have a lot of computer skills you may want to make your menus and your forms nice and simple again it's easier to build it on the screen if you can see it in front of you on paper or on a whiteboard the next step gather together all of your printed reports you probably have paperwork that you're using right now or these things you're printing out using Microsoft Word or Excel gather all those things together so you can see what kind of reports you're going to be designing later on now a word about terminology here a lot of people call paperwork forms here's a form to fill out and they hand you a clipboard with a piece of paper on it remember in database terminology a form is something we're going to work with on the screen whereas a report is something that's generally designed to be printed out or save as a PDF or emailed think form on the screen report print out can you do it the other way around can you print a form sure can you view a report on the screen absolutely but the general rule is forms are for data entry and reports are for printing so get all of your current printed paperwork together that you want your Access database to generate later and that will give you an idea for the kinds of reports that you're going to want to be able to make for example here on the screen you can see I've got an accounts receivable report and invoice and some mailing labels make a list of all the different kinds of reports you want to generate with your database the bottom line here is plan ahead a complex database takes a lot of planning you don't want to be almost finished with your database and then realize it you forgot to put something in later yes you can always add things and change things later but it's much much easier if you know in advance all the things that you want to have in your database if you forget to put a simple field on one of your tables for example sure you can add it later but then you have to remember to add it to all your queries and all your forms and all your reports so to save yourself some work later on try to get all this stuff together right now the more planning you do ahead of time the easier the job of building the database will be later on now don't worry too much about planning your queries queries are usually something you build as you need them queries are usually used to support a forum or a report but do take the time to figure out exactly which tables you'll need what fields you want in each table what forms you're going to want to design what your interface should look like what the end user should see on the screen and what printed reports you want plan ahead now to save time later thanks for watching if you like this video make sure to give us a thumbs up and comment below I post new videos all the time so be sure to subscribe to my channel for updates click to begin lesson 3 now and also click to visit my website access learning zone comm for more free videos and to sign up for the entire level 2 series for just $1 you

Channel: Computer Learning Zone

Views: 619,090

Rating: 4.9517813 out of 5

Keywords: microsoft access, ms access, microsoft access 2013, ms access 2013, access 2013, access tutorial, access training, database planning, plan database, customer table

Id: cHRLNT4MdI4

Length: 12min 44sec (764 seconds)

Published: Tue Aug 20 2013