MS Publisher

In this article

=> What’s new in Publisher 2010

=> Create a publication    

=> Print your publication       

=> Send your publication in mail or in e-mail

=> Getting Started - What’s new in Publisher 2010

Microsoft Publisher 2010 adds new capabilities geared to help you create, print, and distribute professional-quality publications and sales and marketing materials. With the updated user interface, an improved print experience to help with more efficient printing, new picture placement and manipulation tools, building blocks of content, and fine typography options such as true small caps, ligatures, and stylistic alternates, Publisher 2010 improves the desktop publishing experience and makes the results more predictable so that you get the publications you want.

In this article

Improved interface

Introducing the ribbon

Welcome to the Backstage

Print efficiently

Print settings and print preview

Fine typography       

Building Blocks

Building block galleries

Improved image tools

Crop

Pan

Picture style gallery

Improved interface

Publisher 2010 introduces several features to dramatically enhance how you see and work with your publications. The updated user interface that includes the Ribbon, the Backstage, and a cleaner workspace, that make it intuitive to discover more commands so you can get things done faster.

Introducing the ribbon

When you first start Publisher 2010, you may be surprised by what you see. The menus and toolbars have been replaced with the ribbon, which helps you quickly find the commands that you need to complete a task. Commands are organized in logical groups that are collected together under tabs.

For Publisher 2010, all tabs and groups on the ribbon are fully customizable. If your organization has features unique to its business, you can group them on their own ribbon tab.

Welcome to the Backstage

Click the File tab and you are in the Backstage, a one-stop graphical destination for managing your files. The Backstage contains the same basic commands available on the File menu in earlier versions of Microsoft Publisher to open, save, and print project files. For more information about the File tab and Backstage View in Office 2010, see What happened to the File menu?

            

Print efficiently

The Print command is now in the Backstage View. To get to the Backstage View, click the File button in the upper left of Publisher . Publisher 2010 combines the Print and Preview experiences to make it much easier to print both simple and complex documents. Simultaneously view both sides of a page, multiple pages, page boundaries, and other pertinent print information to get the expected print results the first time.

Print settings and print preview

The new Backstage View for printing looks like this:

On the left are the print settings and on the right the preview pane. Changes made to the setting in the left pane (of the Print Backstage) will affect both the preview and the printed result. Changes made to the setting in the preview pane (of the Print Backstage) will affect the preview, and will not affect the printed result.
Note   Some of these controls are dependent on other settings, for example the color control will only be available if your printer can print in color and the front/back transparency slider will only be available if you choose print on both sides of the paper.
For more information about printing in Publisher 2010.

Fine typography

Publisher 2010 delivers new tools that help you transform ordinary text into fine typography. Use the stylistic sets, stylistic alternates, true small caps, ligatures, number styles and more that are available in OpenType fonts such as Calibri and Gabriola. You can also use additional OpenType fonts that are available through other companies. Whether the font supports the advanced typographic features, as well as which features, is determined by the font designer. Most fonts at this time do not support advanced OpenType features.
hen you create a text box the Text Box Tools tab appears on the ribbon. If the font you have selected supports OpenType, the Typography group will be enabled and give you access to formatting features and tools including new typography features.

For more information about the Publisher 2010 typography feature,    

Building Blocks

Building blocks are reusable pieces of content such as business information, headings, calendars, borders, and advertisements that are stored in galleries. You can access and reuse the building blocks at any time. You can also create and save your own building blocks, categorize, write a description, and tag them with keywords to make them easy to find. These custom building blocks can be a combination of graphics, text, and even other building blocks.

Building block galleries

Four of the five building block galleries are found on the Insert tab, in the Building Blocks group and the fifth, Business Information, is found in the Text group.

The five galleries contain:

• Page Parts: This gallery is for pre-formatted structural elements for your publication such as headings, sidebars, and stories.
• Calendars: This gallery is for pre-formatted monthly calendars. You can select the current or next month from the gallery, or select the month and year from the gallery from the Building Block Library.
• Borders & Accents: This gallery is for graphic elements such for adding borders, emphasis, and frames.
• Advertisements: This gallery is for pre-formatted advertisement elements, including coupons.
• Business Information: This gallery contains your Business Information sets. For more information on Business Information,

Improved image tools

Add or change pictures to your publications seamlessly while preserving the look of your document. Use crop, visual pan, and picture style gallery to get the results you want.

Crop

When you select a picture in Publisher 2010 a new Picture Tools ribbon tab becomes available. The Crop tool is in the Crop group on the far right of this new tab. Cropping is often used to hide or trim a part of a picture, either for emphasis or to remove unwanted portions.

Pan

While using the crop tool you can move the picture within the cropped picture area to make sure that the picture is centered the way you want.

Picture style gallery

The Picture Styles group gives you options for your picture’s shape, border, and captions. You can apply pre-defined picture styles from a picture style gallery, or you can format the picture’s shape and border manually.

The style gallery contains four different shapes each with six different border options that you can apply to your pictures. When you hover over one of the gallery options the selected pictures will preview the style.

=> Get Started - Create a publication

You can create your publications with the professionally designed Microsoft Publisher 2010 templates, customize the templates as needed, and then reuse the templates. Do you need to create several publications for the same business? You can create different publication types that use the same options and information — such as the same color scheme and business information set.
This article will walk you through the steps to create a three-fold brochure. First, you’ll download the template from the Office Online template collection. You will then use the template to create a new publication. And lastly, you will use the Backstage View to enter some business information that you will use in your publication.

In this article

Download a template       

Create the publication       

Enter your business information       

Download a template

This publication is going to be a tri-fold brochure using a template from the Office Online template collection.

1. Click File > New and click Brochures.
2. From here, click the Business tri-fold brochure (civic) template to open it in Publisher.

Create the publication

The template contains two pages, shown in the Page Navigation pane:

