How to Create a Contacts Database in Microsoft® Access

How to structure Microsoft Access tables for a contacts management database that also acts as a module to include in your other databases that need to include information about contacts.

On this page:

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.

Last updated on 2024-05-01.

Initial Considerations

Before launching into its construction, I recommend you take time considering what purpose your database is to serve and, were it to prove a triumph, what development it might benefit from in the future. This planning process is usually referred to by database professionals as data modelling.

If you haven't given data modelling any consideration, then Data modelling in Practice is worth a read before starting your contacts database.

Risks Caused by Not Data Modelling

CW // This section may contain content that you may find harrowing.

People who've asked for help admitted that when they created their databases they had not given enough thought to its foundations. Not one of those people claimed to have completed any 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, once these went live, lead to subsequent fiddling that wasted more time — time that always was going to be wasted because no amount of fiddling would provide workarounds for flaws in table structures and relationships.

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.

As a result of revisions to tables, there was the stress of re-designing queries, forms, reports and code. Inevitably all this caused interruption to business.

Types of Contacts

In my view there are two types of contacts: people and organisations. What data uniquely identifies a person is different to the data that uniquely identifies an organisation.

Hence the need for separate data tables for people and organisations in a contacts DB. These two tables are a good starting point for the data modelling of your DB.

Beware of assigning to these tables fields for data that is, or may not be, unique to a person or to an organisation.

An address, for example, surely should not be used as part of a person's or an organisations's unique record.

Planning for People

In the UK, first name and last name are the minimum information that you need to identify a person. If your DB is for contacts in your country alone, then you can adopt a naming convention that better fits than the UK one I'm using in this article.

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.

For example. 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.

If you have contacts in different countries, then you'll need to adopt a naming convention that will accommodate regional variations. You may even need to allow for recording local variations to the names used for the same person.

This is a useful guide to some of the different styles used for personal names: https://www.w3.org/International/questions/qa-personal-names.

Planning for Organisations

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 seperate tables for each type of contact. We resist the temptation to cram everything into one table.

Organisations are nothing without their people — their employees, representatives and so on.

A data table for organisations should not itself contain information about its people. Nor should the data table containing the record of a person attempt to identify the organisation or organisations with which that person is associated.

The data structure needed to accommodate such associations are considered below.

Be Wary of 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:

People's names and addresses are crammed into the same table. 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 Greene marries and chooses to adopt her partner's family name of Brown. So what do you do? Replace the “Mary Greene” FullName with “Mary 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 — ContactRef 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 below for how to cater for addresses, 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 with a template for a Customer Service database, with the one on the right:

If you're 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're looking to your business in the future, then please get your database off on the right foot by completing your data modelling and not leaving anything to chance.

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 OrganisationName field 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 databases.

Planning for Addresses

A person has a home address. They may have a delivery address, which is different to their home address. During a pandemic, they may have an address to be used whilst they are locked down there. Then there's their regular work address.

An organisation has a head-office address. It may have regional offices, each with their own address. Its services address may be different to its billing address

People and organisations change addresses.

Your database needs to accommodate addresses in other countries.

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.

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, which is suitable for addresses in the UK alone:

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.

That makes addressing sound very simple. It is up to the point at which you need to make accommodation for foreign addresses. Then things get very challenging because of the ways addresses appear in different countries.

In the simple table to the right, the CountryID field provides for the country. But in no way does the overall design provide for the seemingly endless variations in address composition used throughout the world.

Take a look at Frank da Cruz's wonderful FRANK'S COMPULSIVE GUIDE TO POSTAL ADDRESSES to get the full flavour of the variations.

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. 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've 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 and hence shares the organisation's address as their current work address.

The structure allows you to keep track of a person's movements between organisations.

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:

• People
• Tracking people's name changes
• Organisations
• 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.

Ringing Up

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.

The tables for phone numbers follow those for email addresses detailled below.

Email Addresses

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.

Download and please consider making a contribution to the upkeep of this website.

Possible Shortcomings of This Data Model

In this article 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?

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) —

Thanks, in anticipation.