Access 2016 - How to Make a Database - Part 1 - Tables

so in this series we're going to create a new database using Access 2016 depend on what version you're using these screens may be somewhat different but most of the functionality should be there so in Access 2016 when you first open a new project you are given a variety of templates which is going to click on blank desktop database we're going to change the name to DB tutor and we're going to accept the default location and click on create when you open a new database it opens to a default table just click on the X in the upper right corner to close that out let's just take a moment to look at the interface so at the top you have several tabs and each tab has different tools this is referred to as the ribbon on the left side you have a listing of all of the objects within the database so databases store information what's known as tables tables are really the heart of the database that is the repository of data that is where you store the information that you're trying to retrieve and track and report on databases can have many different tables those tables can be linked and we're going to look at how to do that a little bit in this video so let's go ahead and create our first table so we want to click on create and we'll go and click on table and it looks pretty much what we're just looking at so we want to actually design this and not just accept what they've given us so at view click on the down arrow and choose design view it's going to ask you to name this let's call this since we're going to use several different tables we don't want to use just main table because in a real company you're going to have various tables that store various types of information so let's call this ticket tracker and we'll append the word table and go ahead and click on ok so now you're in as we said design view this little symbol here it's known as primary key we're not going to get into that right now the way you toggle it on and off is with this button so we're just going to shut that off for now so you have two columns field name so the field name would be something like customer first name customer last name ticket number things like that the data type is how is this formatted if you've used Excel you'll be familiar with how a column in Excel has AI can be formatted as text can be formatted date can be formatted as number very similar so the it's a good comparison to compare an access table to an Excel spreadsheet because they functionally are very similar there's differences but if it gives you a basic idea of what a table is it's basically a spreadsheet so let's start with case number as the field name because it's going to be a ticket tracker case management database and the data type is auto number so that's fine so that's going to start with one two three four now we're going to add a few more fields so let's say customer F name and this will be short text depending on what version of access you might not have an option that says short text it might just say text because in earlier versions there was there was text and then there was memo text aloud for 255 characters memo allowed for much larger amount I'm presuming that for consistency they got rid of those two terminologies and now you just have short text and long text so we'll choose short text let's now use customer L name this will also be short text now we'll put in associate name we'll just use a full name layer and then we'll have incident date and this will be date time and then we will let's just capitalize that to be consistent then we will have incident details so this will be the actual description of what's going on we'll have that be long text now depend on what you're doing you might have the entire description in this one field some ticket targets may have two fields one for the problem and then one for the resolution so let's do that that will also be a long text and then we will do ticket should we use the word case so we'll call this case status and I'm just trying to be consistent since we said it's a case number then we should really talk about case status rather than calling it ticket status likewise these are the incidents so incident date incident details incident resolution and that should about do it so let's go ahead click on save so it saves the format and then click on the down arrow and go back to datasheet view now you can't really read the names because the columns are too narrow so if you point up the column you can then click and expand the width of the column and whenever you make a change like this this is considered a layout change so access will prompt you to save so once I've made these changes we'll go ahead and save again now you're actually free to enter information here so let's just quickly put in some information and to go from field to feel that I'm tabbing widen this one a little bit more because it's a date it prompts you with a calendar is in details and there you go you have now entered a record into your database a couple things to point out one this really isn't a great way to enter using the datasheet view we're going to talk in another video about a better way to enter data but I really want to look at the format of the data in this one so a few things incident date if the incident date is always the current date that you really shouldn't prompt for a counter you really should have this be a fixed field likewise status you probably only have a few statuses closed-open escalated so you really don't want this meanly typed in because if you're running reports and someone misspells the word it's not going to come up on your report like say you're looking for all open cases and someone types Opie and E they misspelled it and you're searching for the word open it won't find it so let's go back to design view and we'll make some changes so for incident date so when you highlight a field down at the bottom here you actually have some attributes that you can work with default value is the one we care about for this so if you type date with an open and close paren see after it that will populate it with the current date for case status we don't want a default value per se but what we do is we want a selection so if you click on the datatype and there's a lookup wizard you're going to get a prompt I want the lookup field to get the values from another table we'll look at that in a minute while I will type in the values that I want we'll do that one in this case we don't expect the statuses to change in the long term closed-open escalated really no reason to link it to a table the reason why you'd link to another table is if you're expecting these values to change and then we'll look at why values would change so let's do open escalated and closed again you have more values this is just meant to be just demonstrative so we'll go next do you want to limit entries to the choices yes limit to the list do you want to store multiple values for this logo no one needs to be mutually exclusive and then we click on finish so go ahead save go back to datasheet view and you're going to notice a few things first of all the date is now pre-populating second law if you try to do the status you now have a drop-down box with your choices so so far so good associate name you really don't want people to be able to invent associates so you want to pull that in but unlike the case status depending the size your organization your associate names may be coming and going particularly your in a larger organization so let's create a new table so let's go to create table we'll go to design of you and this will be employee dir for directory get rid of primary key and this is going to be EMP name we'll just use a single name rather than first and last we'll make this a short text EMP I D will also make this a short text so even employee IDs maybe numbers you really don't want to manipulate it you don't want to calculate it and if there's like leading zeros you don't want the service to be dropped off so even if you plan on having numbers for an ID you really want to treat it like text and we'll save that let's go back to datasheet view and we're just going to do a little mini data entry just like we did in the other table so and for consistency I'm going to go back and change these two lower cases all right we'll stop there for now we'll save that again you don't need to save when you enter data because as soon as you put data in the field that's it it's been it's been stored in the database but I widened the columns so that is a layout change and so that would require me to save it so let's go ahead and close that one now keep in mind that when you close it it doesn't delete it the only reason why that first database delete it is because that first table excuse me deleted was because it was a default table there was nothing stored in it no fields were saved so would that new table save we can now link the two together so go to design view for associate name choose lookup wizard just like we saw last time except this time we want the top choice where it reads I want the lookup field to get the values from another table employee directory is already selected pushover employee name and we'll have this sorted by employee name shows the current list we don't want multiple values so we'll leave that blank you get a prompt to save the table click on save now let's go back to the datasheet view so if we try to create a new ticket okay a new record when we come to associate name we now have a drop-down box just like this one over here but whereas this one is merely entered and can be manually changed this one actually is a link to another table so we'd have to go up to that other table to change it now because I use so few examples it might not seem like a big deal but when you start linking associate name two three four or ten or fifteen different tables you would have to go to each and every one those tables and change the list if you didn't link it like this so because it's linked then anytime this has changed than any table that is linked to it it will also change so right now because it's only one table it looks like it'd be a wash either way but once you start linking that employee directory out to multiple tables then it really does make a difference

Channel: Design and Deploy

Views: 1,455,967

Rating: 4.8209877 out of 5

Keywords: MS Access 2016, Database Design, How to Make a New Database

Id: PBhftKTmdHI

Length: 15min 52sec (952 seconds)

Published: Fri Mar 25 2016