Streamline Your Customer Contact Database with airSlate SignNow

How to create outlook signature

In this video we will guide you through step-by-  step instructions for creating your own contact   management database in Microsoft Access. We've also  included some hints and tips based on our many   years of experience creating contact management  databases for real businesses. There is a free   download of the database we'll be creating in this  video available on our website: the link is in the   description. Note that in order to follow this  tutorial, or use the free sample, you must have a   full version of Microsoft Access installed on your  computer. In this video we'll be working in Access   2010, but the methods will be broadly the same in  other versions, although in Access 2003 and older,   the interface will be significantly different.  We'll also be assuming a passing familiarity   with how Microsoft Access works. Before we begin,  another term for contact management is CRM, or   customer relationship management. Throughout this  video we will use the term contact management, but   the term CRM equally applies. Step 1: Designing  your Contact Management Database. The first step   to creating your database is to plan out all  the tables you'll want and the fields each one   should contain. To be as flexible as possible, you  might want to do this on paper. Each table should   hold information on a single topic. For example, the  first table we want should store the information   on the contacts in our database. We'll call this  table "Contacts". The fields we choose for this table   depend on what we want to know about our contacts.  For example, let's say we want to store their name,   address, and a company they are associated with  (if any). The details of the company are separate   from the details of the contacts, so we'll make a  second table called "Companies". Within this table   we're going to need a reference that we can use  inside the program: the name of the company, the   address, etc. This reference will be the primary key  and must be completely unique to the company. Later,   we'll use this to make a relationship between  our company field in the contact record, and   our company table, that will allow Access to see  details from the company table whilst looking at the   contacts table. There are in fact many pieces  of information about a contact that we could   store in other tables and link back to our contact  via relationships. This is either because they are   part of a larger set of other information, as with  company, or because we want to limit the possible   choices the user can make to a strict list which  the other table can store. For example, the Contact  Types table will contain a set of specific records  such as client, customer, partner, etc. By setting   up a relationship between the ContactTypes table  and the contacts, only the options to find in the   ContactTypes table will be options about field  in the Contacts table. In the end, the tables in our   database will look like this, with several tables  that altogether completely define our contact. Each   black line represents a relationship between the  tables. The 1 and infinity symbols at either end   of the line tell us that these are one-to-many  relationships, meaning that there can be many of   one piece of data attached to just one of the  other. For example, one company could relate to   many of your contacts. In your design, you should  think about what relationship types you are using   and add the appropriate symbols. When you build  the database in Access later, this will be done   automatically. Many of the relationships here  are set up to enable the user to select from   predefined entries and lists. This is the same  idea as the ContactTypes and Contact tables we   mentioned earlier. Take the relationship between  StatusList and Contacts for example. For every   possible status, StatusDesc there is a code to  identify it, StatusCode. We might assign a status   code 'A' to a status desc potential client. Then,  when entering in the information on our contact   to our database, when we come to the status code  field, we can select from a list of the status   descriptions, the things we understand, and Access  will store the associated code, the thing the program   understands. You can see other relationships  of this kind too: contact types, comm types and   company types. Feel free to copy these examples,  but in your database you will want to have the   tables that suit you, so think about the sorts  of information you store for your contacts and   whether it will be useful to have choices hard  written into your database. The relationship from   Contacts to ContactComms is a little different.  Here we have decided to store all the various   phone numbers and email addresses that might be  connected to a contact in a separate table, so as   not to clog up the original table and to account  for there being no set amount of numbers one   contact can have. Then, if we wanted to see the  numbers for a contact, we would just search our   ContactComms table for the contact in question  via their Contact Ref. This stands for contact   reference: a unique identifier of each contact.  In fact, you will need something to uniquely   identify each record in every table. Traditionally  this is the first field and is referred to as the   primary key. Note the key symbols in our design  above marking which fields are the primary key   for each table. You can make the primary key  anything you want, as long as it will never   be replicated in any entry. For example, don't make  the postcode the primary key for your contacts, as   you might have two contacts in the same building.  We recommend that you make it something short and   logical, like a number (what we call ID), a letter  (what we call code), or a sequence of characters;   for example Software-Matters can become SOFTMATT  (what we call a reference). So using these   examples, try and set out on paper all the tables  and fields you want in your database and all   the relationships you want to make between them.  Remember you'll want a table to hold information   on all your interactions with your contacts, which  will form a substantial part of your database. In   our example, we call this table "History". When you  are done, your plan might look something like this. Step 2: Reviewing your Database Design. Your design  represents your database structure. You might   look at it and realize that you actually need a  new table or a different relationship from what   you have drawn. This is why in designing first is  important because it is much more time-consuming   and difficult to change a database structure  once it has been established, than to make   it exactly how you want in the first place. Some  things to think about at this stage are: in tables   where the user will be manually inputting data,  are the fields in a logical order? For example, it   seems more natural to insert a contact's name  before their address. Are any of your field   titles more than one word? If so, we recommend  that you write them without the space, as this   will make your database easier to manipulate with  visual basic code if you ever want to insert some   more advanced features. Are there any fields  in your database that have the same name, but   aren't the same information? For example, you  might have placed a field called "Address" in   both the contacts and companies. Since they are  not going to be the same thing, it is important   in Access that they have distinct names. If you  want to give your contact management database   the ability to warn you when it's time to carry  out an action agreed with a contact, you'll want   to include a field like follow-up date in the  history table, as well as a field that tells you   whether the action has been completed yet or not,  like date actioned in our example. You might just   have a yes/no tick box called "Actioned", but this  will give you no information on how quickly you   are following up on your tasks, so it may be more  useful for your own analysis to use an action   date. Once you are happy with your design, it's time  to make it a reality. Step 3: Setting Up Tables and   Fields. When you're ready, open up Microsoft Access  and start a new blank database. We'll call ours   "ContactManagementDatabase". You will be presented  with your first empty table in datasheet view: the   mode of operation used to input data. But first  we need to set all of our fields in our table,   so we need to head into design view, which can be  achieved by clicking on the view button near   the top left hand corner. You'll be asked to give  your table a name, so let's assume you want to make   your main contacts table first. So as set out in  your earlier design, you should call it "Contacts".   When you enter design view, you'll see three  columns. The first of them is field name. All you   need to do here is go through adding the field  names you have already chosen in your design to   each row. Make sure the intended primary key has  the key symbol next to it. To change the primary   key, select the field that you want it to be, then  click on the primary key button in the ribbon at   the top. Once you have all your fields, you need to  select a data type for each one. Choose something   that is relevant to the type of information  you want each field to be storing. For a date,   you might select date/time. For a name, you will  probably want text. Now, although not required, you   might want to add a description to each field.  This is helpful because whatever you write will   appear in the status bar at the bottom left-hand  corner of the screen when a user selects the field   ready to make an entry. After that, you'll see  there are a variety of options in the Field   Properties window near the bottom of the screen.  Here, you can be more specific about what sort   of records will be in your fields, for example, you  only need 8 characters to store a postcode, so you   could set your postcode field to 'text' and set the  field size to 8. This will mean the user can't put   in more than 8 characters, preventing them from  making a mistake. Putting a greater than sign in   the format box means all text will be converted  to capitals which is also useful for the postcode   field, but also for the various references. For more  information on the other properties and how they   affect your data, press F4 while the cursor is  in the property entry window for each one. You   can also look at the properties we have used in  our sample database download to get an idea of   where each one might be applied. Go through all  of the fields in your table and make sure that   they have the properties you think they will need.  It will be possible to change them later, but for   fields that will be involved in relationships,  this can be difficult. It is a good idea to add   captions to any field names that aren't completely  clear, or could do with spaces between the words   to improve readability. Whatever you write in the  caption box will appear instead of the field name   when viewing the table in the future. Default  values are also a useful property to add. For   example, date added is a field which will show you  the date you added to this contact. Therefore, it   would be a good idea for this to default to  the current date, to save you having to enter   this every time. Your finished contacts table  in design view should look something like this. Now you are ready to make the rest of your contact  management tables. Click on 'create' at the top left   of the screen and then 'table design' to open up  a new design view window ready to make your next   table. Use the paper design you made earlier to  fill out all the tables you want with the correct   fields. You'll see all of the tables you've made  displayed in the access objects navigation pane   on the left side of the screen. Press F11 if you  can't see it, and it will appear. Take note of the   fields that you have decided are related. The  fields at each end of the relationship must   have the same properties for the relationship to  work, so text field with size 10 can't relate to   one of fields size 1. If you forget to make the  properties the same, Access will force them to be   the same when you make the relationship, which  could lead to loss of data if you have already   started putting in actual records in your table,  so it is best to get it right from the start.   Step 4: Establishing Relationships. With all the  tables and fields complete, it is time to establish   the relationships planned out earlier. For example,  let us say we want to connect the Company fields   in Contacts to something that uniquely identifies  each company, which is the Company Ref fields in   Companies. First head back to the contacts table  in design view. Now, highlight the data type of   the company field. In the drop down list you can  see Lookup Wizard. You should proceed to select   this. Don't worry, the data type you had previously  selected will still apply to that field. The Lookup   Wizard is just launched from this list, rather than  being an actual part of it. You'll want to pick the   first of the two options the wizard will offer  you. The second will allow you to input a list of   possible companies that you can't edit. Click Next  to proceed. Now select the Companies table as where   you want to look up the information from. Click  Next again. It will now ask you which fields you   want to get the information from. All you really  need is the unique identifier of the company: the   Company Ref, but in case that reference doesn't  mean anything to your user, it would be best to   select both Company Ref and formal name, then click  Next. The next page gives you the option to sort   the entries in a particular order when your user  comes to select what company their contact works   for. For example, you might pick formal name and  ascending to have them appear in alphabetical   order. However, as we want to show the Company Ref,  we should order by Company Ref and ascending. On   clicking Next to proceed, you come to a window that  will allow you to change the column width of the   fields and the dialog box. You'll notice that the  primary key is not appearing in the dialog box as   the hide key column box is ticked. In this case,  the primary key is the field that will actually   be appearing in our table, so it makes sense to  be able to see it. Go ahead and uncheck this box   for now. Pick a width for your field that you think  will be appropriate. If unsure, exit the wizard and   put some trial data into the field to which you  are linking. It will then appear in this wizard   window when you return to give an idea of scale.  Again, click Next. Next, the wizard will ask you   which field you actually want to bring into the  Contacts table. In this case, we want Company Ref,   as this is the primary key field and is therefore  the field you should almost always select here. So   select it from the list and click Next. Now you're  on the final window. The label for your lookup   field is just the field name that will appear in  the Contacts table. You probably want to leave this   as it is. Head over to the relationships window  under database tools to view the relationship you   just setup. Double click on the line linking the  two fields to open the Edit Relationships window.   You should tick 'enforce referential integrity', as  this means, in our example, that you can't have an   entry in the Company field in Contacts, if there  isn't a corresponding entry in Companies. Access   will stop you accidentally deleting or editing  your data if you would end up with something   invalid as a result. You are left with two options:  'cascade update related fields' and 'cascade delete   related records'. Cascade update means that if you  edit the entry on the one side of a relationship   that that change will automatically cascade  through to all the related records. In our example,   if we edit the Company Ref for a company, we do  want that change to cascade through to all the   linked contacts, so that they stay linked to their  company. So, we should tick cascade update. Cascade   delete means that if you delete the record on the  one side of a relationship, for example a company   record, then all the linked records will also be  deleted, all the contacts linked to that company.   In our example, if we delete a company, we want the  system to stop us if we have linked contacts, so   we don't want this ticked. Generally here, you will  only want the first two options ticked, but where the   records on the many side are part of the one-sided  ones, for example the History and ContactComms   tables, you will also want to tick cascade delete.  Finally, you can click OK and the relationship is   fully established. You can now repeat this process  for all the relationships you wish to set up using   your paper design for guidance. Take note of  the lookup tab in the properties window, for it   contains additional options you can configure  for your new relationship. Again, use F1 when   selecting an option to get information on what  it does. Once at least one relationship exists,   you can see the connection by selecting database  tools at the top of the screen, then relationships.   If a table doesn't come up, click 'show table' at  the top and select the table. You can expand the   window and move the tables around in the diagram  to get a layout that seems logical, which is   especially important when you have many tables  and relationships to consider. Set up all your   relationships, then return to the relationships  window and click on all relationships to see your   database in full and compare it to your initial  design. Your database is now ready to receive data.   So far, your database simply stores information and  allows you to come and look at it for reference,   but the true power of a database comes from the  clever way it can collate information from across   multiple tables to extract the details that you  need to know. If you wish to expand your database   to do such a thing, keep watching. Step 5: Creating  Queries. A query is a database object that can make   table like objects called Dyna sets, that combine  details from multiple tables in ways you can   specify. In this example, we're going to create a  query that will show us all the follow-up actions   in our contact management database, in order of how  urgently they need to be done, allowing the user to   easily prioritise their communications. Click on  'create' and then 'query design'. You will be taken   to the query design window and immediately asked  which tables you want to see. In our example, we   need history, for it contains the information  on the follow-up dates and the Hist Details   fields, which will contain some information on what  exactly it is that needs to be followed up. We also   need contacts, for we will want to see the details  of the person we need to get in touch with. You   will see the tables are brought into the window  and the relationships between them are marked.   Now you need to pick the fields that contain the  information we want. Just double click on the field   names in the tables. We will need follow-up date,  Contact Ref, full name, Hist Details, and perhaps   contact notes. We will also need date action from  history, even though it won't be something we want   to see in our query. This is because we need  it to determine whether a history record with   a follow-up date has been followed up yet or not,  so select date actioned too. The fields you selected   are now visible in the columns at the bottom of  the window. Now we need to start using the power   of queries to get only the information we need  from these fields. First consider the follow-up   date field. In the sort box, pick ascending. This  will make the date that is furthest in the past   appear first in your query, which is useful as  this follow-up is the most urgent. Next, select   the criteria box. Here, we can enter conditions that  a record must satisfy to appear in our query. For   example, if we don't want to see any information  on follow-ups that don't need to be done for at   least a week, we could enter less than or equal to  date plus 7. This means all displayed information   will be on follow-ups due on or before this time.  The other field we need to consider carefully is   date actioned. Firstly, all the entries we want  to see in our query should have nothing in their   date action record, so we may as well uncheck  'show', since it will just be empty. To make sure   we only get empty ones, we need to write in the  criteria box is null. Overall, this query will be   a dyna set that lists follow-ups that haven't been  action yet in order of urgency, where the person it   regards and some details of the follow-up. To  see that table, click on 'run' in the ribbon at   the top of the screen. Keep in mind you'll need  some actual data in your database for this to   produce anything. Click the X in the top right of  the window to close the query, then click yes to   save it. You should name it something along the  lines of follow-ups due, ensuring it is all one   word. To reopen the query, double-click on it in  the navigation pane on the left of the screen. Other Access Features. The two other types of  database objects you can add to your contact   management system are forms and reports. Forms  allow you to create user friendly windows for   adding or changing records in the database,  rather than having to go into the tables and   do so there. Reports allow data to be displayed in  a customizable way that can utilize the results   of any queries you have set up. Both of these  Access features can be set up through wizards in   the create menu on the ribbon, but are not required  for you to use your new database, so are beyond the   scope of this video. The hidden power of Access  comes from the ability to create and automate   processes using VBA, Visual Basic for Applications,  code. This is a more advanced topic that you should   consider looking into when you are comfortable  with your current database, and there are more   features that you'll want that Access can't provide  on its own. This video has explained to you how   to set up a simple contact management database  for use in Microsoft Access that can aid the   administration of your business. If you'd rather  not try to create one for yourself, make sure to   download our free contact management example  database that you can use as a simple contact   management system right away. Be sure to check  out the link in the description to our contact   form if you have any questions about anything  covered in this tutorial. If you want to get more   from your database, contact us to find out how we  can expand and customize your contact management   system to provide a more powerful and streamlined  tool that goes far beyond basic features explained   here. Contact management is just the beginning  for most databases. We at Software-Matters   can produce databases that can apply to any and  all aspects of your business and link all your   operations together into one system. If you need a  professionally made bespoke database of any kind,   then contact us to discuss your needs and decide  whether you would like our database experts to   craft a system designed to meet your business's  needs, no matter the size. Be sure to check out the   link in the description to our contact form if  you have any questions about anything covered   in this tutorial, or feel free to call us on 01747 822616.  If you enjoyed this tutorial and found it useful,  please leave a like or a comment and click the   subscribe button to make sure you don't miss out  on any other of our tutorials. Thanks for watching