web analytics

UNDERSTANDING THE DATABASE DESIGN IN MICROSOFT ACCESS 2010 – PART 2

Microsoft Access 2010

This is PART 2 of the lesson of explaining the database design in Microsoft Access 2010.

First look at the reports people are doing now and ask:

  • Which ones they want to keep?
  • Which they don’t need?
  • and what they would like to have?

Next, lay out the reports as people would like to see that you can change the report layout later, but it’s important to see what data you will need. After you have developed your reports make a list of the types of data in the reports. For example, some reports might have customer information including first name, last name and address. In your lists note which reports contain each type of data.

The customer’s first name might be used in the customer list report, the invoices report and a holiday form letter. After you have listed all of the data in your reports, group the data into categories, such as:

  • customer data
  • sales data
  • and product data

During the table design phase, determine:

  • Which tables you need?
  • What fields will be in each table?
  • and what type of data you will store in each field, text, numbers, dates and graphics and so on.

The data lists that you created from the reports become the basis for designing your tables. Make it easy to see which tables you will need and what data should be in each.

In designing your tables, break the data down into the smallest peaces possible, so that you can use the pieces anyway you want. For example if the customer information is broken into title, first name, last name, company name and so on, you can create a form letter with a salutation to Mr. Smith. You won’t be able to do that if the first and last names are in the same field. After you decide which tables you need, you can use the reports that you designed to determine how tables should be related so you can get the information you need.

For example you might need to relate your customers table with your order table, so that you can get a report of a customer’s orders in your table design. Last you will want to indicate how your tables will be related. After you have determined the tables and fields, finally you are ready to decide how you want to enter your data. In some cases you might want a form to mimic the paper form that the user gets the data from. In other cases yellow modify the form to make data entry easier. Sometimes it might be easier if the user sees one record at a time on the screen. Other times it might help to see many records out of time in a row and column.

Database design may appear to be a daunting task and it can be if you have an extremely complicated data storage requirement but, if you take the time to design your database before you created you’ll find that the finished product is both efficient and effective. Studying the templates and examples that come with access is a good way to learn more about table structure forms in reports.

If you want to learn what are the elements of the Access working screen, click here.

Leave a Reply