BAR 101

Keepin' It Clean - Using GL database with SMART, InProcess, Audit, IPEDS

Clarissa Wolfe,Yakima

April 21, 2016

YVCC uses our General Ledger Database for everything… finding data, cleaning data, pulling data for auditors, running queries for IPEDS, and more. The GL database is a look at the accounting transactions from a database perspective, allowing queries of all information fields. You can find information quickly based on one field, multiple fields, or pieces and parts using wildcards. Join us for a demo of the GL database, the database fields, and some query techniques. We’ll show how we use GL Database to find SMART or InProcess errors as well as pull data for audit, auditors, and IPEDS.

What’s the GL Database?

The General Ledger Database is a Microsoft Access overlay to data stored on the SQL Server. The data is stored in tables for each fiscal year. Tables include data from 9899 to present fiscal year. The college runs a process each weeknight to pull current day data into the “Current Month” table. The morning following fiscal month close, the college verifies all GL Totals from the Current Month table with the GA1331 General Ledger Report. Once the data is verified, the college runs an append query to copy the Current Month table data to the 1516 All GLS table. The evening following fiscal month close, the Current Month table will be populated with the new month data and is current as of the night before.

What Data Fields are Available?

The GL Dbase fields include:

Most of these fields you should recognize. The LoadDate is a field that is just the date/time of the append. It allows us to find and delete sets of records if we need to redo the “append” process.

How do we query data?

For the most part, you always use a “query” to look at the data. The power behind using the database is you can tailor the data to what you are trying to find… one field, several fields, and pieces and parts of a field.

What Queries are Available?

When GL Dbase was created, the designer provided the Business Office with four primary queries for each fiscal year.

  • ALLAll dbase fields, date field based on part of the batch#
  • All GLsAll dbase fields, date field based on part of the batch#,

and search criteria in the “date” field for yymmdd or * for all

  • RevExpAll dbase fields, date field based on part of the batch#,

search criteria in the “date” field for yymmdd or * for all,

where GL=Like "32*" Or Like "65*"

  • SumsSome dbase fields are grouped with a SUM for TRNS_AMT

Here’s a sample of the query listings for the 1516 queries and the Current Month queries.

For the most part, we use qry1516 All GLs and/or qry Current Month All GLs.

TIPS:

  • ALWAYS open the query in DESIGN VIEW.
  • Find the query you want to open, right-click, select DESIGN VIEW.
  • Looking for expenses, find the GL column and put in 65*.
  • Looking for revenue, find the GL column and put in 32*.
  • Looking for a particular batch, find the BTCH_NUM column and put in the number or a wildcard. For example, WC* would bring up all batches for WC (Wolfe Clarissa).

GENERAL QUERIES

Let’s run a sample of the 1516 All GLs query for GL 65*

Query: qry1516 All GLs

Where: GL=65*

Now, let’s run it for 65xx and a specific ORG

qry1516 All GLs

GL=65*

ORG=1220

This is just a snippet of info… there are 406 lines of data for this data pull.

Now, let’s run it for 65xx, a specific ORG, and where BTCH_NUM=PC*

qry1516 All GLs

GL=65*

ORG=1220

BTCH_NUM=PC*

In just moments, you can find data based on what you need. 26 lines, $21,125.45 in a few clicks.

You can sort it by any field and further filter the data down based on what you are trying to find.

