How To Check Control Accounts In Resource 32000
Resource 32000 stores both control account values and nominal ledger values in the same table, “Detail?. Therefore the scope for the two to get out of step is fairly limited, and revolves around such possibilities as:
- A control account entry to a different nominal code from the expected one.
- Entries to the control nominal code which are not control entries. (eg manual journals)
- Entries to the Detail table not reflected in the NominalSummary (TB) table. This would manifest itself usually as an out of balance TB, but might not if due to a server failure transaction processing had failed.
- Postings have been made to a future period (perhaps by accident). Clearly these postings will not reflect in a current trial balance.
- Postings have been made to a past year (perhaps by accident). These postings will not reflect in the current year until year end in the past year has been run.
- An account (customer/supplier/etc) has somehow been deleted while it has a balance. Resource 32000 does not allow this but other mechanisms for deleting accounts exist. Or a database failure might have removed an account. Such an account would not show on ledger reports, but postings which had been made to it would still appear in the TB and on NL detail reports.
- Resource 32000 started life out of step, due to converted data from a previous accounting package, either Resource 3/4/5 or another 3rd party package.
This document explains how to check the movements on control accounts in Resource 32000. Hence there is a supposition that at some time in the past the control account balanced with the nominal ledger, and we are now trying to work out how it has become out of step.
- The following query summarises the periodic movement of the sales ledger:Select year,period,sum(basevalue) from detail where ledgercode=’SL’ and controlentry<>0 group by year,period order by year,period
The results from this query can be compared with the period-by-period balances on the nominal ledger account (from 1b below) (or accounts in a multi-account ledger.) Any period which differs may be explained by one of the queries below. The results from this query do not by themselves tell you what the problem is, but they may show you which period(s) it is in.
- To get the Periodic trial balance movement figures for one account more easily than printing lots of TBs use the following query:Select year,period,value from nominalsummary where nominalcode=’1500’ and budgetcode=’#’ order by year,period
- To check that the detail postings to that account are correctly reflected in the trial balance use the following query:Select year,period,sum(basevalue) from detail where nlcode=’1500’ group by year,period order by year,period
Subject to opening balance considerations (period 0) reports 1a and 1b should supply the same results. If they do not then the period in question needs to be rebalanced, if it has not been closed. Such an imbalance can only arise from server-side failure of your database.
- This query summarises the nominal accounts that sales ledger control entries have been made to:Select nlcode,nldepartment,sum(basevalue) from detail where ledgercode=’SL’ and controlentry<>0 group by nlcode,nldepartment order by nlcode,nldepartment
Hopefully this query only returns one row, or multiple rows all with the same nlcode value. If it returns any unexpected value of nlcode (ie not a control account for the ledger in question) then you have your answer, which is that one of the accounts in your ledger is posting or has posted to a different nominal control account.
- You may want to check if any entries have been made to nominal account ‘1500’ (for example) other than its SL control entries. This query supplies a list of such entries:Select ledgercode,nldepartment,basevalue,ref1,transtype,applieddate,year,period,transactionid, id from detail where (ledgercode<>’SL’ or controlentry=0) and nlcode=’1500’
If this query returns any rows at all then you have your answer, which is that non-control postings have been made to your control account. Probably it is not locked (or at some time in the past was not locked) against manual postings.
Clearly when using the above queries you may want to change the ledger code ‘SL’ to the ledger of your choice, and the account ‘1500’ to the appropriate control account in your system.