Search: Go

Getting the most out of MS Access Part 3: Designing with Wizards

In my last paper, Getting the most out of MS Access Part 2 – Planning Your Database, I discussed the necessary steps for planning your database setup and design. The final step was “Implement the design”. Access provides many tools and wizards that make it easy to do this. In fact, there are wizards to guide you through each task you will need to accomplish.

Creating Tables

To create a table with a wizard, select the “Create table by using a wizard” option from the tables tab in your Access database. There area a variety of samples tables to choose from in two categories: Business and Personal. Each table has a list of fields that would be appropriate to include.

To create your own custom table, you can select some or all of these fields. You can even choose to include a field twice. For example, in the Mailing List table, there is only one field for address. If you wished to include two lines of address, you could select the address field twice. The fields would be named “Address and Address1” by default. However, the wizard allows you to change the name of any selected field. Click on “Address” and then the “Rename Field…” button to change the name of the field to “Address2”.

If you already have other tables in your database, the wizard will allow you to specify how the new table is related to the existing tables and will automatically create a field in the child table of the foreign key if you did not already include it. Finally, the wizard will give you the option of automatically generating a data entry form for your new table.

Creating Queries

You can use the Query Wizard to create a query that includes one or more tables. To begin, select “Create query by using wizard” on the queries tab. Select the first table you want to include fields from and then the fields themselves. If you are creating a multi-table query, select the next table. If the tables are not related, you will be prompted to create the relationship before you can continue. Continue selecting tables and fields until you have all the fields for your query.

The Query Wizard also allows you to create “Summary” queries. These are queries that provide counts or totals, such as the total number of sales for each region, the maximum score on a series of tests, or the number of products per supplier. To create a query that shows how many contacts each customer has, you would select the CompanyName field from the Customer Table and the ContactID field from the contacts table. The next screen gives you the opportunity to select a Detail or Summary Table. Selecting “Summary” makes the “Summary Options” button available to you. Clicking this button brings up a screen with a list of fields that can be totaled. In this case, you would simply click the checkbox titled “Count Record in Contacts”.

Creating Forms

In order to simplify the data entry process, you’ll want to create forms for your new tables. If you didn’t create them automatically when you created the table, you can use the Form Wizard to do it at any time. The Form Wizard gives you more options for your form design than the Table Wizard.

To start the wizard, select “Create form by using wizard” on the forms tab in your database. Select an existing table from list and the fields you wish to have displayed on the form. Like the Query Wizard, you can select fields from more than one table.

Once you have selected all your fields, select the type of layout you want. “Columnar” will place the fields from top to bottom. Data entry forms are often columnar. If you plan to display one record at a time, this may be the best choice. “Tabular” will place the fields side by side on the form. This is a good choice if you plan to display several record at one time in continuous forms. “Datasheet” will create a form that looks just like a table (or a spreadsheet). “Justified” is also a common choice for data entry forms. A justified form often resembles a paper form.

Finally, select the style you want for your form. The style will dictate the form’s background color or image, the font color and the appearance of the entry blocks.

Creating Reports

Reports can also be created using wizards. To begin, select “Create report by using wizard” from the reports tab. Select the fields you want form one or more tables. You can specify which fields should be grouped together. For example if you are creating a list of contacts you may wish to have them grouped by Company and only list the company name once for each group of contacts. To do this, you would select CompanyID (or Name) as a grouping level. Within the group, you could have sub groups if you wanted. For example, you could specify “State” as a primary group and “City” as a secondary group. Grouped fields are automatically sorted numerically or alphabetically. Fields that are not grouped can also be sorted within their group. For example, you might wish to group your contacts by company. You can sort the contacts by last name and first name within the company.

The next step is to select a layout for your report. Do your want your left margins flush or do you want sub categories indented? Once you have determined the layout you will select a style. As in the Form Wizard, the style you choose will determine the color scheme and fonts used on your report.

As you can see, wizards can help you to quickly and easily implement much, if not all, of your database design. They allow you to perform complex tasks quickly and with only a basic knowledge of Access. In some cases, they may be all you need. However to truly get the most out of Access, you will sometimes need to “get under the hood” and customize your database elements. In our next article, we will discuss table design and how to protect your data integrity by using the tools Access provides.

Part 4

Colleen Emerick
Application Developer