• Mailing labels
• Rosters
• e-mail lists
• Members boats and harbors
• Members eligible for certain classes

In this section we will create these basic reports and then create a query to create selective reports.

You might think of a query as a way to ask the database a question.

A query can be used to create a more sophisticated report or help you organize a report. An example might be listing boat alphabetically by harbor.

It is suggested that you follow the building of reports in the order presented. Tips will be given which are not repeated.

Generally, the default format for the reports is sufficient. On the report layout screen you will be able to adjust the location of data and the font used, but little else. If you need more sophistication, you will need to export your data to an external program. See the section on Exporting.

If you have questions on creating a report, e-mail the instructor. A Q&A section might be added to this section of the tutor.

Mailing labels

This is the simplest of the reports.
If you need selective mailing lists, read the section on Queries after reading this section.

Start the report wizard

The assumption I am making now is that you want to make labels for all members.
The DataSource: Members includes records for all members.
Click Next
In a couple of seconds a screen will appear as a record source is built.

From this screen you must select label form. Avery 8160 is the most common. Text size and appearance can also be set. When you are ready click Next.

Select the fields in the order you want them displayed. Use the editing buttons to insert new lines, commas and spaces. A space needs to be added after the comma following the City. Also I placed 3 spaces to separate State and Zipcode. Use Undo to reverse your entries.

The next step is a little misleading. It does not print the labels. It only formats the labels for the selected printer. Select your printer and click OK

Here you can preview your labels

At the top of the preview screen you are given options to print, save, go back to make changes, of change the offset location on the label. A negative offset will move labels to the left.

After saving you are given a confirmation screen

When you are satisfied with the layout, select print from the menu and you are done.

Special note: InkJet printers use water soluble ink. Labels may smear if subjected to water or rain. Placing clear tape over the label can prevent problems or print using a LazerJer printer. LazerJet ink is not water soluble.

Roster

Use a query to select the fields you will use in the report and how you want the data sorted.

Remember to sort the last and first names "Ascending". Also, to place the Last name to the left of the First name. Sorts are selected left to right in the query. In the report, the first name can be placed before without effecting the sort order.

e-mail list

The query needed create the e-mail and Boat query is designed to eliminate all records which don't have an entry in the selected field. These fields are considered 'NULL' fields. Null fields are not the same an fields which have blanks or spaces. Null refers to the absence of any data.

The query line is: [Email] Is Not NULL
Select Not then Null. then click OK. Use Data view to see the results.

Remember to sort 'Ascending' on Last and First names
If a text file is created, it can be edited and used for mass mailing

Note that the red check has been removed from the name fields. Use this feature if you do not want the field to show in the display or report. It will not prevent the field to be used as a sort criteria.

Queries Intro

Follow the printer steps and select Query

Select File > New > Query

Members is the only tables that comes with the DB2000. Double click members. Members will appear in the right panel and the OK button will highlight. Click OK

In this query we will select fields to create a selective mailing label list. This is the field selection screen.

Double click the needed fields: First, Last Address, City, State, Zip, MemTypeStat

If you select a field by accident, don't worry. Right-Click in the field header and select delete.

Select Delete. We will use Expression.... very soon.

To sort the list by last name, select the dropdown list and ascending.

Next we will limit the list to specific members by membership type. See Membership codes.
Some squadrons reduce a members annual dues by reducing mailing costs and excluding them from mailings.
Usually this includes AC11, FM11, FM12 and WC10 and AP10.

Right click in the Criteria: field and select Build.

You will have to repeat the following inputs several times to build the Query string
The sequence to repeat is click: Like - fill the Argument box - then click OK.
The argument is AC10 for this pass. For the next pass, click Or then repeat the pervious steps 4 times.
Supply the following arguments AC15 AC2? AC5? AC7?. The '?' is a wildcard.
After the last pass be sure that your entries look exactly like the example; then press OK to return to the previous screen.

[MemTypeStat] Like 'AC10' Or [MemTypeStat] Like 'AC15' Or [MemTypeStat] Like 'AC2?' Or [MemTypeStat] Like 'AC5?' Or [MemTypeStat] Like 'AC7?'

If you are having trouble, clear the string area and cut-n-paste the above string.

Use the option buttons to View the resulting data, Create a file and above all SAVE YOUR WORK.

At the top of the view screen shows the rows of data. This also reflects the number of records of members to receive mailings.

You have two save format options: txt and .cvs. cvs files can be imported into Access or Excel.

For this tutorial, use the query name 'Mailing Active". Keep names short or they might be wider than the dropdown list can display.

The label report can now be run after selecting the query 'Mailing Active'

Member's boats

Use a query to sort boats by harbor and boat name. If a harbor isn't listed, it will fall into an 'unknown' group. Members without a boat are eliminated from the list by the NULL boat name criteria. This is important because the ownership of a boat might be known, but the harbor might not.

[BoatName] Is Not NULL