The REJV tool – CARES NG Documentation

Introduction

The entire Residential Exposure Joint Venture (REJV) database is stored in the CARES NG® platform but it is available only to the REJV members This database collects, organizes and analyses label and use information for pesticide products used in and around the home; please refer to the section The REJV database for more information.
The REJV data cannot be used in a residential assessment but the CARES NG® gives you the ability to query, summarize and analyse them through the REJV tool. This section describes how to get access to this tool and use it. This tool works only on the tables that compose the REJV database and whose structure is described here under.

Description of the REJV tool

The REJV database is composed by the following eight tables:

Demographics – The Demographic data is the original sample frame of all individual that were asked to participate on the survey and received the screener questionnaire. The questionnaire captured the principal demographic data such as geography, household characteristics, and characteristics of the household members.
Screener – The Screener data is the initial group of individuals that were asked to complete a screener questionnaire about pesticide use. The objective was to identify a pool of individuals representative of the US population that are pesticide users.
Professional – The Professional data record the applications of pesticidal products made by professionals. The date of professional application is separately recorded as month and day where the treatment occurred (indoors, outdoor lawn, other outdoor, and don’t know). For each site multiple pesticide codes for indoor and outdoor pesticides were recorded (up to 10 codes for indoor and up to 20 codes for outdoor)
Miscellaneous – The Miscellaneous data is based on various responses that are not categorized (comments, blank values and so on)
Inventory – The Inventory data record the quantity of product(s) used every month
Inventory_updated – The Inventory Updated data is a revised, cleaned up version of table Inventory
Application – The Application data records where and when each product was applied.
Application_updated – The Application Updated data is revised, cleanup version of table Application

A ninth table, that does not belong to the original REJV database, is added to the list of the REJV tables installed in CARES NG® and it is called Active Ingredients. This table contains the list of the active ingredients, both their codes and their names, recorded in the REJV database. It is possible to use this table in the REJV tool to perform analyses on specific active ingredients.
All these tables are saved and made available in a specific folder, called REJV, which is listed under the CARES Data folder in the CARES NG® File Management System.

How to access the REJV tool

In the CARES NG® File Management System, browse to the My Data folder, Group Data folder or any subfolder. Click on and select REJV in the dropdown list.

You are now inside the REJV tool which consists of two parts:

a MySQL editor that allows you to run queries on the nine REJV tables, placed at the top of the REJV tool
the result area which is the grey area underneath the MySQL editor; this area will contain the result of the MySQl queries run in the editor

The MySQL eidtor contains two buttons:

The button runs the SQL queries written in the REJV tool
The button opens a sidebar which allows you to access to
- Sample Queries which is the list of sample SQL queries
- Table & Fields which is the list of the REJV tables and their columns that can be queried through the REJV tool

The sidebar was design to help you writing the SQL queries to be run in the REJV tool; the sections here under describe how insert the information provided by the sidebar into the SQL queries.

The scope of this tutorial is not to teach the MySQL language, but to explain how to write and run SQL queries in the REJV tool and save the results of these queries in the CARES NG® platform. It is possible to find friendly and comprehensive MySQL tutorials online, such as W3 schools or Tutorials Point.

How to run a SQL query in the REJV tool

The REJV tables are not editable and cannot be modified; hence the REJV tool allows you to select a subset of the data contained in the database and carry out some data manipulation such as grouping the data and calculating a number of summary statistics. All the MySQL queries to perform these type of actions begin with the command SELECT; this is the reason why the MySQL editor contains by default the SELECT command.

The REJV tool is immediately ready to take and run MySQL queries. To try the tool out, write the following query in the MySQL editor:

SELECT * FROM `application` LIMIT 5

Then click on . The result area now contains a table of five rows that correspond to the first five rows of the Application table, as shown in the image below, and a white bar with

the number of rows selected and displayed in the result area; this is displayed on the left side of the bar
the button which allows you to save the result of the query into a new table.

