Access: Database Creation Participation Project for the West Virginia K-12 Education Problem

Welcome to the video for the Database Creation Participation Project To get started, you’ll need a copy of the data files for this assignment I’ve saved mine to the Desktop I’m going to extract them by right-clicking on the Data Files and going to Extract All I’m going to choose to use the default location here which will place the files in a folder on my Desktop I’ll click the Extract button You can see here that there are 5 files that are included in that Data Files We have a couple XML files and a CSV file I’m going to go ahead and close this Explorer window We’ll start up Access I want to create a blank database, so I’m going to click on the Blank Database button And then I’m going to browse and place the file on my Desktop so that it’s easy to find According to the instructions, we want to name this file as education_ppdc_wvkep.accdb Go ahead and click the Create button You can see here that I have this new database It comes with this Table1 that I’m not really interested in, so I’m going to click X to close Table 1 Now that we have our database created, let’s begin importing some tables For this, we want to go to the External Data ribbon, click on New Data Source, From File, and the first file we want to import is an XML file so choose the From XML File option We want to go here and we’re going to browse for where we have the file stored So click the Browse button The file is on the Desktop, and in this Data Files folder is where we extracted them to The first file we want to import is counties.xml, so I’m going to double-click to select that I’m going to tell it OK Access is going to ask us how we want to import this data here XML files can contain the structure of the database, data, this last option here, or they can contain structure and data so they have a fully formed table with records in it Structure and Data is what we want to import here, so we’ll double check that structure and data is the selected option We’ll go ahead and click OK And then we’ll click Close to get out of the Get External Data dialog box You can see here that Access has imported the Counties table You can double-click on it and then we can see that we have 55 records, one for each county We’ll click X to close out of that table And we’re going to continue on importing our other tables So, back under New Data Source From File, XML File, the next file I’m going to import is annualstatistics.xml So, I’ll select that file Click OK Again, we want structure and data So we’ll click OK Then we’ll click Close Next, we’re going to go back to New Data Source From File, XML file We’re going to import schools.xml We’ll tell it OK Structure and data is what we want to import so we’ll tell it OK again And click Close The last XML file we are going to import is for the AnalysisQuestions table We’ll go back to New Data Source, From File, XML File I’m going to select analysisquestions.xml Tell it OK Import structure and data, so we’ll tell it OK Go ahead and click Close Next, we’re going to import from a CSV So, we’ll go to External Data, New Data Source, From File, and for CSV files, we want to use the option to import from a text file since a CSV is really just a text file Here, let’s go ahead and browse We’re going to import dropouts.csv Down at the bottom, we have a couple options here The first one is the one that we want to use We want to import the data into a new table in the current database The second option is to append a copy of records You would use this if you already have a table created and you’re just adding additional data to it The third one, you’re probably really never going to use That one is just to link to the external data source with a linked table It’s uncommon that you would have a case where you would want to be linking to an external table separate from a database so you’re probably never going to use that third option But with the import to a new table option selected, we’re going to click OK Depending on which version of Access you’re running, and if you’re run the Import Wizard or not yet this session, you might get a warning here about potential security concerns for when it tries to run the import wizard There is nothing to be concerned about here Just go ahead and click Open We’re going to start up the Import Text Wizard here We need to first of all tell Access how our data is formatted We’re dealing with a comma-separated or comma-delimited file, that’s what the CSV

stands for, so we want to choose that we have a delimited file Go ahead and click Next If we look at the data preview, we can see that this very first row contains names for each of those columns So we can choose the First Row Contains Field Names box so it will use those names when it names those fields We’ll click Next Now, Access is going to ask us basically to verify what type of data is in each of these fields It’s going to make its best guess as to what is there The first field, County, short text is an appropriate type to use there as it has a fairly short number of characters and they’re alphanumeric so we need to use a text type option SchoolYear, even those the years themselves are numbers, because we have a dash in there, we can’t use any of the numeric types We’re going to have to use short text for this one Now, DropoutRate, those are percentages Here, we have a decimal representation of that Since we’re dealing with a decimal number, we want to use the double data type You can see here that Access has already figured out that we want to use double as the data type so we’ll click Next At this step, Access is asking us what we want to do for a key for our tables We can have Access add an AutoNumber-type field to serve as the primary key If we already have an existing field that works, we can choose that Or we can tell it to have no primary key In this case here, as the instructions are telling us, we are going to use a composite key with the County and the SchoolYear because we need both of them together to ensure we have a unique value There’s multiple records for the same county you can see here Multiple records for the same school year, so we can’t use any of those by themselves as a primary key What we want to do right now is to tell Access that we want no primary key because we’re going to go back and set that ourselves later Go ahead and click Next We’re going to import this data to the Dropouts table so we’re going to choose Finish And then we can click Close to get out of the Get External Data dialog box here Now, we need to go and make a change to that Dropouts table that we just imported so I’m going to right-click on it and go to Design View And we said that we need to go and set a composite key with the County and SchoolYear So I’m going to highlight both those fields just by dragging with my mouse, and then I’m going to click the Primary Key button on the Table Tools Design ribbon You’ll see that both fields here get the little key symbol beside them Let’s go ahead and save that change We can click Close to get out of that table Next, we’ll create the SchoolTypes table For that, we’ll go to the Create ribbon and click on Table Design For this table, we’re going to have two fields as given in the instructions Let’s start to create them The first one is going to be called SchoolTypeAbbrv or school type abbreviation That one is going to be a short text field and this one is going to be our primary, so on the Table Tools Design ribbon, click the Primary Key button You’ll see that one has a little primary key icon now Second field is going to be SchoolTypeName This one is also going to be a short text field Let’s go ahead and click the Save button We’re going to name this as SchoolTypes Type that in and click OK Now, let’s go ahead and add the records for our table So I’m going to go over to Datasheet View here by clicking the button on the Table Tools Design ribbon I’m just going to expand the size of these columns so it’s a little bit easier to see what we’re typing in So, we’re given 11 records here in the instructions Let’s go ahead and type them in The first one has a school type abbreviation of ELS and that’s going to be an elementary school We’ve got INS, Intermediate School MDS, Middle School HIS, High School EMS, Elementary/Middle School MHS is Middle/High School EMH is Elementary/Middle/High School OTH is Other PRE is Preschool ALS is Alternative School and TEC is Technical Center We can see down here at the bottom that we have 11 records, which is what the instructions tell us we have, so we can save our changes and close out of the table It’s a good idea to keep tables closed if you’re not actively doing something with them because having extra tables can cause problems if you’re trying to make relationships or work with queries For Step 5, we’re going to modify some our existing tables to add lookup fields That way, we can select values from a dropdown list rather than having to type them in

Using a lookup field makes it easier for users and also reduces the chance of data errors The first thing that we’re going to do is we’re going to modify the Schools table So, let’s go right-click on Schools and open it in Design View The instructions say here that we want SchoolTypeAbbrv to be a lookup field So, under SchoolTypeAbbrv, I’m going to go to the Data Type column and from this dropdown list, I’m going to choose Lookup Wizard Here, I got a security concern Just tell it Open We’re going to start the Lookup Wizard here In this case, the values that we want are going to come out of the SchoolTypes table that we just made So choose the option that “I want the lookup field to get values from another table or query.” Tell it Next We want the values to come from the SchoolTypes table so we’ll choose that and click Next And here, I’m going to go and add both the SchoolTypeAbbrv and SchoolTypeName to the fields that are going to be shown I’ve added both of those fields by clicking the right area I’m going to click Next The instructions say that we want to sort by school type abbreviation in ascending so I’m going to choose SchoolTypeAbbrv in ascending order If I wanted descending order, I would just click that button to toggle it but ascending is what we want Let’s go ahead and click Next Here, we want to uncheck the “Hide key column” box because we want to show both the abbreviation, which is the key, and the school type name I’m going to resize these columns a little bit so that we can see their full text and them I’m going to click Next Here, Access is asking us which of the fields we actually want to store in the database We always to store the key field so that our relationships work correctly SchoolTypeAbbrv is the key field, that’s the one we want to store, so with that selected, we’ll click Next Here, Access is asking us what we want to name the field SchoolTypeAbbrv, the existing name, works well We want to go and enable data integrity This is basically the same sort of thing as referential integrity in the Relationships window It’s going to make sure that we don’t have problems with data in one table getting out of sync with data in another table The instructions tell us that we want to restrict deletes The way this is going to work here is that if the SchoolTypeAbbrv changes, we actually change the abbreviation itself, it will go through and update the Schools records but we will not be able to delete a school type is there any schools that type That’s what the restrict deletes part means Let’s go ahead and click Finish Close out of there We’ll get a warning that the table has to be saved before the relationships can be created It’s just asking us if we want to do that We’ll tell it yes And, let’s go ahead and click out of the Schools table We’ll close that For Step 5b, we’re going to create a lookup field for the SchoolYear field in the AnnualStatistics table So let’s right-click on AnnualStatistics and we’ll go to Design View I want to go down to SchoolYear and click in the Data Type column We’re going to go down and choose the Lookup Wizard, but this time, we are going to choose the option that says “I will type in the values that I want.” You can go through, and what we are going to do here, is to type in the specific options that users are going to be allowed to select from as opposed to having it look up those values from another table So, with the “I will type in the values that I want” option selected, we’ll click Next And now, we are actually going to type in the values we want We want 2010 to 2011, 2011 to 2012, 2012 to 2013, 2013 to 2014, 2014 to 2015, and 2015 to 2016 Those are going to be the options that users will be able to select from Once I have that typed in, I’m going to go ahead and click the Next button And we’re asked what we want to name it SchoolYear works well We’re going to limit to list because we only want people to select from those options that they were given When you go through and define your own options, Access allows you to allow multiple values so if someone wanted to go and select multiple school years, if you checked this box to allow multiple values, they would be able to do that Generally speaking, that is not something that you want to do It is not something that is a standard database design convention and it can cause other problems later Make sure that’s unchecked, and click Finish Now, you can see here that it has gone back to saying that this is a Short Text-type field Because the value that is actually being stored in the field is Short Text, it will list the type as Short Text If we look at the Lookup tab down below, we can see it’s a combo box with these options

So, if you set something as a lookup field, it’s not going to say Lookup field in the Data Type It’s going to show what the underlying data is being stored in the database So, if we go back and look at the Schools table, it’s also going to be Short Text there because it’s storing the short text SchoolTypeAbbrv field Let’s save our changes and close out of the AnnualStatistics table For Step 6, we are going to create our relationships It’s important that before you go into the create Relationships Window, you have all of your tables closed All of our tables are closed, so let’s go to the Database Tools ribbon here We’ll click on Relationships You can see here that since Access had to go through and create a relationship for doing that lookup field between Schools and SchoolTypes, that relationship is already there Let’s go ahead and show tables, so I’m going to click the Show Table button here I’m going to add all the other tables to our grid here except for AnalysisQuestions AnalysisQuestions is kind of a special one for us It does not have a relationship to anything else but the data in AnalysisQuestions does not bear a relationship to anything else in the rest of the dataset So, we’re not even going to deal with AnalysisQuestions You’re never going to have to create a relationship to it in any of the CS101 assignments Let’s go ahead and add the other tables we need here We already have Schools and SchoolTypes Let’s add Dropouts We’ll double-click on it to add it We’ll add Counties and we want to add AnnualStatistics Go ahead and click Close now that we’ve added these tables I’m going to reposition things a little bit here so that it’s easier to see how we have our relationships set up OK, now that I’ve finished moving everything around here, we’re going to work through the list of relationships that we’re given The first relationship we are going to make is between the Counties table and the AnnualStatistics table If you take a look at these two tables here, you’ll see that they both have this County field, so the same name, and if we look at the data, they store the same type of data So that’s going to be a good place for us to start with building our relationships In this relationship, I want to select County from the Counties table I want to hold down my mouse button and drag over to County in AnnualStatistics I’ll release the mouse Here, it’s going to open up the Edit Relationship window You can see on the Table/Query side here that we have the Counties table with the County field In then in the AnnualStatistics table, we have the County field there as well The instructions tell us that for this setup here, we want to enforce referential integrity but we do not want to cascade updates or cascade deletes so we’re going to leave these ones unchecked We’ll go ahead and click the Create button You can see we have this line between the two fields here showing that we have the relationship and since we used referential integrity, it also goes through and shows us this is a one to many type relationship Let’s go ahead and get ready for the next relationship This one is going to be between the Counties table and Schools We can see that we have a County field in both tables So let’s go and select County from Counties and drag over to County in Schools We’ve got that part set up and we’ll enforce referential integrity and click Create And then, we’re going to create from the County field in the Counties table to the County field in the Dropouts table We’re going to drag over there, click to enforce referential integrity again and click Create The next set of relationships we are going to make are between composite keys, where there are multiple keys for the table You have to be careful when making relationships between composite keys If you’re going to be using referential integrity, Access can be picky about which table you start to drag from and which one you drag to So, sometimes, you might have to go through, if you start to go through and create the relationship one way and try to turn on referential integrity and it doesn’t work, you might need to cancel out of that relationship and try it the other way In the instructions here, it’s actually telling us specifically which one is going to be the table and which one is the related table so you want to start with the one that is listed as table So, we want to go to Dropouts, and this one here, we’re going to create a relationship to AnnualStatistics We can see that both of these here have the County and the SchoolYear Actually, in this case here, both of these fields are part of the composite key in the Dropouts table and they’re not in AnnualStatistics I’m going to start from the Dropouts table like it says in the instructions I’m going to select County and drag over to County in AnnualStatistics Now, when I dragged over there, you can see that it has the County field

We need to go down below and click to select that we want SchoolYear So, if you have multiple fields that you are making a relationship on as part of a composite key, you need to do it all at the same time as part of this Edit Relationships window Don’t try dragging two separate times Let’s go ahead and click to enforce referential integrity We’ll click the Create button You can see now that we actually have two lines between those tables We’re also going to create a relationship between Schools and AnnualStatistics You can see that the County and SchoolName fields are the ones that are in common there So, Schools is the main table, it’s the parent table, that’s shown in the first column in our instructions, so I’m going to click on County in Schools and drag over to County in AnnualStatistics Here, I’m going to add the SchoolName as a second field for this relationship And then I’ll click to enforce referential integrity and click Create Let’s go ahead and save our changes and close out of the Relationships Window now that we’re done making our relationships For Step 7, let’s take a look at the analysis question that we were given We’re going to open up the AnalysisQuestions table and we’re going to type in our response here as a new record So, let’s say this is going to be Question 7a The prompt here asks us if there is a relationship between students’ attendance and their academic achievement We’re asked why do we think this is or is no the case My response here is that there appears to be a direct relationship between student attendance rates and academic achievement Students are more likely to succeed in academics when they attend school consistently to improve their knowledge and skills Poor attendance can lead to later problems like an increased likelihood of dropping out Once you have your answer typed in, go ahead and save your changes Click to close out of the AnalysisQuestions table We’re going to try to run the Compact and Repair Database utility Go to the Database Tools ribbon here Click on Compact and Repair Database In this case here, it ran fine Occasionally, you might get an error that the utility could not be run because the file was in use If that happens, OK, just move on It’s not really going to cause any major problems At this point, we are done with the Database Creation Participation Project