Building A Microsoft Access Database

Where do I Start?

A general rule for creating a database (or a database feature) is to envision the minimum solution needed to satisfy the goal.   Keeping it simple applies especially to beginners.   If you want to build a system for Tracking Sales Leads, don’t add in building a Customer Service database or Billing System at the same time.   Then, once your new application is working well, you can add new features and integrate other business aspects into the design.   

Before you start, it is good practice to plan out your development so you can always envision your goals and avoid unnecessary hurdles.    Getting your team members involved will also help to define the project as well as increase buy-in to use of a new system.  

MS Access database designs should always begin with some simple concepts related to your organization:

1. Define the Goals

What challenge(s) will the database resolve? 

• How will the organization benefit?   i.e., streamlining process, enforcing strict paths of work, single source recordkeeping
• How will the users benefit?   i.e., organized data, quicker retrieval of data, less redundant data entry
• How will the customers benefit? i.e., comprehensive/consistent reporting, faster customer service, better tracking of history

2. Define the Workflows on Paper

• Write down the steps necessary in your ideal workflow.  For example, in a system for Tracking Sales Leads, write down the full list of the ideal steps from a sales lead all the way through to closing a sale.  There may be exceptions to the ideal path that need to be addressed.  It is often a good idea to address these exceptions once the new system is proven to work well for a majority of the cases. 

3. Design Tables and Data Fields on paper

• Tables are the building blocks of the database and should include all the data you are hoping to manage in your new system.  Typically, all the data points that are included in your forms and reports will be represented in a field within a table.  In a Sales Lead Tracking database, you might include tables and fields such as:

1. 1. 1. Organization – Including Organization Name, Address, Address2, City, State, Zip, Phone, etc.
      2. Contact – Including First Name, Last Name, Phone, Email, etc.
      3. Lead Source – Including Source Name, etc.
      4. Lead Status – Including Status, etc.
      5. Lead Details – Date of Lead, Product or Service of Interest, Notes, etc.
      6. Team Member – Initials, First Name, Last Name, etc.

4. Design Forms and Reports on Paper

• Your forms and reports are going to be your gateways to putting data into the system, managing the data that is in the system and pulling data out of the system.  Gather all the applicable forms and reports that you already use or sketch up the ones you will need.  Ensure that all your essential data is captured on these documents.

In our next article, we will discuss how to create your tables in MS Access.