The following video (approximately 105 minutes) walks new users through the Custom Search and Custom Reporting functions of NACCRRAware. A guide to Query Building is also below.
NACCRRAware Query Builder
Using Conditions Correctly
- “Equal To”will list all records exactly matching the value entered.
- “Not Equal To” will list all records where the value isn’t equal to the value chosen.
- “Contains” can be used for text fields only and will list all records that contain the value, regardless of the position of the value in the field. For instance, the query ‘City Contains “black”’ lists records where the city name is Blackwell, Black Bear or Stoneblack.
- “Greater Than”, “Less Than”, “Greater or Equal” and “Less or Equal” can be used for fields storing numeric data (dates, vacancies, rates, numbers of children, etc.).
Using Separators Correctly
When AND is used in a query, the results must meet the conditions on both sides of the statement. For example, ‘Environment Equal To “No Pets” AND Environment Equal To “Non Smoking”’ lists providers who have a smoke free environment free of pets.
When OR is used in a query, the results can meet the conditions on either side of the statement. ‘Environment Equal To “No Pets” OR Environment Equal To “Non Smoking”’ lists providers who have a smoke free environment free of pets as well as providers who have a smoke free environment with pets or an environment without pets that’s not smoke free.
DONE should be used to indicate the end of a query. While it won’t affect the query results to have a dangling AND or OR at the end of a query, it can make placing parentheses (as described in the next section) more difficult.
When to Use Parenthesis
Any query that contains both separators (“AND” and “OR”) will require the use of parenthesis, regardless of whether another statement containing “AND” is going to be a part of the query. For example, if you build a query around Type of Care and Date Provider Added, your query might be:
Type of Care Equal To “Family Child Care” OR Type of Care Equal To “Child Care Center” AND Date Provider Added Greater or Equal “03-01-2011”
If you run the query at this point, NACCRRAware will generate a list of all family child care providers and add to that child care centers added on or after March 1, 2011. It reads the query differently than what you wanted. You wanted a list of family child care providers AND child care centers added on or after March 1, 2011. You need to add parenthesis to the query to separate the query into (type of care) and date added:
(Type of Care Equal To “Family Child Care” OR Type of Care Equal To “Child Care Center”) AND Date Provider Added Greater or Equal “03-01-2011”
Please note: The parenthesis must be placed directly before the first letter of the first query value affected, and directly after the last quotation of the last query value in the string. If you receive the following message, “Query Builder parsing problem - missing AND/OR combination, clear and try again”, then the spacing between the parenthesis and the AND/OR is incorrect.
Additional Notes on Building Queries in NACCRRAware
Date fields must be entered in the following format: month/date/year where year is written as a 4 digit number. It’s not required to use a 2 digit month or date but using a 2 digit year will result in a 1900 date (“4/1/11” will query as “April 1, 1911”). Dashes can also be used in a date value: month–date–year.
Address fields in client and provider queries should include the address type selector (Provider Address Type or Client Address Type). If the address is not specified, the query results will include records where the address value is contained in any of the address fields. For example, Zip Equal To “12345” will list client records where the zip 12345 is found in Location, Mailing, Address3 or Address4. To limit the results to just those clients LOCATED in 12345, the query should be: ‘Client Address Type Equal To “Location” AND Zip Equal To “12345”’
Queries using the provider minimum/maximum age range fields or the client age care needed field have the following in the value box on the query builder: yrs mos wks. It’s important NOT to remove this but to simply add the necessary data. To query providers who accept infants, the query would be similar to: ‘Minimum Age Range Less or Equal “yrs mos 6wks”’. It is not necessary to add zeroes to the unused categories. To query client records for clients with children under 1 year of age, the query would be similar to: ‘Age Care Needed Less Than “1yrs mos wks” or ‘Age Care Needed Less Than “yrs 12mos wks”’.
Provider queries using fields in the rates and/or population info tables on the Shifts screen should include either “Rate Age Group” or “Population Age Group” to limit the search results to a specific age group. For example, to list providers charging $100.00 or less for full time infant care, the query would be similar to: ‘Full Time Weekly Rate Less or Equal “100.00” AND Rate Age Group Equal To “0 – 51 weeks”’.
- What records are automatically excluded from the custom search results?
- How are the records sorted in the custom search results?
- When do you add parentheses to a query?How do you add a field to the query builder?
- How do you add a field to the query builder?
- Name one way you can edit the search query.
- What happens if you forget to change the separator to Done when adding the last query field?
- What may prevent the results window from opening up?
- Where can you find saved searches?
- Name one way to access the custom report field selection screen to begin building a custom report.
- How do you build a custom report that includes ALL clients or providers?
- How many ways can you sort the records in a custom report?
- What type of file can you create with the report results?
(For sample answers to these questions, check out: NACCRAware 101: Review Question Answer Key)
Please remember that these are the configurations in the NACCRRAware training database. The configurations for certain fields may vary by field name and value for some of these queries in your agency’s database.
- Search for clients added to the database this month.
- Search for family child care providers who are CPR certified.
- Search for clients needing care for an infant (defined as a child under 1 year of age) in a smoke free and no pet environment
- Search for center based programs which accept 6 year old children and offer an afternoon snack for $75/week (full time) or less.
Other parts of this tutorial: