How to Create an Access Database

Designed properly a Microsoft Access database can be a wonderful tool to boost productivity.  Understanding the steps involved is the key to creating an Access Database that fits the workflow needs of your business.   Please setup a Free Consultation or give me a call.

By Joe Richardson, Owner of JustGetProductive.com

Introduction

Basically before you can create an Access database you have to design it from the top down and build it from the bottom up as you'll see in the article.  I will not get into the details of how to create an Access database in Microsoft Access, but rather outline the basic steps to avoid 95% of the pitfalls most have when trying to create an Access database.

Define the Purpose

Before you try to create an Access database first decide what the purpose for the database is.  Software is much like a vehicle.  Cars transport people from one place to another. Trucks carry few people and lots of cargo. The database you create should have a mission and purpose.  It may be to track jobs, log activity, record and monitor sales or any of thousands of puropses.

Define Your Workflow

Most people skip this step before trying to create an Access database.  You'll regret it if you do!  You have work to get done and already have steps you are probably following now to accomplish the job.  You're not yet using an access database to accomplish these tasks. Write these down in a step by step fashion. This is your workflow.  Workflow usually starts with the arrival of some type of information.  That information needs to be entered into the database once it's built.  What are you now doing with that information?  What steps are you taking once you receive that information?  Do you process it?  Does it go to someone else for approvals?  Write down the workflow you are currently using to process that information from beginning to end.  This is essential before creating your Access database.  Below is a sample...

Identify Forms & Reports

Next, take that workflow and mark on it where along the way you will need to enter information or retrieve information.  Really that's all you can do with a computer - put information in or get information out. Write down on your workflow points where you need:

• To enter data (form)
• To modify (form)
• To View data on the screen (form)
• To print data on a report(report)

 

Design Forms & Reports

Now you should draw out, on paper, what you want each form and each report to look like. You want to write the names of the fields you want on each design of each form and report.  This will help you realize exactly where the fields need to be placed.  Then, look over your reports and make sure you know where the data came from for that field or column.

Design Your Tables

Now you can design your tables.  Based on each of your forms you can see what tables you will need.  Normally each form will have a table, and that table will contain the the fields from your form.  Some of those fields may come from other tables but the bulk will come from one table. 

1. List the tables you will need and the fields for each.
2. Each table should have a unique identifier "Key" for each record.  For example; if the table is full of drivers then a driver ID Key would be assigned to each driver record in the table.

 

Discover Your Relationships

Relationships in Access are not designed, they are discovered based on your actual business  practices. Look for one-to-many or parent-child relationships.  And place a foreign key in each of the child tables.  For example, if you had a State table and a County table you know each state has many counties. This is a parent-child relationship.  The State table should have a Key field ID.  Place this same field ID in your County table.  Make sure it's the same field type, but is not a key field.  The County table should have it's own Key field.

Build Your Tables

After designing your tables you can build your tables. Microsoft included lots of tools to help you build the components for your database.  I will not get into all the details in this article.  It's most important though, that you build your tables before you build your forms. 

• Create your tables
• Create your fields
• Set your field properties
• Establish your relationships

 

Build Your Forms

After building your tables then you build your forms.  You can use wizards or do them by hand.  Make sure you build your tables first before you build your forms.

 

Build your reports

After building your forms enter some test data so you can see what you are doing on your reports.  Building reports is usually last.

 

Conclusion

As you can see see the process goes in reverse when you build your database.  If you don't design from the top down and build from the bottom up then you will run into the problems 95% of people do when they try to create an access database.

THE RIGHT WAY TO CREATE AN ACCESS DATABASE

1. Design Forms and Reports on paper
2. Design Tables on paper
3. Build Tables
4. Build forms and reports

THE WRONG WAY TO CREATE AN ACCESS DATABASE

1. Start building forms on the fly
2. Try to build the tables to work with the forms
3. Change the forms to fit the tables
4. Build the reports
5. Change the tables to fit the reports
6. Change the forms to fit the tables
7. Keep repeating the above steps until you are completely confused and frustrated