Click on ; it opens a pop-up windows that allow you to assign a name to the new table which contains the result of the MySQL query. CARES NG® provides a default name to the table, which is formed by the string REJV-Results— followed by the time and the date when the table is created.

To assign a new name to the table, click inside the text field, select the default name and type the new name; for this tutorial, type REJV table 1. Once you are happy, click on
The new table is automatically opened in the CARES NG® Data Editor; the new table is not editable, but you can perform further analyses on the data by using the filtering and sorting tools of the Data Editor.

Click on to close the table and go back into the CARES NG® File Management System.

The REJV tool allows you to query in a straightforward way the frequency of all the distinct values present in each column of the table in the result area. Right-click on the header of column of interest; a drop-down list appears under the header with the option Group by distinct.

Click on it; a pop-up window opens which contains a table with the following information:

the list of the distinct values
the frequency of each value
the percentage of the total of the frequency of each value

How to use the sample queries

Click on to open the sidebar and then click on Sample Queries.

A list of five queries opens underneath; these are:

Generic Select Query; it selects 8 fields from the application_update table where the pnamex field has a specific value
Active Ingredient (Inventory Update); it selects all fields (*) from the Inventory Update table where any one of six columns has a specific value.
Active Ingredient (Application Update); it selects all fields (*) from the Application Update table where any one of six columns has a specific value.
Count Distinct; it selects the distinct values of the pnamex field and the number of times they appear (GROUP BY pnamex and COUNT ), and also that total as a % of all rows ((COUNT / (SELECT COUNT FROM application_update)) * 100). It also uses AS to name the fields that it creates (COUNT AS ‘Total’)
Joining Two Tables; selects columns from two tables (application_update and demographics) and matches them on the hhidx column in each (INNER JOIN demographics ON application_update.hhidx = demographics.hhidx). In order to avoid confusion fields are the prefixed by the table they come from (application_update.allyrx). This query also filters (WHERE) rows based on two comparisons and one string contains (LIKE “%9688%”)

Delete the default SELECT from the MySQL editor and click on USE beside the name of the query you want to use; the MySQL code of the query is automatically inserted and displayed in the editor of the REJV tool. For example, click on USE beside the query Generic Select Query; the editor contains the code of this query, as shown in the image below.

Close the sidebar by clicking on the X in the top right corner; then click on to run the query and inspect the result. The query is editable and you can modify it in order to select and analyse other subsets of the REJV data. It is possible to change the list of the fields and the tables used in the query by selecting them from the list displayed in the Table & Fields section of the sidebar, as explained in the following section.

How to use Table & Fields

The Table & Fields was designed to provide a quick way to insert tables and fields into the MySQL queries. Open the sidebar and click on Table & Fields; you are presented with the list of the nine tales composing the REJV database.

Beside the name of each table, there are:

the + sign which opens the list of the table’s fields underneath the table name
the ADD sign which allows you to automatically insert the table name into the MySQL query

If you want to insert a table name into the query, place the cursor of your mouse in the spot of the MySQL editor where you want to insert the table name and then click on ADD beside the name of the table you want to query. For example, write the following MySQL code:

SELECT * FROM LIMIT 5

Place the mouse cursor between FROM and LIMIT, then click on ADD beside application_update; now you should see this query written in the editor space:

SELECT * FROM `application_update` LIMIT 5

If you want to add a field to the MySQl query, click on the + beside the name of the table that contains the field; place the cursor of your mouse in the spot of the MySQL editor where you want to insert the field name and then click on ADD beside it. For example, write the following MySQL code:

SELECT FROM `application_update` LIMIT 5

Place the mouse cursor between SELECT and FROM, click on + beside application_update then click on ADD beside hhidx, usablex, appdatex, appmonthx, pnamex and write a comma between the field names; now you should see this query written in the editor space:

SELECT `hhidx`, `usablex`,`appdatex`,`appmonthx`, `pnamex` FROM `application_update` LIMIT 5