Tutorial - Creating an MS Access Contact Management Database
< Previous PageCreating Queries
A Query is a ‘database object’ that can make table-like objects called ‘dynasets’ 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 what tables you want to see. In our example we need ‘History’, for it contains the information on the follow up dates and the HistDetails field which hopefully 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 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 FollowUpDate, ContactRef, FullName, HistDetails, and perhaps ContactNotes. We will also need DateActioned 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 DateActioned 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 FollowUpDate 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 week, we could enter <=Date()+7. This means all displayed information will be on follow ups due before this time (Date()) next week (+7). <= means ‘less than or equal to’.
The other field we need to consider carefully is DateActioned. Firstly, all the entries we want to see in our query should have nothing in their DateActioned 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 dynaset that lists follow ups that haven’t been actioned yet in order of urgency, with 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!
Other Access Features
The two other types of ‘database object’ 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 manually.
Reports allow data to be displayed in a customisable way that can utilise 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 article.
The true 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 you want that Access can’t provide on its own.
Review
This article has explained to you how to set up a simple contact management/CRM/prospect 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.
If you want to get more from your database, contact us to find out how we can expand and customise your contact management system to provide a more powerful and streamlined tool that goes far beyond the basic features explained in the this article.
Contact management is just the beginning for most databases; we at Software-Matters can produce databases that 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.
If you enjoyed this article or found it useful, why not tell others about it?
Software-Matters is an all-UK software development team based in Gillingham, Dorset, and close to Wiltshire, Somerset and Hampshire. Nearby cities include Southampton, Bristol, Bath, Bournemouth, Poole and Salisbury.