The publication will be open to page one. You will see placeholder text and images, and on the first page there is placeholder business information, which you will update in a moment. First, replace the image of a pile of books on the first page with a clip art image of a shipping package:

1. Click the image of the pile of books in the left-hand pane of the first page.
2. On the Ribbon, click the Insert tab, and then select Clip Art.
3. In the Clip Art pane, in the Search for box type: boxes.
4. In the Results should be box, de-select everything except Illustrations and the select the Include Bing content check-box.
5. Press Go to start the search.
6. To insert a Clip Art image from the results list, double-click on an image such as this:
   
   Note    Choose any box image that you want, it does not have to be the image shown.

If the image doesn’t fit exactly into the space and looks like this:

You can clearly see the part of the image that fits exactly into the space that had been taken by the pile of books. The part of the image that does not fit exactly is dimmed and will not show when you print the publication. The black lines and corners framing the image are called Crop Handles and are used to crop the image.
To crop the image so that the entire image shows:

1. Move your mouse cursor over the bottom right corner crop handle, when you do you will see the cursor change into a little black corner.
2. Click and drag the cursor down to the bottom of the underlying image.
3. Then move your mouse cursor over the top right corner crop handle and click and drag the cursor to show the entire image.

You can now see the entire image, but the bottom corner of the package is below the bottom of the brochure’s left-most section.
To move the image:

1. Move your mouse cursor over the image until the cursor becomes the move pointer .
2. Drag the image up so that the entire image fits above the bottom edge of the brochure’s left-most section.

Now go ahead and replace the two images on the second page with other Clip Art images of packages. You can also replace the placeholder text in the text boxes if you like.
   

Enter your business information

You might not have anything that you think of as business information. You might be a civic or charitable newsletter, or simply creating publications for your family’s garage sale, but you will still have a set of information that you want to re-use in your publications. So, play along as we go to the Backstage View to enter some information about your organization.

1. Click the File tab (for more information on the File tab and the Backstage View).
2. In the Info tab, click Edit Business Information.
3. Enter as much information as you want in Create New Business Information Set, by clicking in the appropriate text boxes, and click Change button if you would like to change the logo image. Remember that you can go back and change this information whenever you like. For more information.
   
   Note    You can also get to this dialog from the Insert tab’s Business Information button by then selecting Edit Business Information.
4. Enter a name in the Business Information set name box, and then press Save.
5. Click Update Publication.
6. Click the File tab to return to your publication.

Now your brochure has been customized not only with images and text, but with reusable business information.

=> Get Started - Print a publication

The main use of Microsoft Publisher 2010 is to produce high-quality print publications. These may be printed on a desktop printer or sent out to a copy shop or commercial print shop. For information to help you choose between these options see About choosing between desktop, copy shop, and commercial printing.
In this article you will be printing the tri-fold brochure created in Get Started - Create a publication article on a desktop printer. If you don’t have a desktop printer you can print the publication to the Microsoft XPS Document Writer, or you can install the Generic PS printer driver. The goal is to print the brochure on both sides of a single sheet of paper.
Note    The preview window may display in black and white even if your publication(s) includes color if the selected printer does not support color printing, or if the selected printer supports color and you selected Composite Grayscale setting.

In this article

Where is the print command?       

Print settings and print preview       

Configuring your print settings and printing       

Where is the print command?

In Publisher 2010, the print command is now in the Backstage View. To get to the Backstage View, click the File tab in the upper left of Publisher. In addition to printing, the Backstage View contains commands for saving, opening, and closing files, as well as information about the current publication and sharing the publication, and Publisher options. You can add the Print and the Print Preview commands to the Quick Access Toolbar. For information, please see Customize the Quick Access Toolbar. You can also get to the print command by pressing CTRL+P.
   

Print settings and print preview

The new Backstage View for printing looks like this:

On the left are the print settings and on the right the preview pane. Changes made to the setting in the left pane (of the Print Back stage) will affect both the preview and the printed result. Changes made to the setting in the preview pane (of the Print Back stage) will affect the preview, and will not affect the printed result.
Note    Some of these controls are dependent on other settings, for example the color control will only be available if your printer can print in color and the front/back transparency slider will only be available if you choose print on both sides of the paper.
The print settings allow you to configure:

1. Print:     Enter the number of copies of the print job you want to print and press Print to send the job to your printer.
2. Printer:     In addition to selecting from a list of available printers, you can also add a new printer, access the Advanced Output Settings, and print to a file.
3. Settings:     The settings section gives you control over:

• Pages:     Allows you to select specific pages or page ranges to print.
• Print imposition:     Here you can specify the arrangement of the Publisher pages on the printer’s sheet of paper. For example, you can impose multiple copies of one page on a sheet, or you might impose multiple different pages on a sheet.
• Paper size:     Select the size and style of the sheets of paper from those supported by the selected printer.
• One sided/two sided printing:     Choose whether to print on one or both sides of the paper, and which edge to use in flipping the paper.
  Note    If the printer does not support duplex printing "Manual 2 sided print" will be displayed. This is the case also for the XPS printer driver. After the user presses the print button the printer setup wizard will guide the user on how to print duplex manually.
• Color:     If your printer supports color this control will allow you to choose color or grayscale printing.
• Save settings with publication:     If selected this check box will save your Settings selections with this publication.

Note   In Office Publisher, page size refers to a working area of your publication. Paper, or sheet, size is the size of the paper that you use for printing. A sheet of paper may contain multiple pages, and a large page may be imposed across multiple sheets.
The preview pane changes to reflect changes made in the print settings. It also allows you to:

