AWdb Support

Sometimes you need information that is not readily available on specific pages in the database or with a formal report. In that case, a query of the database can produce the information you need for a custom, or ad-hoc, report. The Query Module is designed to facilitate creating queries to get custom information. The results are displayed in a table and include options for filtering, grouping, and sorting.

While the Query tool is designed to help create custom queries, a basic understanding of relational databases is needed. For assistance with a specific custom report need, please contact customer support.

Query Toolbar:

Add New Query - use to start building a new query.

 Open Query - opens a dialog with a list of saved queries. Select a named query to use. This loads the saved query into the query builder. The name of the loaded query will be shown at the top.

Save Query - once a query is built, click the Save Query button to name the query and save it for later use. Queries can be saved as Private (only the user who created it can access it) or Public (all users can access it).

 Delete Query - to remove a saved query, first open it, then click/tap Delete Query and confirm the deletion.

To Build a Query:

1. Person table. The Person table is required for all queries. No columns need to be included, but generally member name information is desired at a minimum. Select any columns that should be included in the results.

Tips:

• Select the Person status to include in the query. This will limit records to members with this status. For most queries, this will likely be 'Active' and 'Visitor'. The status column does not need to be included unless the status should be shown in the result table.
• To include member names, select how they should appear:
  • Filing Name = one column using "last name, first name" (i.e. Smith, John)
  • Mailing Name = one column using "{first + last name}" (i.e. John Smith)
  • First Name and Last Name will display as separate columns in the result table (i.e. one column with John and one column with Smith)
• Select any other columns from Person to include in the result table.

2. Add a Related table and select additional columns to include in the result table.

    Tips:

• Some tables, like Check-in, require another table (Role) to be included. If another table is required, it will be noted and selections will be disabled until the other table is included. The required table does not need to have any columns selected; simply select it as a related table.
• Each table has options for common selections. (Details below.)
• Most tables have an option for 'Only include people with....' This is selected by default and should be left selected in most cases to avoid records with no data.
• Grouping selections are suggested when results will include multiple rows.

Select from the following tables:

• Award - select Award Sources to include; limit to 'This year' or use 'Any year'
• Book - select which Books to include
• Check-in - requires Role table; Club Year selection is determined by the Role table's Club Year selection; select a Session (club) date
• Consent - requires Role table; Club Year selection is determined by the Role tables' Club Year selection; select which consents to include (Choosing 'All Consents' enables selection of columns, else a default is used.)
• Correspondence - select Correspondence Types to include and a Club Year
• Event - select Event Names to include and a Club Year
• Household - select 'All households' or 'Only the primary household'
• Household Additional Info - requires Household table; select a Club Year and which Additional Info to include (Choosing 'All additional info' enables selection of columns, else a default is used.)
• Household Communication - requires Household table; select which Communication Types to include (Choosing 'All communication records' enables selection of columns else a default is used.)
• Household Ledger - select 'All', 'Fees', or 'Payments' and a Club Year
• Leader Service - select a Club Year
• Leader Training - select Leader Training Descriptions to include and a Club Year
• Member Additional Info - requires Role table; Club Year selection is determined by the Role tables' Club Year selection; select which Additional Info to include (Choosing 'All additional info' enables selection of columns, else a default is used.)
• Member Ledger - select 'All', 'Fees', or 'Payments' and a Club Year
• Person Communication - select Communication Types to include (Choosing 'All communication records' enables selection of columns else a default is used.)
• Role - select a Club Year and which Roles to include
• Section - select a Book and a Section and which type of book to include
• Visit - select a Club Year

3. Order columns for the result table. Selected columns will be shown in the "Display Columns" section. By default, they are in the order they were selected. To change the order the columns are displayed in the result table, drag thebutton next to the column name to place it in the preferred location.

4. Add Filters. To further limit the rows returned in the results, filter conditions may be included. Click the '+ Add Filter' button. Select the Column to be filtered, the Operator, what to Compare To and the Value. Additional instructions are provided in the Filters section. The Filter Actions Buttonprovides additional selections for: Add filter after, Start group here, End group here, and Remove condition.

5. Select Options. Choose up to 3 grouping options and up to 3 sorting options from selected columns.

6. Run Query. The query will populate a table with the results. A filter is provided for each column in the result table. Use the Show Print Menu button on the top of the page to print or export the results.