The best way to gather this information is via Report Builder (Query), This report will require building a string query to identify memberships who have received unemployment deposits in a given month. Please Note: Using Tool #100 to build queries typically requires training. The information written below assumes the reader already has entry-level understanding of database terminology and query skills.

Building the query is one part of a total strategy:
  1. Find and list the members receiving unemployment deposits (via Query)
  2. Take action with the list of members – study, utlize CU*BASE communication tools, create analysis, etc.
    • Once a list (file) of these members is created, there an array of options to use the information. Additional information on member communication and analysis tools can be found by visting CU*BASE Online Help or the CU*Answers Reference library. 
Let’s cover the basics of what you’ll need to begin,

Key Tables:

Data Table (file) Location Description
TRANS1 FILExx Transaction postings – current month
ETRmmyy1 FILExxE Transaction postings – prior month (mm = month, yy = year)
TRDESC FILExx Transaction descriptions – secondary details
MASTER FILExx Member file – All open memberships

Query Requirements:

  • This report will require the user to build two separate queries that work together (a string query).
  • The first query will need to identify the transactions being deposited and summarize per member.
  • The second query will take in the file created by the first query, polish it into something compatible with CU*BASE communication or analysis tools, and link in additional relevant fields about the memberships.

Building the Report:

The first query produces file UITRX which organizes the transaction data into a nice, clean, summary of activity per member. To organize the information to 1 row per member, you must use the Sort/Break/Summary tactic on the ACCTBS field. To filter transactions to only ACH deposits from unemployment agencies, you need to set filters narrowing results down to only ACH deposits. There are multiple correct ways to do this. One example is shown below:

TRORIG = 11 (ACH activity)
TRANCD = 12, 32 (checking and savings deposits)
TRTYPE = 18 (ACH deposits only)
TRDESC = varies per state (this needs to be your state’s specific unemployment depositor’s description)

The second query should then further organize and add member details into the UIMBRS file, most importantly setting the Account Base (ACCTBS) as the first column in the file so that the file will be compatible with other system tools.

In summary, an example of the final product is outlined here, with the UIMBRS file name holding the final list of memberships:
 
Query Name Source File(s) Output File Notes
UNEMP01 ETRmmyy1, TRDESC, UITRX Edit month (mm) portion of ETRmmyy1 file as needed to choose which month the query should evaluate.
UNEMP02 UITRX,
MASTER		 UIMBRS Edit the fields included in the final list to make sure ACCTBS is the first field in the file