1. Sheet navigation:     Move through the sheets of your publication.
2. Front and Back buttons:     These buttons are available when printing on both sides of the sheet of paper, click to view the front or back of the sheet.
3. Zoom slider:     Slide to the left to zoom out and slide to the right to zoom in on your publication.
4. Fit to sheet:     If you are viewing more than one sheet this button will zoom to view one sheet.
5. View multiple sheets:     If you publication will be printed on multiple sheets of paper you can use this button to preview more than one sheet at a time.
6. Show/hide page numbers:     This slider will show you the order of the pages being imposed on the sheet(s) of paper. This is particularly useful when you are printing a publication with more than one page on the sheet, like a greeting card.
7. Show/hide the rulers:    Shows or hides the rulers for the height and width of the currently selected sheet of paper. If you change the paper size in Settings, the ruler will change accordingly.
8. Transparent view slider:     If you are printing on two sides of the sheet, this slider will allow you to see through to the other side of the sheet, like holding the printed paper over a light table. This allows you to be sure that your publication lines up correctly on both sides of the sheet of paper.

  

Configuring your print settings and printing

To configure the print settings for your publication:

1. Click the File tab to open the Backstage View.
2. Click Print to open the Print Settings page.
   Note    You may have to wait a moment while Publisher connects to your printer.
3. In the Printer section, make sure that the correct printer is selected.
   Note    The preview window will display in black and white, regardless of whether your publication(s) includes color, unless you have selected a color printer.
4. In the Print What section, make sure that All is selected.
5. In the Other Settings section, make sure that the publication is set to print One page per sheet, Letter, and change the setting from Print One Sided to Print on Both Sides / Flip on long edge.

Take a moment now to notice a change in the print preview pane on the right. When you changed the publication to print on both sides of the sheet of paper a new slider appeared in the upper right of the application:

This slider controls the transparency of the print preview, like putting your printed publication on a light table so that you can see through the paper. This allows you to see if the front and back line up the way you want them. Play with the slider a little, sliding it from left to right, from no transparency to fully transparent. As you do you will notice that with the current setting of flipping on the long edge the page on the back of the sheet is upside down.
To fix this:

• In the Other Settings section, change Print on Both Sides / Flip on long edgetoPrint on Both Sides / Flip on short edge.

Finally, to print your publication click the Print button.

=> Create a mail or e-mail merge in Publisher

You can use mail or email merge when you want to create a large number of documents that are mostly identical but include some unique information. For example, you can use mail merge to create individually customized publications with personalized notes or unique addresses and salutations. You also can use mail merge to create a product announcement that you want to customize before sending to specific people. The text of the publication is always the same, but the name and address are different for each recipient.

In this article

Choose Mail Merge or Email Merge       

Create or connect to the recipient list       

Prepare your publication       

Create the merged publication

Cancel a merge       

Choose Mail Merge or Email Merge

The first thing to do is choose if you are creating a mail merge for printing or an email merge for online distribution.

• Click the Mailings tab and choose either Mail Merge or E-Mail Merge.

Create or connect to the recipient list

To perform a mail merge, you'll need to open a new or existing publication and then connect to a data source (a file containing the unique information that you want to include). A data file may contain a list of names and addresses, product data, or pictures. The data file can be in a variety of formats, including:

• Outlook Contacts list
• Excel worksheets
• Word tables
• Access database tables
• Text files in which tabs or commas separate the columns, and paragraph returns separate the rows

You can connect to an existing list or data source, or you can create a new recipient list. If you want to use only certain entries in your list, you can filter your list by a specific criterion. You can also sort the items in alphabetical order.
Note   If you want to merge pictures into your publication pages, your data source must include either file names or paths for the picture files that you want to merge. Don't include the actual pictures or images in your data source.

Use an existing list

1. Click Mailings > Select Recipients > Use Existing List.
   By default, Publisher stores data sources in the My Data Sources folder. You may need to browse to locate your data source.
2. In the Select Data Source dialog box, click the data source that you want, and click Open.
   Depending on the type of data source that you select, other dialog boxes may appear requesting specific information. For example, if your data source is an Excel workbook with info on multiple worksheets, you'll need to select the worksheet containing the info you want.

Select from Outlook Contacts

1. Click Mailings > Select Recipients > Select from Outlook Contacts.
   If you're prompted to choose a mail profile, click the profile that you want, and then click OK.
2. In the Select Contacts dialog box, click the contact list that you want, and then click OK.
   All of the contacts in the folder appear in the Mail Merge Recipients dialog box, where you can filter and sort the list of recipients to include in the merge.

Type a new list

If you don't have an existing list to connect to, you can create a new list.

1. Click Mailings > Select Recipients > Type New List.
2. In the New Address List dialog box, type the info for the first entry in the fields relevant for your mailing.
   Note    To change the default columns, click Customize Columns and add, delete, rename, and reorder the columns in the list.
3. When you've finished entering info for the first entry, click New Entry.
4. Repeat step 2 and step 3 until you have finished adding entries, and then click OK.
5. In the Save Address List dialog box, type a name for the address list in the File name box, and save it.
   Note   By default, Publisher saves address lists in the My Data Sources folder. It's best to keep the address list here because this is also the default folder where Publisher looks for data sources.
   All of the contacts in your new list appear in the Mail Merge Recipients dialog box, where you can filter and sort the list of recipients to include in the merge.

Select recipients

In the Mail Merge Recipients dialog box, you can select the recipients that you want to include in the merge. Select the check boxes next to the recipients that you want to include, and clear the check boxes next to the recipients that you want to exclude.
If you want to use only certain entries in your list, you can filter your list by a specific field or criterion. After you filter the list, you can use the check boxes to include and exclude records.
Filter items in the list:

1. Click the arrow next to the column heading of the item you want to filter by.
2. Click any of the following:
   
   • (Blanks) displays all the records in which the corresponding field is blank.
   • (Nonblanks) displays all the records in which the corresponding field contains information.
   • (Advanced) opens the Filter and Sort dialog box, which you can use to filter on multiple criteria. You also can click Filter under Refine recipient list in the Mail Merge Recipients dialog box to open the Filter and Sort dialog box.
   • To quickly select or clear all items, select or clear the check box column heading.
     Tip   If your data source contains records that share the same information, and there are ten or fewer unique values in the column, you can filter by specific information. For example, if there are multiple addresses that list Australia as the country/region, you can filter on Australia.
     The Mail Merge Recipients dialog box displays only the designated records. To display all the records again, click (All).

