Filtering Data for Reports

 

When a report is selected from the Reports Menu, a filter form is displayed.  This form can be used to scroll through the records that are available for the report.  Do not make any changes or additions to the data in these forms.  Always use the Main , Detail Table or Books forms from the Main Menu to modify the information in the database.  In most cases, only some of the records are desired for a report.  To create a filter for the records to be included in the report, click the Build Filter button.  A new window opens, overlaying part of the filter from.  This will appear similar to the following.

 

This new window consists of two panes.  The top pane contains a Field List box with the inventory data fields that are available for use in filtering.  This box is titled "qryXXXXX", where XXXXX is the name of the report selected, i.e., qryMain, or qryIntegrated.  Throughout this Help file this box is referred to as the qry box.  Below this is a series of rows and columns.  In this pane, you will build the selection criteria for the information to be displayed in the report.

 

If the window covers too much of the filter form hiding the bottom row of buttons, as shown below, simply left click the Restore Window button in the top right corner of the screen.  This button looks like two overlapping boxes.

Many books describing Access are available and will provide additional information regarding the use of filters.

There are several ways to select the data field to be used for the filter as described below:

 

1.  The data field to be used in a filter may be selected directly from the field list qry box in the top pane of the filter form by scrolling to the desired field and double clicking with the left mouse bottom.  This automatically places the field name in the next empty cell in the Field row of the lower pane and is the method used in the User Manual.

2.  The data field can also be selected from the same field list qry box by placing the cursor on the desired data field and while pressing and holding the left mouse button, dragging the field down to the Field row in the bottom pane of the form.

3.  Or you may click with the left mouse button in the first empty cell in the Field row of the bottom pane.  This will open a drop down box listing all of the available data fields.  Scroll to the desired field and click with the left mouse bottom to select the field.

4.  Another method that is very easy to use after you become familiar with the database structure and the data field names, is to place the cursor in an empty cell in the Field row and start typing the name of the field.  The system will select the field as soon as you begin typing, when the correct field is shown, hit the enter key.

Note: With any of the above methods, only those fields available for the selected report can be used.  In other words, you will not be permitted to search on Detail Table fields for a Main Inventory Report, or Main Table fields for a Detail Inventory Report.  In all other reports, you will be permitted to search on any field from either table.

 

To filter on a field, scroll to the desired field in the qry box displayed in the top pane of the filter form and double click with the left mouse button.  This places the field in the first empty cell in the Field row of the lower pane.  You then enter the string of characters that you are looking for under the field name in the row labeled "Criteria:".  The system attempts to find a data element matching your entry, so it is suggested that you place your entry in quotation marks. Continue across the columns and repeat this process for other fields to complete the selection criteria for the desired report. There is no limit to the number of fields used for filtering.   Continue across the columns and repeat this process for other fields to complete the selection criteria for the desired report.  There is no limit to the number of fields used for filtering.  Use of "Y" is particularly important where the field contains only a "Y" (Female, Rural, Kitchen, etc.) otherwise, the system automatically inserts "year."

Use of Null or Not in Filters

To eliminate records containing certain information, the word “NOT” may be inserted before the string entered into the Criteria row.  This selects records not matching the string, i.e., to skip all inventories with a CLASS of “OF”, enter NOT OF into the criteria block under CLASS.  The system converts this to NOT “OF”.

 

The word Null may be used to select records where a data field is empty, i.e., to locate records where the MATERIAL field contains information, you could filter on the MATERIAL field with the Criteria of Not Null.

Apply Filter Button

At any time, you can see the results of your filter by clicking the Apply Filter button. This will show the number of records selected by your criteria and allow you to scroll though the records to determine if the filter performed as desired. If further filtering is required, simply click the Build Filter button again and continue to build the filter criteria. At times the results of filters you are trying to modify do not clear properly. To resolve this condition, simply click the "Show All Records" button, close the Filter form by clicking the "X" in the top right corner and start the filter process again.

The Filter form may remain on the screen after the Apply Filter Button has been clicked, blocking part of the window. This can easily be closed by clicking on the "X" in the top right corner.

 

Special Characters

Due to the use of special characters in the database, it is recommended that the wild card character: ASTERISK (*) be used to ensure that no records are missed.  As an example, when selecting CLASS of Elite, use "E*" instead of "E".  This will select all records with a CLASS of "E", "E+", and E-".  When the asterisk is used as described above, the system  automatically converts it to a wild card search by changing it to Like "E*".  Since the database also includes special characters such as "=" to indicate an assumption and "-" indicating that the item is part of a list of items, when looking for "chair" in the TYPE field, use "chair*" to ensure that no chairs are missed because of special characters.  The string of characters entered as the criteria are not case sensitive; therefore "chair*", "Chair*", or “CHAIR*" yields the same results.

 

When searching for a word or string of characters anywhere within a field, use the wild card character (*) before and after the search string.  When looking for all records containing the word "old" in the MEMO field, enter *old* in the criteria row under MEMO.  The system converts this to Like "*old*".  This  selects all records that contain the string "old" anywhere within the MEMO field.  It should be noted that the system is not selective regarding this search and the results will include records with the words "sold', "olden", "gold", etc., as well as "old."

The criteria shown in the following illustration selects all inventories belonging to women in Maryland, with a CLASS of "Elite", listing livestock.

Complex Searches

To make more complex selections, the use of and/or logic is possible.  In the above example, to include Aspiring households along with Elite you would enter "E* or A* into the Criteria row under CLASS.  The system will convert this to Like "E*" or Like "A*" indicating that either Elite or Aspiring will be selected.  The simple way to understand the and/or logic is to think of the first row, labeled “Criteria:” as an “and” condition and the next row, label “or:” as a separate condition to be handled as another selection.  In other words, any record that is not selected by the logic in the “Criteria:” row is again evaluated by the logic in the “or:” row.

 

