Save Populated Validated with airSlate SignNow

Save populated validated

okay after watching the previous two tutorials the next thing we're going to do and is create a pen queries to append the rest of the data all of this valid data that we've got now populate this table we're going to use this data to populate all of the other tables and and we've then got a fully normalized database before I can do that and there's some more data that I need to import nationalities table and the hobbies table and because this data is very small and there was really any validation that we can do on that i'm just going to import those straight into the tables so I go to external data i'm going to import excel spreadsheet and browse to the location where I've got them stored so there's my nationalities table I'm gonna open that I'm going to append a copy of the records to the table nationalities okay and yes I'm happy with that file we can see here this yes it picked out the countries and the nationalities it's selected the headings the first row that is fine it's going to port it to the nationalities table just click on finish and also yeah save these import steps input nationalities yeah that's done and then I can use this import as part of a macro later on so we'll save the import I'll just double check broken up the nationalities table yes that's now been populated I'm go just to repeat the process again this time I put the hobbies in append it to the driver hobbies table okay yes it's selected the heading let's drive hobbies it's gone to the right table okay this pulls up an interesting point and it was unable to append the data due to key violations now the reason for that just come out of that cancel this and we'll just check hobbies it hasn't put anything to hobbies now the reason for that if i look at the relationships okay the hobbies and is the primary key of this table it's also the foreign key of another table which means that the hobbies must be in this table before they can be sorry actually what I've done is I've actually tried to append it the wrong way around I should have applied appended the hobbies into this table rather than the driver hobbies so because driver hobby description is a foreign key it means that you must have this hobbies populated in the hobbies table before I can add them into this one so let's try that again do my import again external data import the hobbies append it to not Drive hobbies I just want to depend that to the hobbies table that was where I made the mistake yeah we've got the correct data there it's importing into the hobbies table so I click on finish save those import steps again so we can add that to a macro later I check hobbies that's all populate it now okay the more complex and a pending that I'm going to do now is to append all of this valid data okay in the valid data table I'm going to paint that to appropriate tables so the first thing I'm going to do is create a new query in design view and what I'm going to do first of all is populate the drivers table I'm going to get all the information from the valid data table so select valid data and add that to my query I then need to add in all of the fields which are in the driver table so that is let me check driver ID first name last name world champion car number and nationality so I've got driver ID first name last name current car number well champion and nationality I'm going to change this now to an append query so I click on app end you'll then ask me which table I'm going to repent it too that's the drivers table okay and I need to set up each feel to it append it to the crepe filled so driver I deal with the driver ID driver's first name goes to drive his first name last name to last name current car number well champion and nationality ok so I've linked all the fields to the correct fields at the table who are pending it too I'm going to do something slightly more complex now with the driver ID I'm actually making this extending that making it a little bit wider and because i'm actually going to create a new driver ID number using information from the rest of the table so driver ID is going to be the name off the field ok and so i put a coat on there now this is going to be made up of the current car number and underscore and then the drivers initials so do this by putting in a square bracket and the name of the field so I want to be the first part so current class make sure to spell that correctly current color number and then close the square bracket so that will insert the current car number and then also going to put in an underscore so I use the ampersand sign ok to join separate elements if i'm going to insert a little character then have to enclose that in single quotes so that's my underscore and then i'm also going to add to this the drivers first name so i do the initial of their first name and then this was their last name so to do this i'm going to enter left open a normal brackets square brackets because i'm referring to a particular field i'm going to name that field and close the square brackets I then put comma 1 and clothes off my round bracket so just to explain this little piece of code here it means that so I made a mistake they don't use a square bracket there we go so it means i'm going to take the driver's first name but i'm only going to use the first character and i'm using the first character from the left so starting from the left i'm accessing the drivers first name field and i'm just taking the first character okay we can repeat that again so i use the ampersand symbol so that i'm also going to add the next piece and so we start from the left i'm looking at this time the drivers last name so that's the field that i'm looking at and i'm just going to take the first character from the left ok so this piece of code I've entered here it will add the current car number and underscore the drivers initial of their first name and then the first letter of their last name that is now the append query completed for appending the drivers so I'm going to save the changes to that i'm going to call it append drivers and ok that okay you can now see that I've got this append drivers query at the moment my driver's table is empty now if I run this query just gives me a message going to run the query it's about to Penn 30 rows yes that's correct now it's said here that it hasn't managed to append 25 records and that makes an error but it's actually correct because if this is due to a key violation well because in my valid data if I just click on yes to run that query anyway we'll look at the valid data well we don't want to repeat rubens barrichello a number of times or jenson button number times we only want to store a record once for each of these different drivers obviously in the unnormalized data it's still a lot of times so if i look at the drivers table now it's only got each driver stored once and that's what i wanted to do and so following on from that you've got to create append queries to append the rest of the data from walk from this table into the appropriate tables it follows very much the same process as we've done in their pen drive errs so i'll let you carry on with those yourself