If you want to see items in alphabetical order, you can sort the items in your list.
Sort items in the list:

• In the Mail Merge Recipients dialog box, click the column heading of the item you want to sort by. For example, if you want to display the list alphabetically by last name, click the Last Name column heading.
• To sort using multiple criteria, in the Mail Merge Recipients dialog box, click Sort. In the Filter and Sort dialog box that appears, select the criteria you want to sort by.

Prepare your publication

A mail merge creates many documents from a single template using placeholder info and unique information that is added to the placeholder info on each document. After getting the recipient list together, you can get the main template ready.

Insert a text box

1. Click Insert > Draw Text Box .
2. In your publication, point to where you want one corner of the text box to appear, and then drag diagonally until you have the text box size you want.

Add the text you want in every version

• Click inside the text box, and then type the text you want to appear in every version of your mail merge publication.

Add data fields to your publication

1. In your mail merge publication, click inside the text box where you want to insert the data field.
2. Insert any of the following:
   
   • Address block with name, address, and other information:
     
     1. In the Write & Insert Fields group, click Address block.
     2. In the Insert Address Block dialog box, click the address elements that you want to include, and then click OK.
        Note   If the names of the data fields in your data source don't match the names of the fields that Publisher uses for the address block, you may need to click Match Fields in the Insert Address Block dialog box. In the Match Fields dialog box, use the drop-down lists to select the fields from your data source that correspond to the Publisher fields.
   • Greeting line
     
     1. In the Write & Insert Fields group, click Greeting line.
     2. In the Greeting Line dialog box, select the greeting line format, which includes the salutation, name format, and following punctuation.
     3. Select the that text you want to appear in cases where Publisher cannot interpret the recipient's name; for example, when the data source contains no first or last name for a recipient, but only a company name, and click OK.
        Note    If the names of the data fields in your data source don't match the names of the fields that Publisher uses for the greeting line, you may need to click Match Fields in the Greeting Line dialog box. In the Match Fields dialog box, use the drop-down lists to select the fields from your data source that correspond to the Publisher fields.
   • Picture
     
     1. In the Write & Insert Fields group, click Picture, and then click Insert Picture Field.
     2. In the Insert Picture Field dialog, select the picture field to insert.
     3. Note   If you want to merge pictures into your publication pages, your data source must include either file names or paths for the picture files that you want to merge. Do not include the actual pictures or images in your data source.
   • Individual fields of information
     
     • In the Write & Insert Fields group, select Insert Merge Field, and click the fields that you want to include in the text box.

Format the data fields

You can apply formats to the data fields and any other text you've added (such as a greeting of Hello or a salutation like Dear...) to change the appearance of the merged data. To format the merged data, you'll need to format the data fields in your mail merge publication.
In your mail merge publication, select the field containing the information that you want to format.

• If the field is an Address Block or Greeting Line, in the Write & Insert Fields group select Format.
• If the field is an individual data field, on the Text Box Tools Format menu, click Font, and then select the options that you want.
• If the field selected is recognized by Publisher as being a number, currency, date or time, click Format and change the formatting of how those kinds of fields are displayed.

Preview recipient data in the data fields in your publication

You can review how your publication will appear with actual data in the merged fields.
To preview your publication, click Preview Results, and then do any of the following:

• To preview entries in order, click the navigation buttons to see how each entry will appear in your merged publication.
  The information from the first record of your data source populates the merge fields. You cannot edit your data source entries on your publication pages, but you can format, move, or delete data fields there.
• To find and preview a specific entry in your data source, click Find a recipient, and then enter the search criteria in the Find Entry dialog box.

If you need to, you can make changes to your recipient list. Do any of the following:

• To exclude a particular recipient from the merge, click Exclude this recipient.
• To change the list of recipients, click Edit recipient list, and then make your changes in the Mail Merge Recipients dialog box.

After you complete your mail merge publication and insert all of the merge fields, File > click Save As, name your publication, and click Save.
    

Create the merged publication

To create the merged publication, click the Finish & Merge button and follow the directions below to print, preview, save it, add the merged pages to another publication, or send them in email.

Print your merged publication

1. Click Merge to Printer.
2. In the Print space, select the options that you want, and then click OK.
   Important   To print different sets of merge data—for example, different names and addresses—on each sheet of paper, click Multiple pages per sheet in the Printing options section. If you choose Multiple copies per sheet, each sheet of paper will contain multiple copies of the same data.
   

Save the merged publication for later

If you want to edit your merged pages, save them for later use, or print them at a later time, you can collect and save all of your merged pages as a single new merged publication that contains the actual data instead of merge fields.

1. Click Merge to New Publication.
2. In your new publication, in the Mail Merge task pane, click Save this publication. Name your new publication, and click Save.

Add the merged pages to an existing publication

If you plan to add your merged pages to the end of an existing publication, make sure that your mail merge publication matches the existing publication in the following ways:

• Page size (height and width)
• Page view (one-page spread or two-page spread)
• Publication type (web or print)
• Page order (left-to-right or right-to-left)

Publisher adds merged pages to a new or existing publication but can't add them to an open publication. If a publication that you want to merge to is open, be sure to save and close it before you complete the merge.

1. Click Add to Existing Publication.
2. In the Open Publication dialog box, locate the publication to which you want to add the merged pages, and then click Open.
3. In the existing publication, click Save this publication in the Mail Merge task pane to save your updates.

Send email messages

If you are creating an email merge the only option available under Finish & Merge will be Send Email Message.

