Creating a Contacts Database in MS Access
How to structure Microsoft Access tables for an extensible contacts management database.
Last updated on 2019-04-22 by David Wallis.
The majority of databases I’ve worked on over the decades have held information about contacts: names and addresses, telephone and mobile numbers, and email addresses.
There are templates for DBs (databases) that come with your purchase of MSA (Microsoft Access) and that are available off the web. Of those I have seen, I considered none was fully fit-for-purpose.
If you construct a contacts database properly from the outset, then extending it, or incorporating it as a module into any future database you plan, will save you a lot of time and possibly a lot of expense. Hence this piece.
Before launching into its construction, I recommend you alot time for considering what purpose your DB is to serve and, were it to prove a triumph, what development it might benefit from in the future.
You will also need to assess your skills levels in respect to how much you know about working with MSA, and to assess what time you can spend on the development of your DB.
Risks Caused by Not Planning
Each person who has asked me for help admitted that when they created their DB they did not give enough thought to its foundations — to the data tables and the relationships between them, in the process stage of database design referred to as “data modelling”.
Instead, they whacked in a few tables and then spent what they now recognise was an inordinate amount of time on user interfaces and reports. Breakdowns during use of their DB lead to subsequent fiddling that wasted more time. Time that always was going to fail because no amount of fiddling would get around subsidence of the foundations.
Major panics occurred when it was realised that the tables were not fit for purpose. With the original tables filled with months’ worth of records, there was the challenge of redistributing data about the revised family of tables. On top of that, the effort of re-doing queries, forms, reports and code.
Inevitably, restructuring caused interruption to business.
Planning for Contacts
In my view there are two sorts of contacts: people and organisations. The information a DB holds for a person will be different from that for an organisation.
First names and last names are the minimum information that you need in a contacts DB to identify a person. That said, people change their names: Jacqueline was first known to me as Lyn, who on marrying not only adopted her husband′s family name but also changed Lyn to Jac.
Does your database need to track any person’s name changes? It well might do if you intend it to track the services you’ve supplied to a client who over the years has changed their name.
If Mary Greene placed an order for widgets in 2016 and, as Mary Brown (née Greene), has just placed another one, then your DB needs to identify Mary as the same contact for both these orders.
An organisation is recognised by its business name. That is the minimum you need to record about it as a contact.
The different nature of contact information for people and organisations leads to the conclusion that we need two data tables — one for each type of contact. We resist the temptation to cram everything into one table.
Additionally we must also allow for people to be members — employees, representatives, etc — of organisations.
You need to be certain your planning is complete before moving on to blueprinting your contacts tables.
Planning for Addresses
A person has a home address. She may have a work address, which is different to her home address. She may have an address to which she is temporarily seconded.
An organisation has a head-office address. It may have regional offices, each with their own address.
People and organisations change addresses.
It’s essential to have clear in your mind exactly to what aspects of addresses your DB is to cater before blueprinting your data tables.
Off-the-Shelf Contacts Tables
A number of websites giving guidance on creating contacts databases, present a design for a contacts table similar to this one:
In this table the ContactRef field holds the unique identifier of a person in the DB. Other tables will link to a particular person by reference to her ContactRef.
Imagine that in 2016 you input Mary Greene as a new contact. The DB assigned her record the ContactRef of 695. Other tables will be using 695 to identify Mary Greene.
In 2017 Mary marries and chooses to adopt her partner’s family name of Brown. So what do you do? Replace the "Greene" FullName with "Brown", thus losing any trace of a Mary Greene?
Or, do you start a new record for Mary Brown, resulting in two contacts in your DB — 695 and latterly 2031 — who in real life are the same person?
Faced with such choices as these is reason enough for me to reject the table design above as unsatisfactory. There are a number of other reasons too — see Addresses Table, for example.
Unfortunately, the above table design is widely promoted. When I last checked my Office 365, the supplied template for a Contacts DB came with the table on the left and for a Customer Service DB with the one on the right:
If you are concerned only with bunging something down and are not minded to consider the future, then I say bung it into Excel. Avoid Access.
If you are looking to your business in the future, then please get your database off on the right foot. For if you don’t, hopefully I’ll be welcoming your call for help in due course!
Blueprints for Contact Tables
I recommend two tables for your contacts, one for organisations and another for people.
tblOrganisation holds the information about an organisation that does not change with time or circumstances. Its Name is all you need to begin using the table.
Other fixed information, eg VAT Registration Number and Business Registration Number, would go in this table, if needed.
tblPerson holds the information about a person as we know them at the moment, that is, by their Firstname and Lastname. So why the prefix Current?
Because we are building in provision for tracking name changes should our DB need to provide for this in future.
Fixed information about an individual, eg date of birth and NI number, would get fields in tblPerson if needed.
Organisations and Their People
Some, if not all, of the people in your DB might be employed. If you want to keep track of a person’s employment, then we need to plan for this.
An organisation may have many employees; an employee may move between jobs within his employer’s organisation; a person may move between employers.
To record associations of organisations and people, I propose a new table. Name it tblOrganisationPerson.
tblOrganisationPerson has to manage the many-to-many relationships that can exists between organisations and people — an organisation may have many people; a person may belong to many organisations during her working life. Here goes:
This new tblOrganisationPerson provides for an organisation having as many people as you need to assign to it. The table also allows you to track an individual′s employment history across as many appointments as any human could possibly entertain.
The function of the DateFrom and DateTo fields is to allow your database to track job appointments from when each one starts to when it finishes.
In a record in which you have not input a date into DateTo means that the person is still in that job. DateTo is a much more effective means of tracking current job positions than the “IsCurrent” or “IsLive” fields behind check boxes and combo boxes you see in so many DBs.
Blueprint for Addresses
Within a well-designed DB the details of an address need be input once only, into a data table like this one:
Throughout the database an individual address will be identified by its AddressID.
Only an appropriate ID needs to be passed in order to establish a person’s, or an organisation’s, address. This way repeated typing of all the lines of the same address is avoided.
Contrast this approach with that of the off-the-shelf tables mentioned above. In those, if you had four contacts at the same business, for example, you would need to input the same address four times.
Addresses for People
For your database to hold information about a person’s change of addresses, I propose tblPersonAddress and linking it to both tblPerson and tblAddress:
Leaving tblPersonAddress.DateTo blank is the way that you indicate that the address is the person’s current one.
Keeping track of a person’s address at work is explained in the People’s Work Addresses.
Addresses for Organisations
Perhaps not as often as people, organisations change addresses nevertheless. So a table similar to the one linking people to their addresses provides for the history:
Leaving tblOrganisationAddress.DateTo blank is the way in which you indicate that address is the organisation’s current one.
People’s Work Addresses
Provided you have linked a person to an organisation via tblOrganisationPerson and linked that organisation to an address, via tblOrganisationAddress, there is nothing more you need to do to establish the address at which that person works.
The absence of a date in tblOrganisationPerson.DateTo indicates that a person is still with the organisation.
The Six Tables So Far
With your six tables you have the basis for a contacts DB that allows for expansion in the future and for importing into any DB in which you need to record contact information.
These tables provide for:
- Tracking people’s name changes
- Addresses for people and organisations
- Tracking address changes for people and organisations.
Your contacts DB can supply Microsoft Word with names and addresses for letters, mail shots, news letters and so on.
We should take these factors into account when planning for phone, mobile and fax numbers:
- People can have more than one number
- People change their numbers
- A person may have different number for different purposes — family and friends, business, etc
- Organisations’ numbers follow those patterns.
Let me know if you need tables for phone numbers urgently and I will get on with them.
We should take these factors into account when planning for email addresses:
- People can have more than one email address
- People change their email address and therefore it may be important that our DB caters for keeping track of these changes
- Peoples’ email addresses may serve different purposes — family and friends, business, etc
- Organisations’ email addresses follow these patterns.
Those requirements lead us to a table that will provide a one-to-many relationship between a person and her emails:
If we want to record to what purpose each address may be put, we could add the tluEmailPurpose table:
I apply the prefix tlu to the names of tables serving the same purpose as this one — delivering predetermined lists. tlu distinguishes such tables from tbl-type ones, which carry variable data.
Entries in tluEmailPurpose provide the user with a list of the purposes that we decide upon. The list can then be the record source of any combo box we want to include in a form.
At any time you choose, you can add to the list. With this flexibility built-in, your DB does not need structural modification when you come to need “Brochure” added to the list.
Download Data Model
The download is a zip file DMW_Contacts_Data_Model.zip. Extract to get the file DMW_Contacts_Data_Model.accdb that contains the tables discussed on this page.
Shortcomings of This Data Model
We have our tables and relationships for our contacts database. Now is the time to reflect: have we fallen into the traps alluded to in Initial Considerations; does the data model suit our particular type of business; is it set up to accommodate modules that would expand versatility?
“We simply cannot afford to be spending time and money, applying misguided strategies to poorly conceived and ill-defined problems.”
The Brilliantly Simple Way to Solve Any Problem … and Create the Change You Need
Pete Lindsay & Mark Bawden (2019).
Critically examining the data model we’ve pursued, you may have spotted the shortcoming that I write about on the Party data model page.
Your Support for dmw TIPS
If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —
Thanks, in anticipation.