Empower your Human Resources team with a customer contact database for human resources

How to create outlook signature

welcome to another free video tutorial brought to you by access learning zone comm I am your instructor Richard Ross in today's class we're going to learn basic contact management now contact management involves keeping track of all the contacts that you have in your customer database so if you have a conversation with a customer or a customer comes into the store anything you want to track as being a contact with that person that's going to be saved in our contact management database now this database requires another one of my database templates you can find it on this page I'll put a link in the description below so you can just click on it all it is is a simple customer database with a customer table and a customer form you can build it from scratch if you want to or you can download a template free from my website this just keeps me from having to reinvent the wheel with every tutorial ok so here I am inside the basic customer database that I built before here's a simple customer table first name last name address and so on and I've got a basic customer form looks just like that now what I want to do is every time Jim Kirk calls me on the phone or has a conversation or stops in the shop I want to track each one of those contacts now you could just put it in the notes field and that's what a lot of people do that don't know how to properly build access databases and there's nothing wrong with that but you're very limited you can't do reports you can't sort them you can't filter information it's all just in one big note field so what we want to do is want to create a second table a contact table to store each bit of information about the contacts for this customer I can have none I could have one I could have an unlimited number of contacts for this customer and they all go in a separate table so taking a look at the customer table remember from our last class that we have a customer ID here and that's a unique number that tracks each customer right Jim Kirk is customer ID two will always be customer number two I can use that customer number two the key field remember that the primary key field I can use that in my contact table to know which customer I'm talking about so let's go ahead and build a contact table I'm just going to slide the customer table up just a bit just so you can see what I'm doing here and do that so I contact the table and my customer table they're side-by-side so let's go up here I'm going to go create table design slide this down just a bit okay now every table should start with its own primary key field in this particular case it's the contact ID and that'll be an auto number now that contact ID is the primary key for this table and it represents a unique contact an instance of communication with this customer now how do I know which customer this contact represents well that's what this customer ID is for up here so I'm going to come down here and then make a field called customer ID now this can't be an auto number because an auto number says to access hey start with 1 and generate unique numbers for each record going up so you don't want to auto numbers in your table alright so this just has to be a regular number all right specifically a number of type long integer now we put in here the fields that we want to track for each particular contact for example the contact date don't put just date in there or time the words date and time are reserved words and access there's special words access will let you get away with it but trust me don't do it make it contact date that'll be a date or time I like to put in next a short description which will be short text and then I like to put in notes which will be a long text they used to call long text memo the reason why I like both is because short text is easier to do stuff with to sort to filter all that stuff long text can be very very very big fields you can store lots and lots of information on them but they're not as flexible as using short text fields so I like to have one of the short text is like a quick description if you need more notes like the customer send you an email you want to make sure you store the whole thing you can copy and paste into a long text field and that's pretty much it for now that's basic contact management the date and time and what happened later on and some of my other templates will put more stuff in here like do I need to followup with this customer and what date and time should I follow up with this customer but for now let's save this ctrl s save and this will be my contact T my contact table no primary key defined should I define one yes I'm going to notice how I put the key next to the auto number the contact ID I'm going to close this and let's reopen it contact the I'm going to open it and slide it right underneath my customer table I'm gonna slide over a little bit to okay so let's put some contacts in the contact table now customer ID zero there is no customer zero so let's say the first contact is with Sue Jones so I'm going to put in here a three yeah that works the contact date there's a special key you can press on your keyboard it's control semicolon that'll insert the current date all right control semicolon you can put the time in there if you want to as well if you want to put the data and time in there all right the time if you want to you can click after that and go space and as control shift semicolon or basically control : all right that puts a date and time in there and yes in a later class I'll show you how you can automatically have that default to right now but we'll get to that later okay a description right um asked about her cat let's say we're running a pet shelter if you want to put notes in there you can alright but I'm not going to tab tab okay next contact let's say is with Jim Kirk he gives us a call number two that's customer - alright date and time and needs more dilithium yes I told you in the last class I'm a Star Trek nerd okay and here are some notes okay next let's say sue Jones calls back I'm gonna go to this time out of here though we just care about the date I was just showing you that all right so sue Jones calls back so now we're customer three again see how you can have multiple contacts for each one these customers ignore this number this number is meaningless okay this is the contact ID will probably never use it alright this is what matters this is just in case we want to do stuff with later if we want to make reports or or link the contact table to something else okay then we might possibly utilize this Auto number but for now all we care about is this alright contact date let's say sue callback picking up our cat tomorrow I don't know why a cat shelter would need dilithium crystals but all right next contact let's say Richard Ross calls I call in right right checking on status whatever all right so you see how this works that is the number that matters this is the customer each time the customer calls in we're going to put the customer ID in this table and then the information in the contacts now you don't want to have to keep typing that number in that's a pain so what we can do is we can make our contact form and drop that inside of our customer form as a subform an access will automatically make that relationship between them and you won't have to keep typing in that customer ID let's do that so let's go up here we're going to create and then form design we covered this in the last class first thing is to double click here and the form properties and tell this form where it's getting its data from so on the data tab under a record source we're going to pick contact key this time this is the contact e alright then we're going to add some fields I'm going to shrink this up a little bit we'll come up to add existing fields there's the fields from our contact table I'm going to click on the first one hold down the shift key and click on the last one that selects them all click and drag them over here just like that okay see how easy that is let's drag this stuff up in the corner nice and neat customer ID like that ok the contact date let's get everything on lesson nice here the description the description can be a little longer ok and then the notes will go over to the over here like that and I'm gonna drag notes underneath actually we can get rid of that label all right I want to delete that label click delete we know this is aa note field again a little bit of a training issue and if you want this to look all nice and you can highlight all of these three guys click and slide them over like that I cover all this neat little form formatting tricks and stuff like this in my full beginner one class I spent a lot of time on it so I'm not gonna waste the time here all right if you want to watch that it's free it's on my youtube channel beginner level one of three hours long the purpose of these little template video is just to give you this information quick all right so I'm not going to spend a ton of time on formatting alright let's make the background color here let's go I don't know green what's the customer table that the customer forum is is blue so let's go to the darker blue let's go to the dark blue and we'll highlight these and we'll make these black like that alright so there's the contact ID customer ID whoops just right after the C in there like space contact date now here you don't have to say contact that you can just say date in your label okay because this is just for you to see all right the contact ID and the customer ID let's gray those out like that because if the the the user needs to know they can't change those I mean they could change the customer ID if they really wanted to but it will change who the contact is for and I like to take notes fields like this I like to make them look like a little sticky pads I mean I like to give them like a little tiny bit of yellow I just think it looks nice alright let's save this contact F that's my contact form and I'm going to close that down now I can open up the contact form if I want to and just come in here and look it's up but it's not very helpful okay so what we're going to do is we're going to drop the contact form inside of our customer form so open up the customer form we're going to right click design view slide this down like this make some room down here on the bottom this is where our contacts are going right down here in the bottom it's a big empty space come over here find the contact form drag it drop it right there see that I put this form inside of the other one that's called a subform alright I'm going to delete this little label here we don't need it and I'm going to slide this guy up like that maybe make it a little bit bigger that should be big enough let's make a little bigger just in case all right bring this side back over like so okay and keeping with tradition let's make this notes field yellow as well format well I've got the same color there okay that way the user knows yellow is for notes a little bit of a training issue okay here we go let's see what happens I'm going to close this Save Changes sure let's open up the customer form now all right look at that now you've got two navigation bars down here on the bottom this one is for the customers this one up here is for the contacts now notice right customer ID one customer ID one all right see how they'll link together this is only showing me the contacts for this customers because access saw that we had a customer ID field on both forms so it made that automatic relationship there is a way to make relationships manually I will show you that in a different class but for now notice if I go to the next customer right there's Jim Kirk there's his contact needs more della theum and you only seen the one contact for him if I go to the next record you can see there's Sue Jones all right one of two she's got two contacts all right asked about her cat and picking amber cat tomorrow here's the nice thing if I go to a blank new record if I want to add a contact click here all right notice the customer ID is automatically set for us it's linked these two together so that three is automatically set in that subform so now I can come down here I can put it into one right picked up cat he was friendly all right see that and there you go that's how you set up a basic contact management system the benefit of this is I can have an unlimited number of contacts for each customer right and those two tables are related together you no longer have to put all this information in the notes and there's all kinds of stuff that we can do we can make follow-up lists to-do lists based on this we can sort this information we can generate reports to shows customers all these contacts we can count the contacts all kinds of things we can do