STEP 1: Define the Purpose

Before attempting to create an MS Access database, first decide what the objective for the database is. The software is very much like a vehicle. Cars transport people from one place to another. Trucks can carry a few people and a lot of cargo. The database you create must have a assignment Y objective. It can be to track jobs, log activity, record and monitor sales, or any of a thousand other purposes.

STEP 2: Define your workflow

Most people skip this step before trying to create an MS Access database. You will regret it if you do! You have work to do and you already have steps that you are probably taking now to get the job done. You are not yet using an access database to perform these tasks. Write these processes step by step. This is your workflow. The workflow usually begins with the arrival of some type of information. That information will need to be entered into the database once it has been created. 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 approval? Write the workflow you are currently using to process that information from start to finish. This is essential before creating your Access database.

STEP 3: Identify forms and reports

Then take that workflow and go through it step by step. Mark next to each step where you will need get into gold information retrieve information. That’s really all you can do with a computer: put information in or get information outside. Write down, next to each step, in your workflow the points where you need:

  • A get into Data Form)
  • A Modify Data Form)
  • A View data on the screen (form)
  • A Print data in a report (report)

STEP 4: Design forms and reports

After marking out your workflow with forms and reports, you need to draw, on paper, what you want each form and 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 type the names of the fields you want to use or view. Draw the buttons you will need as well. Each form or report also needs a title. This will help you think through and figure out exactly what fields you need and where they should be placed. Then review your reports and make sure you know where the data for that field or column is coming from.

STEP 5: Design your tables

Now you can design your tables. Based on each of your forms, you can see which tables you’ll need. Typically, each form will have a table, and that table will contain the fields for your form. Some of those fields may come from other tables, but most of them will usually come from one table.

  1. List the tables you will need and the fields for each.
  2. Each table must have a unique “Primary Key” identifier for each record. For example; if the table is full of drivers, a “Driver ID” key will be assigned to each driver record in the table. There are no two primary key fields in the database that must have the same name.

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 put a foreign key in each of the child tables. For example, if you had a state table and a county table, you would know that each state has many counties. This is a father-son relationship. The Status table must have a Key field ID. Put this same field ID in your county table. Make sure it’s the same field type, but not a key field. The County table must have its own Key field.

STEP 7: Build your tables

After design your tables can build up your tables Microsoft included many tools to help you build the components for your database. I will not go into all the details in this article. However, the most important thing is that you build your tables prior to you build your forms.

  1. Create your tables
  2. Create your fields
  3. Set the properties of your field
  4. establish your relationships

STEP 8: Create your forms

After building your tables, you then build your forms. Follow the layout of the paper to know where your fields should be placed. You can use wizards or add them by hand. Make sure to build your tables first prior to you build your forms.

STEP 9: Create your reports

After you create your forms, enter some test data so you can see what you’re doing in your reports. Reporting 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, you will run into the problems that 95% of people have when trying to build an access database.

TEA RIGHT HOW TO CREATE AN MS ACCESS DATABASE

  1. Design of Paper Forms and Reports
  2. Design tables on paper
  3. build tables
  4. Create forms and reports

TEA WRONG HOW TO CREATE AN MS ACCESS DATABASE

  1. Start creating forms on the go
  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!