Access Database Design Process - Overview

Designing a database is both an art and a science. In our Access Database Design Bootcamp course, I teach a step-by-step process to designing a database. The class also includes a case study, where students have the opportunity to design a database in class.

Source: Access Database Design Bootcamp.

1. Gather Requirements.
   Interview the primary decision maker and key database users to determine the purpose and scope of your database. Collect sample files and documents to determine what they are currently using to accomplish database activities. In some cases, the team may be using a hodge-podge of Excel and Word files to accomplish the tasks they want the database to handle.
   
   
2. Sketch Forms & Reports.
   Sketch all forms and reports on paper and give them to the key users to approve.
   
   
3. List fields.
   Use the approved forms to list every single item you need to track in the database. These will become your database fields. Describe what each field is supposed to track.
   
   
4. Map fields to tables (on paper).
   Create a preliminary list of tables. Assign each field to a table.
   
   
5. Normalize your draft tables (on paper).
   Normalization will reduce redundancy and errors.
   
   
6. Establish relationships between tables (on paper).
   Link tables to establish 1-to-many relationships, and create linking tables to implement many-to-many relationships.
   
   
7. Create tables (in Accesss).
   Now that you've sketched your tables on paper and mapped your relationships, create your tables in Access and enter sample data.
   For more information, see our Access Module I - Tables Class.
   
   
8. Create your queries, forms and reports (in Access).
   Use the documents you've worked on thus far to create your queries, forms, and reports. For more information, see our Access Queries, and our Forms & Reports classes.
   
   
9. Test your database.
   Test your database yourself, and then present it to your key users to review and test. Repeat this process until you have a deliverable database.
   

Excel 2007 - Creating Custom Lists

In a recent Introduction to Excel 2007 training session in Atlanta, I went over one of my favorite Excel features: lists.

Excel's lists feature is an incredible time saver. If Excel recognizes certain items as a list, you do not have to enter these items. All you do is enter the first item, click and drag and Excel will fill in the rest.

Here's an example. If you enter the word "January" in a cell, and use your mouse button to drag down from the lower right hand corner of the cell, Excel will recognize this as the beginning of a list. As you can see from the diagram below, a box pops up showing the subsequent months.




When you release the mouse, Excel will fill the cells with the months
as shown below.




Excel's fill series even works if the list moves from left to right.




Anything that Excel recognizes as a series can be used in this way.

Here are some examples:

• Days of the week,
• Years,
• Quarters (ex., Qtr 1, Qtr 2, etc.).

Here's where this feature gets even more powerful - did
you know that you can create your own list to use in Excel?

Two doctors were taking my Excel training class because they were opening a medical practice in Atlanta. One list they thought would be extremely helpful would be a list of their insurance companies.

Setting up a custom list of insurance companies would provide the doctors the following benefits:

• Save time - Because they wouldn't have to keep typing the same information over and over.
• Reduce typos - Since the list is stored in one place, there is no chance of reducing the typos.
• Increase efficiency - When an insurance company is added or removed, they simply have to edit the list.

Setting up a custom list.

Enter the values for your list in a range of cells (ex., Cigna, Aetna, Kaiser). Make sure to enter the items in the list in the order you need them to appear. For example, if it's important that the names of the insurance companies are in alphabetical order, type them in alphabetical order (ex., Aetna, Cigna, Kaiser).

Select the range of cells.




Click the Microsoft Office button.




When the menu expands, click the Excel Options button.




Click the Popular category.

Under Top Options for working with Excel, click Edit Custom Lists.





In the Custom Lists dialog box, you will notice the cell reference of the items that you selected is displayed in the Import list from cells box.




Click Import to add items in the list that you selected to the List Entries
box.




Click OK twice.


Now you can type any word in the list, click and drag, and Excel will fill in the rest.

Excel 2007 - Control Enter Key Action

In a previous post,I showed how to change which cell is selected after your hit your Enter key in Excel 2003.



Here are the instructions for Excel 2007.



Click the Office Button in the upper left hand corner of your screen.



In the bottom right hand corner of the screen that pops up, click Excel Options.



Click the Advanced link on the left hand side of the box.



Under Editing Options, make sure a check mark is next to After
pressing Enter, move selection.



Next to Direction, select Right.



Click OK.