1. Click Send Email Message.
2. In the Merge to Email dialog box, select the recipient field from the To drop-down list. In most cases this will be Email Address.
3. Under Subject, type the subject line. Under Items to insert, click any data fields that you want to insert in the subject line.
4. Click Options to specify any other options that you want, such as Cc or Bcc recipients or message attachments, and click Send.

Save the mail merge publication

If you also save the publication containing the merged fields, you can go back and edit the fields and create a new merged publication. To save your merged publication, do the following:

1. Click File > Save.
2. Give the publication a name, and click Save.

Cancel a merge

You can cancel a merge to disconnect a mail merge publication or a catalog merge template from its data source.

1. Click Mailings tab > Select Recipients, and then click Cancel Merge.
   
2. When asked if you want to cancel the merge, click Yes.
   Note   If you cancel a mail merge, fields other than Address Fields, Greeting Lines, and Address Blocks are converted to regular text.

MS Access

Getting started with Microsoft Access
This tutorial is neither complete nor comprehensive, but does illustrate some of the basic skills you need to know to construct a Microsoft Access application like the one you'll do for Assignment #1. The tutorial concentrates on the 'hows', not the 'whats' nor the 'whys'.  The latter are the focus of the course as a whole.

The tutorial contains quite a few screen capture images to illustrate what the tool looks like during various activities.  Please be forewarned that some of the segments could take a while to upload over slow communications links.  Hopefully the wait will be worth it. The tutorial is broken down into five sections:

1. Creating a Database 
2. Creating Forms   
   • Creating a combo box   
3. Creating Queries 
4. Buttons and Switchboards 
5. Macros

Create the Database

1. Since you'll be creating a new database, when you've started Access, click on the Blank Access Database option.  Then click 'OK'.
   
2. To create a new database you must provide a name and specify a directory for the database (.mdb) file.  Then, click on Create.
   

Create Tables

1. When the database has been created, it initially exists as a file, an empty shell.  The database becomes useful when we create so-called database objects within it.   In Access, the starting point for creating database tables, queries, forms, reports, macros, and modules is known as the 'Database Window', shown here.  To create a particular object, click on the tab for that object on the left (here, 'Tables').
   
   Access provides three options for creating a new table.  Create table by using wizard allows one to create a variety of common databases (e.g. customer, employee, order, etc.).   Create table by entering data allows one to quickly create a tabular arrangement of data without first creating the table's design.  The third, Create table in Design view provides the greatest amount of control over the design of the table.
   
   To create a table in design view, do one of the following:
   
   1. Double-click on Create table in Design view, or
   2. Click on New, and then select Design View in the New Table dialog box:
      
2. The design of a table requires the specification of four elements:
   1. The name of each column
   2. The data type of each column
   3. The column(s) whose value(s) will be unique for each row in the table (the Primary key)
   4. The name of the table.
3. Type in the Field Name, Data Type, and description for each field you wish to have in your table.  Notice that for each data type there are additional Field Properties you may set.  In the figure below, the CD_Category field is marked as the current one (by the black triangle on the left).  Therefore, the Field Properties pertain to the CD_Category field.
   
   In the Field Properties area we see that the following properties have been set:
   • The maximum length of the field is 30 characters.
   • The Caption property allows you to define a default label for this column on a Form.
   • The Validation Rule property allows you to define more precisely restrictions (constraints) on the values that may be inserted into this column. For example, to reduce variations in spelling, we are requiring that any value placed into the CD_Category column be spelled as one of the following options: Rock and Roll, Country, Jazz, Classical, Other.
   • The Validation Text property allows you to define an error message the user will receive should he or she enter in a value that is not valid according to the Validation Rule property.
   You should set the field properties appropriately for the other fields as well.
   
4. Choose one or more fields to be the Primary Key. You do this by clicking in the grey area to the left of your field definitions (holding the CTRL key and clicking selects individual fields if the Primary Key is to consist of more than one column.
5. From the Edit menu, choose Set Primary Key
6. From the File menu, choose “Save As...” and give your table a name.
7. Close the table by either choosing “Close” from the file menu, or clicking on the Close button in the upper right-hand corner of you window.
   
8. Repeat this table creation process to create another table called TRACKS with the following columns:
   • Track_No (Data type:  Number,  Field size: Integer)
   • Track_Title (Data type: Text, Field size: 80)
   • Track_Duration (Data type: Date/Time,  Format: Short time)
   • CD_ID (Data type: Number, Field size:  Long Integer).    CD_ID in the TRACKS table is what is known as a Foreign Key.  It is a column (or columns) within one table that is also a Primary Key column(s) in another table.   Within Access it is important to note that if the datatype of a Primary Key is Autonumber, then the data type of any Foreign Key that references that Primary Key must be Number, and the Field size must be Long integer.

Create Relationships

1. Fundamentally, relationships within a relational database systems are represented by using Foreign Keys to establish associations between data in one table with data in another.   However, Microsoft Access likes to have a little bit more explicit information about what columns are foreign key columns, so that it can make life easier for you when you create forms, and so that it can better enforce data integrity within the database.  You provide this information to Access via the Relationships... view.  
2. Get a graphical view of the tables and relationships by clicking on the “relationships” button on the toolbar, or by selecting “Relationships...” from the Tools menu.
   
3. In the Show Table window, click on the Add button (for each table) to place a graphical representation of the table into the relationships diagram.  Click on Close when finished to dismiss the Show Table window.
   
4. To tell Access that a particular field in one table represents a field from another table, click and drag from the field in one table to its counterpart in another field.  In the following image, I clicked on the CD_ID of the CD table, and dragged it to the CD_Id field of the Tracks table.  Access then offers me the opportunity to edit that relationship.
   
   The relationship between the CD table and the Tracks table is a one-to-many, since one CD is associated with (contains) many tracks, while each track is located on precisely one CD.  Checking the box to Enforce Referential Integrity means that you may not enter a Track whose CD_ID value does not match one of the existing CDs.  In other words, each Track must be associated with a known CD.   Click Create to complete the creation of this relationship.
5. When the "relationship" is created, Access will show a line between the participating columns.  Note that this graphical representation is simply a convenience.  The actual relationship is established by the data values within the tables themselves.
   

Simple Forms

1. From the Database Window, select the “Forms” tab.
2. Click on “New”
3. I usually prefer to let the Access wizard create a basic form for me.  Then I can modify it as I please. So, choose the Form Wizard.  Also, pick the table whose contents we wish to display in the form (CD, in this case).
   
   Figure 1: Choosing the Source of Data for a new Form
   Click OK to go to the next screen.
4. Access will lead you through dialog boxes that ask you:
   • what fields you'd like to see on the form
   • what layout you'd like to use
   • what background style you'd like to see
   • what title you'd like to see appear on the form
   To choose the fields you'd like on the from, click on a field in the the left-hand column, then on the arrow to move that field into the right-hand column:
   
   Figure 2: Choosing fields to put on a form.
   
   When you've chosen the fields you want on the form, click Next>
   
5. The Form Wizard will then ask you to choose a basic layout for your fields.  In this tutorial, stick with Columnar, but feel free to take a look at the others as well.
   
   Figure 3: Choosing the Form layout.
   
   Click Next when you are ready to move on.
   
6. The Style of a Form refers to the background pattern, the color and font of labels, etc.  Access has a number of standard styles to choose from.   Here we've chosen SandStone, for no particular reason.
   
   Figure 4: Choosing the Form style.
   
   Click on Next to continue.
   
7. Finally, provide a title for your Form, which will show up both in the title bar and on the form.   At this point you may see the completed form, or choose to modify it further before opening it.
   
   Figure 5: Providing a Title for your form.
   
   Click on Finish.
   
8. Once you’ve created a form (say, using the wizard), you are free to alter it. Clicking on controls will select the control.  The selected control will have black boxes (handles) around the edge:
   
   Figure 6: Selecting controls on a form.
   
   Double-clicking on a selected control will show you the properties of that control (another way to see the properties is to select the control, and then choose “Properties” from the “View” menu). You can change things like the color, the borders, and many, many other properties. Experiment.
   
   Figure 7: Control properties.
   
   
9. There are a number of ways you can look at a form:  in Design View (where you can make changes), in Form View (where you see what the form will look like to the user) and in Datasheet View (where the form looks like a table).  Choose your view from either the View menu, or by clicking on the icon that looks like this:
   
   Figure 8: Form views
   
10. To add additional controls onto the form, use the toolbox, shown to your right.
    1. If you want to create a control that asks the user to select from a list of items rather than type in a (possibly incorrect) entry, use a combo box or a list box.  Be sure the wizard is activated.  It will make life a lot easier.  The wizard will guide through the choices you need to make.  If you've created a list or combo box to replace an existing control (field) on the form, you will have to delete the old control.  On the form, the combo box control looks like this:
       
       Figure 9: Combo boxes.
       
    2. If you wish for more information on adding controls, see some examples.
11. If you want to add a control to the form that is based on some field in the underlying table (e.g. a text box), it is best to: a) bring up the field list (it’s in the “View” menu)
    
    Figure 10: The Field list.
    
    and b) drag and drop the field you want onto the form.
    
    Figure 11: Adding a field to a form from the field list.

Forms with data from more than one table.

A very common type of form is one that displays so-called "Master-detail" data.  Every day examples of such displays include your telephone bill, your credit card statement, the list of classes you are taking, etc.  The common characteristic of such arrangements of data is that for each "thing"  (master record) there are multiple associated detail records.  For example, on your telephone bill, some of the displayed data describes the customer who received the bill (Name, Account number, Telephone number, Address, etc.) and appears once.   The details of telephone calls appears on the form as multiple rows with similar format.  Each row shows the Date of a call, the City called, the Duration of the call, the Time of day, etc.   The master and the detail data are likely to be stored in separate tables in Relational databases.   These tables will have what is known as a one-to-many relationship.   One customer has many telephone calls, but each telephone call is billed to only one customer.  In this tutorial, one CD is associated with many tracks, while each track is associated with one CD. Because such master-detail arrangements of data are so common, and because in databases the master data and the detail data are likely to be stored in separate tables, Microsoft has developed an easy way to create a single form to display data both the master and the detail data. We will create what Access calls a Main Form/Subform construct.  A Main Form will display data from the table that is the one part of the one-to-many relationship (i.e. from CD), while a Subform will display data from the table that is the many part of the one-to-many relationship (from Tracks).  The Subform will be planted on the Main Form.

1. In the Database Window, click on the “Forms” tab.
2. Click on the “New” button.
3. Choose the "Form Wizard" option and select the table that will provide data for the Master portion of the form (CD, in this case):
   
   Figure 12: Creating a new form.
   
   Click OK.
4. In the next dialog box, select the columns of master data you wish to see on your form:
   
   Figure 13: Choosing fields for a form.
   
   At this point, we are proceeded exactly as before, when creating a form based on a single table.
   
5. To include columns as well from the detail table (Tracks), choose the Tracks table from the combo box on this same dialog box.  Do not click Next yet.
   Figure 14: Choosing fields from a second table for a subform.
   In the screen-shot above, I have selected the Tracks table, seen the list of available fields from the Tracks table, and chosen two of them (Track_No, Track_Title) to be included on my master-detail form.
   Now, click Next >
6. Access now asks you to confirm the layout of the data from the two underlying tables.
   
   Figure 15: Choosing subform style for the main/subform.
   
   Choosing the Form with subform(s) option makes all of the fields you've chosen visible at the same time on the same form.  Choosing the Linked forms option creates a button on the main form which, when clicked, will bring up a second form with the associated detail records.
   Click Next >
7. A further decision you must make is how you wish the detail data columns to appear.  Datasheet makes them appear in a single table.  Tabular gives each data element its own textbox on the form.
   
   Figure 16: Choosing a layout for the main/subform.
   
   Click Next >
8. As for the form based on a single table, you are then asked to choose a background style, provide a title for your form and for the subform containing the detail data, and whether you want to view the form directly or modify its design.  The end product appears below:
   
   Figure 17: The main/subform.

Adding Controls from the Toolbox
The toolbox offers a quick means of creating a number of useful controls.   These include drop-down boxes (combo boxes), buttons, list boxes, radio buttons, toggle switches and others.  If the wizard is enabled, then the basic procedure is to click on the the control you wish to create on the toolbox, and drag or click again on the form approximately where you wish to place the control.  We will now examine some examples of control creation.

Drop-Down (Combo) Boxes

1. To create a drop-down (combo) box, first click on the combo box icon .  
2. Then, click on the form approximately where you wish to place the combo box.
3. If the wizard is enabled, you will be given a choice of options, as shown in the dialog box shown here.
   
   Figure 1: Choosing the source of combo box values.
   
   At  this point you have three options for populating your combo box with values.  First, you can have the combo box run a query when a user clicks on the combo box.  The query will run and populate the combo box with completely current data.  This type of combo box is very helpful if the values in the combo box are likely to change on a regular basis.  For example, if you had an application in which you had to pick an employee from the set of all current employees, then such a dynamic combo box would be appropriate, showing always the current set of employees.  This is most flexible option. Second, you can type in the values you wish at the time the combo box is created.  This option, currently selected in the figure, is appropriate for cases in which the options do not change frequently.  'Gender' is a clear example.   The third option will not only allow a user to pick one of the permitted values, but will then find a record that match the value chose.   This provides a fast 'record lookup' function.
4. In our current example, we choose to type the values in manually.  This option brings up a window in which we are given the opportunity to specify a) how many columns will be in the combo box, and b) the values we wish to be in the combo box.  Note that even if you specify multiple columns, only the left-most column will be displayed until the user clicks the combo box.
   
   Figure 2: Typing in values for the combo box.
   
5. When we click the Next> button, we are again given a choice.
   
   Figure 3: Choosing to associate the combo box with a table column (field).
   
   What do we do with the value the user selects?  One option is to do nothing other than store that value to be used later, such as in a query.  The other option, shown selected here, is to store the value in a table column.  If we choose this option we must also select, from the combo box shown here, the column into which the value is to be stored.  
6. Finally, we must provide a label for this combo box when it appears on our form.
   
   Figure 4: Providing a label for the combo box.
   
7. The finished combo box appears below.
   
   Figure 5: The finished combo box.

Creating queries

Query-by-example (QBE)

1. From the Database Window, select “Queries”
2. Click on the “New” button
3. (The wizard in this instance isn’t very helpful, so just click on the “Design View” option)
   
4. In creating a query, one needs to specify at least three things:
   • the tables involved in the queries
   • the columns that will either be part of the output shown to the user OR will be subject to some sort of criterion needed to determine which rows will be displayed in the result
   • the conditions (criteria) rows must satisfy to be included in the result
   In the “Show Table” dialog box, you will specify which table(s) contain the data you want to query over, by choosing a table and clicking on the Add button.  If multiple tables are involved in the query, repeat for each table.
   
   When all relevant tables have been selected, click on Close.
   
5. The Query design should now be displaying the tables you have chosen in the top portion of the window, and an area for further defining the query in the lower portion.
   
6. Suppose we wanted to show the album titles and track titles for all Beatles albums aquired after 1/1/1994.  We will first specify the columns of output (CD_Title, Track_Title) we desire.  We then specify any additional columns on which we will define criteria (CD_Artist, CD_DatePurchased).  We will specify these three columns by making these column names appear in the first two Field: fields in the lower portion of the window:
   
   There are three ways to do this, all yielding the same result:
   • double click on the columns desired in the tables shown in the top portion of the window;
   • click and drag a column from the top portion of the window into some field: field;
   • click on the combo box within the field: field and select a column name.
   Notice that the Show box is checked for CD_Title and Track_Title, but not for CD_Artist or CD_DatePurchased.  This is because we want CD_Title and CD_Track to appear in the output, but CD_Artist  or CD_DatePurchased .
   