Let’s find Accounts Receivable info for Labor and Industries (Agency #235)

qry1516 All GLs

GL=13*

DUE_TO_FRO=235

Sorted by TRNS_DESC, YRQ, and Date.

QUERIES FOR SMART

Find the Entry

For SMART errors, we use the GL Dbase to search for the error… to find the batch. Then, we can identify what is wrong with the entry based on the SMART instructions.

Example: SMART#404 VPA, we had an error between 057 and 060. We identified the batch, found the incorrect piece of data, and then did the correcting entry. (See the attached info)

QUERIES FOR IN-PROCESS

In Process Errors

You would use the GL Dbase to find the data associated with the error identified on the In Process Error Report. Query on the Fund, GL, and Subsid or any other information provided. Sometimes, just query for that transaction amount. The GL Dbase would tell you the entry and then you have to figure out how to fix it.

We have had opening entry differences. Normally, I would be expecting these based on a manual adjusting entry, but sometimes there are other items that show up. I will research it via the dbase and then try to figure it out.

QUERIES FOR THE AUDITORS

Trend of General Ledger Balances

The auditors wanted the ending balances of the Fund Ledger. We gave them the GA1332 Fund Ledger report 13th month close for FYR1314 and the prior 2 years. They wanted to “trend” the amounts for the GLs for the last 3 years and they would have to enter the ending total into their spreadsheet. To expedite this task, we pulled the general ledger totals by GL by FYR, put it into Excel and did a pivot table to summarize by the FYR.

To total the data, use the SUM query. Pick the GL and SUMofTRNS_AMT. Below is a snippet of the data… this is only showing the 65xx GLs and their total.

GL / SumOfTRNS_AMT
6505 / $2,587,070.14
6510 / $51,447,088.10
6511 / $82,197.19
6515 / $697.93
6516 / $1,313,644.85
6591 / $2,370,956.88

This is what it looked like in Excel. We added FYR as the first column so you can sort easily.

YVCC: General Ledger Totals by GL by FYR
Prepared 11/03/2015 for Auditors
FYR / GL / SumOfTRNS_AMT
11-12 / 6505 / $2,318,067.98
11-12 / 6510 / $55,823,569.40
11-12 / 6511 / $123,765.00
11-12 / 6515 / $2,209.29
11-12 / 6516 / $1,595,387.70
11-12 / 6591 / $2,353,639.00
12-13 / 6505 / $943,218.83
12-13 / 6510 / $57,568,705.09
12-13 / 6511 / $119,810.00
12-13 / 6515 / ($2,995.33)
12-13 / 6516 / $1,420,895.47
12-13 / 6591 / $2,413,500.00
13-14 / 6505 / $2,587,070.14
13-14 / 6510 / $51,447,088.10
13-14 / 6511 / $82,197.19
13-14 / 6515 / $697.93
13-14 / 6516 / $1,313,644.85
13-14 / 6591 / $2,370,956.88

And this is what it looks like with the Excel Pivot Table.

YVCC: General Ledger Totals by GL by FYR
Prepared 11/03/2015 for Auditors
SUM of TRN Amt / Column Labels
Row Labels / 11-12 / 12-13 / 13-14
6505 / 2,318,067.98 / 943,218.83 / 2,587,070.14
6510 / 55,823,569.40 / 57,568,705.09 / 51,447,088.10
6511 / 123,765.00 / 119,810.00 / 82,197.19
6515 / 2,209.29 / (2,995.33) / 697.93
6516 / 1,595,387.70 / 1,420,895.47 / 1,313,644.85
6591 / 2,353,639.00 / 2,413,500.00 / 2,370,956.88

Deposits in Transit (GL 1120)

The auditors wanted to see the Deposits in Transits (checks received in July with June date).

  • We pulled the GL Dbase detail for 1120 for 1213, 1314, and 1415.
  • We exported the data to Excel to show the detail.
  • We ran totals at the point where the opening entries and the entries early July equal zero.
  • We then ran totals at the end of the fiscal year where you are posting the deposits in transit.
  • We then ran total for the opening entries (that matches those at the end of the fiscal year) and then the July entries that then zero these out.

QUERIES FOR IPEDS

Library Expenses

The Library needed the expenses for IPEDS. Per SBCTC, library expenses are GL=65xx and PRG=05x or 15x. We needed to pull the detail for the library and then sum by SOBJ.

Let’s pull the detail:

qry1415 All GLs

GL=65*

PRG=05* or 15*

This is snippet… there are 4,076 lines of expense data.

Let’s pull the sum by SOBJ:

qry1415SUMs

Where GL=65*

Where PRG=05* or 15*

Quickly, you have the data summarized by the subobj.

Using GL Database is fun and easy… data right at your fingertips. 

BAR 101: Keepin’ It Clean with GL DatabaseApril 21, 2016

Clarissa Wolfe, YakimaPage 1 of 7