How to Create a Contacts Database in Microsoft® Access
How to structure Microsoft Access tables for an contacts management database that also acts as a module to include in your other databases that need to track contacts.
Please consider critically the data model proposed in this article. It may be satisfactory for what you have in mind for your database, but before adopting it as is, see Possible Shortcomings of This Data Model.
If you haven’t given data modelling any consideration, then Data Modeling in Practice might be worth a read before starting your contacts database.
Last updated on 2021-03-24 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’ve seen, I considered none was fully fit for what I wanted.
If you construct a contacts DB 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 article.
Before launching into its construction, I recommend you allot time for considering what purpose your database 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 database.
Risks Caused by Not Planning
People who have asked for help admitted that when they created their databases they had not give enough thought to its foundations — to the 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 databases lead to subsequent fiddling that wasted more time. Time that always was going to be wasted because no amount of fiddling would sure up 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 to a 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 database 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 database 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 database 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 database 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 database. 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 database 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 database — 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 database came with the table on the left and for a Customer Service database 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, e.g. 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.
So why the prefix Current to Firstname, Lastname and Initials? That’s because we’re building in provision for tracking name changes should our database need to provide for this in future.
Supplementary, fixed information about an individual, e.g. date of birth and NI number, would require additional fields in tblPerson.
Before you adopt these tables for your database, take time to consider if they satisfy requirements in all respects (see Shortcomings of This Data Model).
Organisations and Their People
Some, if not all, of the people in your database 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 database 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 database that allows for expansion in the future and for importing into any database 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 database 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 database 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 database 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.
Possible Shortcomings of This Data Model
We’ve planned tables and relationships for people and organisations in 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?
My thanks to WN for his comment:
“FANTASTIC ARTICLE !!!
I would love to know how you would handle the phone numbers and what the shortcoming is... ”
Critically examining the data model we’ve pursued, you may have spotted the shortcoming that I write about on the How to Apply the Party Data Model to Access Contacts Databases page.
Your Support for DMW TIPS
Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
If you’d like an invoice to account for your donation, let me know how much you’re donating —
Thanks, in anticipation.