7. We are interested not in all CDs, but only in Beatles CDs.  Therefore we are interested only in those CDs satisfying the criterion that the CD_Artist = "Beatles" and CD_DatePurchased > 1/1/1994
   
   Notice that the contents of the Criteria field can show an equality (the value in a row must match this one exactly) or an inequality.  Note also that Access has a peculiar format whenever dates are involved.  Surround the date with pound signs (#) for Access to interpret it as a date value.
   
8. To run the query, select Run from the Query menu, or click on the exclamation point.
9. If you wish Access to prompt you for a criterion each time you run the query, put, in hard brackets, the question you wish Access to prompt the user with, e.g. [Please enter Artist:] could be used in place of "Beatles" to make the system more flexible.
   
   Each time the query is run, the user would be prompted with the question, "Please enter Artist:"
   
   This technique is actually a "kludge", taking advantage of Access behavior when it doesn't recognize a parameter value, and is not recommended for full production systems.  However, as a short-term measure to add flexibility to a query, it works.

Buttons and Switchboards

The main switchboard is the initial form the user sees when starting the database application.  It provides a selection of buttons that will initiate the main activities the user might want to carry out.  Typically, these buttons open other forms that are more specialized, run queries, etc.  In short, the switchboard provides the user with a sense of the overall organization of the application.    Access has a wizard that will create switchboards for you, but I've never been particularly happy with the flexibility or functionality of these switchboards.   Consequently, I'm not going to cover it in this tutorial.  Rather, I prefer to build my opening screens from scratch.  In the process, we'll learn to create buttons.

1. Since the main switchboard (and you might even give this name to the form) is just a form with buttons on it, you create the main switchboard in the same way you create any other form. The only difference is that you don’t base this form on some underlying table or query. This form might be easiest to create without the wizard.
   
   Without choosing a table or query where the object's data comes from, click on OK
2. Because you chose no underlying source of data and are not using the wizard, you'll be looking at a bare-bones form:
   
   Let's add some buttons.

Buttons

1. Be sure you are in design view in form (the one you are now creating).
2. Be sure the wizards are turned on.
3. From the toolbar, click on the “Command Button” icon.
4. With the mouse, click on your form where the button is to be located.
5. The wizard will then kick in to ask you a series of questions about what you want your button to do when it is clicked.
   
   If you want the button to open a form, such as the one you have designed for data entry, use the “Form operations” category, and the “Open form” activity.
6. Access will then ask you to pick the form to be opened when your button is clicked:
   
   Click on Next>
7. In the window, the wizard asks you to specify which rows of data will be displayed in the form.  Many times you'll want all rows.  For other forms, you may want just a subset, based on some criteria you can specify.
   
   For our purposes, choose Open the form and show all the records.  Then click Next >
8. Each button can have an icon or text to describe its function.
   
   Click Next >
9. Finally, the wizard asks for a name to be used internally to refer to this button.   This name will not appear on the form where the user can see it, but will be helpful to the application developer as they customize the button's properties.
   
   When done, click on Finish
10. When you’ve come to the end of the wizard, feel free to resize or reposition, etc. the button. You may also select the button (so the handles are visible) and then double-click to bring up the properties list, where you can make other changes.

Building and using macros

What are macros?

Macros are one of the exciting things that make things happen in Access. Conceptually, macros are just a sequence of actions that are grouped together so they can all be executed together as a unit. For example, there may be a set of actions you want to happen when you click on some button (see below) on a form. You might want a button click to result in some form being opened and the contents of the form updated. These two actions (opening the form, setting the value of a control on the form) can be grouped together in a single macro. The button is then set so that the macro executes when the button is clicked. When the macro executes, both activities (in sequence) execute. The types of activities that can be done from within a macro are almost limitless: opening and closing forms, updating controls, executing queries, etc.

Defining a macro

1. From the database window, select the ‘Macro’ tab.
2. Click on ‘New’ to create a new macro
3. In the ‘Action’ column you will put in, one below the other, the sequence of actions you wish to be invoked when this macro is executed. For example, let’s say you wish to create a macro that will enable you to add a new record on a form, but will first place the cursor at a field you specify (two actions).
4. In the action column, pick ‘GoToRecord’ as the action you want.
   
   You will notice that at the bottom of the screen there are various fields you can fill in to tell Access more about what kind of record you want the system to go to. In the ‘Record’ field, select ‘New’. The macro will now cause Access to go to a new record in the currently active Form/Table
   
5. Put the cursor on the next line of the ‘Action’ column. This time, select ‘GoToControl’. Then, in the field at the bottom of your screen, type in the name of the control on which you wish the cursor to be placed.
6. Save the macro and give it a name
7. Exit the macro

Getting system events to kick off a macro

There are a number of ways to invoke a macro. Look in the property list of any control. Under ‘Event Properties’ you will see things like: ‘On Enter’ ‘On Exit’ ‘On Click’ ‘On Dbl Click’, etc.

These are all events that the system recognizes. In response to such an event, you can specify that you want a particular macro to execute.

1. Creating a button that will execute a macro when clicked. Create a button as described above. When the wizard asks you to select a category, select ‘Miscellaneous.’ In response to ‘When Button is Pressed:’ select ‘Run Macro.’ And so on...
   
2. Alternatively, you may specify the macro in one Event Property fields of any existing control. For example, if you have an existing button on your form, in the ‘On Click’ property of that form, you may select the macro from the combo box associated with that Event Property field.
   

Other uses of the Macro

The setvalue action

The setvalue action is used to assign a value to a control on, among other things, a form.  The value can take the form of a constant, a computed value, or the value of some other control.  In the current example, we would like to create a macro that will open the CD form, and for a new record put an initial value of "Jazz" in the combo box that enables a user to pick the CD category. To use the setvalue action, begin with the Macro in design view. You are now prompted for two arguments:  Item and Expression.   Item refers to the control that is to receive the new value.  Expression is the new value or, more precisely, an expression that resolves to some value.   Specifying an Item The Item must be specified using the full naming syntax which, in general looks something like this:   Forms![form name]![control name]  While you can type this in, it is in many cases more convenient to use something called the Expression Builder to create it.  To use the Expression Builder, click on the Elipsis (looks like three dots:   ...) on the right-hand side of the Item argument field. You will see the following: What you will do is click your way forward until you find the control you want, and the "paste" it into the expression field where you see the cursor in the above window.  In the current example,

1. double-click on Forms
2. double-click on All Forms.  Double-clicking on All Forms gives you a list of all forms in your database.  Double-clicking on Loaded Forms gives you only those forms currently open.  The latter can be more useful if you have a large database and are working on only a few forms at a time.
3. single-click on the CD form.  At this point you will see all of the controls on that form in the middle window.  
4. single-click on the cmbCategory control.  This is the name I have given to the combo box on the CD form that lists categories.

In the right-hand column, you now see all of the properties of the control you selected in the middle column.   For right now, we are, however, only interested in the value of the combo box.   We will choose as our "expression" the value of the cmbCategory combo box by now clicking on the Paste button.   You will now see the full name of the combo box appear in the expression window. When you click 'OK', this expression gets placed into the Item argument field in the Macro design view. For the expression, we wish to enter a constant ("Jazz").  The rule is that we should enclose constants in quotation marks.  If we leave out the quotation marks, then Access will interpret our entry as the name of some other control and cause an error. Now, save the macro.