What do you need?

Before you create a database, you need to know the purpose and goal of the specific database you'll be creating. For the purpose of this course, you'll create a leads management database. Leads are prospective customers. When people call in to ask for information or some other method of referral, you want to capture that information and track it, but you don't necessarily want it in your customer database. This process can be different for different industries. You want to track things such as where leads came from. Leads can be walk-ins, phone-ins, Internet, advertisements, and more. From there, you want to track who will follow up on the lead, when they did, and then whether that lead turns into a customer. The databases we create in this course track that information and do the math to find out average time a lead takes to turn into a customer. You'll also be able to track the status of a lead, whether it's active, dead, or something else.

Think big and draw a menu of what you're trying to achieve. You may not create all these items at first, but if you have a final goal that's bigger than your immediate needs, planning is easier.

The menu, shown in Figure 2-1, helps identify purposes and goals, as well as determine logical grouping and future uses, such as:

Figure 2-1: A planned menu.

Next, create a list of the information you need. This will become your list of fields. Keep in mind that you haven't started the database yet. Some people advocate creating while you program, but in this course, we suggest that you think on paper before putting information into the database. This allows you to have a more complete database from the beginning.

Here's a short list of information you need to include for your fields:

This information seems reasonable to us, as humans, but computers require more specific information. Each piece of data has to be broken down into pieces as small as possible. So, looking at our list, here's how you can break down it down even further:

Notice in this list that there are no spaces in any of the names. Instead, I used uppercase and lowercase characters to make the field names easier to read. This helps with programming because although Microsoft Access allows you to create field names with spaces, if you need to create automation for your database later, it's easier for Access to handle names without spaces. Otherwise, you'll need to enclose all the names with spaces in quotation marks.

Good database rules

For field names, here are some general rules to follow:

For tables, here are some general rules to follow:

What else do you need?

At this point, you have a basic level of information for one table. One of the advantages to Access is that it can have multiple tables. Other tables you may need for your leads database are for the source of each lead, the regional sales people related to each lead, and notes that might be created for each lead. Think of all the possible items you might need and list them in groupings of tables. Figure 2-2 shows a possible layout of different tables and different fields. Oftentimes, there are many ways to do the same thing.

Figure 2-2: Planned tables and fields.

The next thing you need to do for the planning stage is draw your forms and start considering what reports you need. Because I'm not an artist, I created a mock data input form, as shown in Figure 2-3. Normally, you would simply draw it, as well as the previous menu and table layout, on a piece of paper. Figure 2-3 provides an example of how you'll enter your information. If there's anything on your form that is not in your table, it needs to be added here. This will help if you missed anything in the previous exercise.

Figure 2-3: Data input form.

At this point, you should also draw your reports. This will help you know what information needs to be entered and exported. Don't forget to consider summary reports.

You might notice at this point we've only discussed tables, forms, and reports. That's because they're the most straight forward. Queries tend to be supportive, as do macros and modules. You don't need queries until after our tables are firmly in place.

More on Data

As you learned in Lesson 1, the table is the heart of the database. All other objects act upon the table and the information contained within. You create a table in Lesson 3, but before you do, consider what will go in that table.

As you plan your database, you need to determine more information about your pieces of data; these pieces are called fields. Let's look at a few of the items on your field list and identify some other information about the fields, namely size (or length), type, and default value.

Types of types

For each field, you must determine important information about the field. You already know about the naming convention, so we'll move on to data types. Think of a data type like a container. The type of information you put in the field determines the data type.

The basic data types in Access are similar in all databases. The following list contains a brief description of each datatype:

TIP
See if you can complete the list of types by looking at Figure 2-2 and identifying which type each field should be set to.

This sums up basic data types. As you get more sophisticated, you can be more specific with field types. In the following section, you learn the actual mechanics of setting up datatypes.

The Access Window

It's time to take a look at the Access Window. To start Access, click Start > Programs > Microsoft Access.

If you do not see Microsoft Access, you may not have the professional version of Microsoft Office and need to purchase that to work on the examples in this course. You can follow along the course without it, but you'll learn more if you have it to work with.

Access opens and asks you to choose a blank access database, a wizard, or a file you've worked on before, as shown in Figure 2-4.