The following criteria generates a report of all mahogany chairs and also all mahogany or all walnut or black walnut tables.

Run Report Button

 

When the record selection is completed and you have the selected records displayed by clicking the Apply Filter button, click the Run Report button.  Prior to running the report, you are prompted for a Report Header.  The information that you enter here is included as a page header on the report, allowing you to describe the filters applied or other report identification to be included on the printed report.  The Report Header may be left blank.  Once the Report Header is entered, click OK.  This runs the report in a Preview mode.  A new window opens with the report shown as it will appear when printed.

Note: If this is the first time that you have run this report, you may get a message similar to “This document was previously formatted for the printer ......., but that printer is not available.  Do you want to use the default printer ............?”  This message means that the printer that was used for development was not found on your system.  Simply answer Yes to this question and the system will use your default printer from now on.

 

Scroll through the report to view it on the screen, by using the arrow buttons on the bottom of the screen.

 

To send the previewed report to the printer, click Print on the top of the screen.  If you do not want a printed copy of the report, select Close Print Preview.  Do not select Exit, as this will terminate the entire database program.

 

I

Filter Example

 

The following steps show a typical use of the Probate Inventory Database to find Windsor chairs in elite households in inventories taken after the year 1799.

 

From the Main Menu that appears when the Probate Inventory Database is started, select Reports.

 

From the Reports Menu, select Expanded Report.  Then click the Build Filter button.  This will display a Filter form similar to the one shown in the User Manual in the Filtering Data for Reports section.

 

This form will contain a section of cells arranged in rows and columns, with the rows labeled as Field, Sort, Criteria and or.

 

With the cursor scroll in the box labeled qryExpanded to the field CLASS.  With the field CLASS highlighted, double click with the left mouse button.  The system will place CLASS in the first column in the row labeled Field of the lower pane.

 

Move the cursor down to the cell below this in the row labeled Criteria and type E*. The system will place Like “E* in the Criteria cell.  This will select all elite inventories.

 

For the next column scroll in the qry box down to DATE_TAKEN as the field and enter >12/31/1799 as the Criteria.  This will eliminate all inventories taken before Dec 31, 1799.

 

Move to the next column and select CATEGORY and enter FURN* as the Criteria.  The system will place Like “FURN*” in the cell.

 

Move the cursor to the next column and repeat the above steps to place SUB_CATEG in the Field row and Seat* in the Criteria row.

 

Repeat the above to select Chair* in the TYPE field.

 

In the next column select DESCRIPT as the Field and enter *Windsor* and the Criteria.  The use of the wild card character (*) before and after the word “Windsor” will locate the word anywhere in the field DESCRIPT.  The use of the single asterisk following the other Criteria row entries will only locate the word if it is in the beginning of the field.

 

Click the Apply Filter button.  You will then get a form showing the records which passed through the filter that you have built.  The bottom of this form will show that you are displaying record 1 of n (where n is the total number of records selected).

 

If the filter was successful and you have the records that you want, then click the Run Report  button.  You will then be given the opportunity to label your report.  Click OK and the report will be run in a preview mode.  To print the report, simply click File >  Print from the top menu.

 

When the printing has completed, close the print preview view by clicking File > Close from the top menu.

 

For this example, we will assume that you did not find as many Windsor chairs as you had expected.  You could expand the search to include aspiring households as well as elite.  Do this by clicking the Build Filter button to modify your selection criteria.

 

Go to the Criteria cell under CLASS and replace the entry with E* or A*.  The system will place Like “E*” or Like “A*” in the cell.  Click the Apply Filter button again.  This filter will now include both elite and aspiring inventories.

Filtering by Date

When using the DATE_TAKEN field for filtering, it is important to notice that this field is stored in a special date format.  In order to enter criteria it must be a valid date, entered in a proper date format of MM/DD/YYYY, example: 12/31/1799 or MMM/DD/YYYY, example: Dec/31/1799..  When looking for inventories between two dates the criteria entry could be >12/31/1749 and <01/01/1791, or you could use the Between function.  In this case, the entry would be Between 12/31/1749 and 01/01/1791.  This will select all inventories with a DATE_TAKEN later than Dec. 31, 1749 and earlier that Jan 1, 1791 or in other words from 1750 to 1790.  The system will enclose the dates with pound signs (#) appearing as >#12/31/1749# And <#1/1/1791#.

Be sure to use four digits for the year, otherwise the system will assume the 20th or 21st century.

Note: Feb/29/1804 is a valid date, but Feb/29/1803 is not because 1803 was not a leap year.            

Click the Apply Filter button and review the selected records.  When the record selection is completed, simply click the Run Report button.  Prior to running the report, you are prompted for a Report Header.  The information that you enter here is included as a page header on the report, allowing you to describe the filters applied or other report identification to be included on the printed report.  Once the Report Header is entered, click OK.  If no header is desired, simply leave the entry blank and click OK.  This runs the report in a Preview mode.  A new window opens with a copy of the report shown as it will appear when printed.

Note: If this is the first time that you have run this report, you may get a message similar to “This document was previously formatted for the printer ......., but that printer is not available.  Do you want to use the default printer ............?”  This message means that the printer that was used for development was not found on your system.  Simply answer Yes to this question and the system will use your default printer from now on.

 

                Scroll through the report, by using the arrow buttons on the bottom of the screen, to view the report as it will appear when printed.

 

                To send the previewed report to the printer, click File and select Print on the top of the screen.  If you do not want a printed copy of the report, select Close.  Do not select Exit, as this will terminate the entire database program.

 

                If the Print option does not appear under the File drop-down list, move the cursor to the double downward arrow.