STEP 1: Define the Purpose

Before you try to create an MS 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 can carry a 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 purposes.

STEP 2: Define Your Workflow

Most people skip this step before trying to create an MS 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 processes down in a step by step fashion. This is your workflow. Workflow usually starts with the arrival of some type of information. That information will need to be entered into the database once it’s built. What are you currently doing with that information? Using spreadsheets? Using paper? 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.

STEP 3: Identify Forms & Reports

Next, take that workflow and review it step by step. Mark beside each step where 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, beside each step, on your workflow the points where you need:

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

STEP 4: Design Forms & Reports

After marking up your workflow with forms and reports you should next should draw out, on paper, what you want each form and each report to look like. This will become your database design. I suggest you use paper first. This is where you get creative and using paper is quick and simple. On each form and report you’ll want to write the names of the fields you want to use or see. Draw the buttons you will need too. Each form or report needs a title also. This will help you think and discover exactly what fields you need and where they need to be placed. Then, look over your reports and make sure you know where the data came from for that field or column.

STEP 5: 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 fields from your form. Some of those fields may come from other tables but the bulk will normally come from one table.

1. List the tables you will need and the fields for each.
2. Each table should have a unique identifier “Primary 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. No two primary key fields in the database should be named the same.

STEP 6: 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 its own Key field.

STEP 7: 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.

1. Create your tables
2. Create your fields
3. Set your field properties
4. Establish your relationships

STEP 8: Build Your Forms

After building your tables then you build your forms. Follow the paper design so you know where your fields should be placed. You can use wizards or add them by hand. Make sure you build your tables first before you build your forms.

STEP 9: 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 the last step.

Conclusion

As you can 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 MS 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 MS 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!

Joe Richardson is a professional database developer and has been building MS Access databases since 1996. He has more than 300 clients in 4 countries across varied industries.

Visit Joe’s website to learn how you can create an Access Database or ask him to build yours.