Figure 2-4: Access open window.

Select Blank Access Database, and click OK.

At this point, you need to name your database. For the purpose of this practice session, you may call it AccessIntro. By default, it will go into your My Documents files or you can save it in another location. Just remember where you saved it.

Remember that we've talked about objects, but all the objects you'll deal with in this example are contained within this one Access database file. The formal name is AccessIntro.mdb where mdb stands for Microsoft Data Base. This is the file you use throughout this course. If you go to your My Documents folder (or wherever you saved it), you'll see this file. You can double-click it as another way to open Access, rather than going through the Start menu. If you've forgotten where you saved it, you can find it on your recently used file list under the File menu in Access.

After you name your database and click Save, you'll see the basic Access window as shown in Figure 2-5.

Figure 2-5: Basic Access window.

Look at the top row of the window to see the usual menu: File, Edit, View, Insert, Tools, Window, and Help.

Below the File menu is the Standard toolbar menu. If you hold your mouse over any icon (image), a yellow box will pop up with the name of the tool in it. Some of this will be familiar to you and some will not. From left to right is New, Open, Save, Print, Print Preview, Spell Check, Cut, Copy, Paste, Format Painter, and Undo. Many of these are grayed out because they're unavailable at this time. Towards the end of the toolbar, there are some new icons you probably haven't seen before: Office Links, Analyze, Code, Properties, Relationships, New Object, and Help.

Now, down to the actual Database Window. Along the top of the database, you see the Windows title bar, which includes the name of the database. In Figure 2-5, it's AccessIntro. If you click the red X in upper-right corner of this window, it closes the database, but be sure to leave Access open. It's important to note as you add objects and have many windows open that if you ever click the X in this window, it will close all the other objects as well.

Remember that object is a generic term referring to any of the items within Access, such as tables, forms, queries, and reports. Access is made up of multiple types of objects, unlike Microsoft Word, which is just a collection of pages and unlike Microsoft Excel, which is just a collection of spreadsheets.

Below the title bar is a small toolbar related to the database. Each object also opens its own toolbar, so the toolbars will constantly look different depending on what area of Access you're in. Don't get confused, just focus on the ones you need at the time.

The first three, Open, Design, and New, simply refer to what you might want to do to any particular type of Object. Because you have an empty database, you don't have a list of objects in the window, but if you did, it would look like Figure 2-6.

Figure 2-6. List of tables.

In the list of tables, one called tblLeads is highlighted. If you click Open, Access simply opens that table and shows the data contained within. You can edit directly within the tables, which looks a great deal like Excel spreadsheets. You'll find out more about tables in Lesson 3.

The Design button allows you to change the layout and fields in the table. In Lesson 3, you'll create a table from scratch using Design view. Your New button opens the New Table dialog box from which you can simply choose Design View.

The next button (the one that looks like an X) is self explanatory and dangerous. It's the delete button. Remember, that you cannot undo a great number of things in Access. Deleting objects is one of them. If you delete an object, you cannot bring it back. If you plan on making extensive modifications, it's a good idea to make a copy of your database before making any changes. Accidents happen. Have a backup!

The next four buttons simply change how you view your data. The example in Figure 2-6 shows the List view. You can choose from Large Icons, Small Icons, List, and Details. Each has its advantages, but this is mostly a personal preference. Click each view to see how it changes the list of choices you currently have.

On the left, you'll see the list of Objects we covered earlier. For this introductory course, you only deal with the top four: Tables, Queries, Forms, and Reports. Click any of these objects; you'll see that the basic window doesn't change much, so be observant and check the object menu on the left to see which objects you're looking at.

Naming Conventions

In programming, there are standard naming conventions used for each object, so the type of object is clear at all times. This helps in many ways, not the least of which is being clear on the list of objects at which you're looking. In programming, it's imperative to be consistent or there will be errors in your programs. Access data often ends up tied to programming, so starting with good discipline in database design is important. Here are the standard naming conventions for Access Objects you'll be working with:

Notice there are no spaces. This is a good programming technique because you can use the names as is without enclosing them in quotation marks during programming.

Moving On

In this lesson, you planned your database. In Lesson 3, you'll create your first table and understand how and why it works.