There must be more than the 2 you have listed.

AP balance is 281.43 more than the list of AP outstanding - either an invoice (or several) that adds to 281.43 was not entered in history mode or the AP balance account was unlinked to make an entry without going thru A/P modules or you posted a purchase invoice right when there was a power outage etc.

To look for the difference - how about exporting AR balance account to Spreadsheet at a specific date and put in alpha order to match against aged AP at same date.

Or type in the Quick Search bar the amount of 281.43 to see if that pops up anywhere.

Same goes for Vac pay balance which is 381.20 less than the total of all employees outstanding vac pay.

You must be out 99.77 somewhere in the integrity check box - maybe AR?

I presume you mean ..exporting AP balance account...',

reports->financials->GL for the AP account (2100) for this fiscal year as of today

export to excel, sort by comment (vendor) and compare the open balance for each vendor to

reports->payables->aged overdue payables as of today (summary)

I ran a balance sheet as of May 1,2007 (first date) - ap = 42,000

aged overdue payables as of same date = 0

I presume this means they entered the account balances in historical mode, but not the outstanding invoices

could this be the root cause of the data integrity mismatch

doesn't dbutil rebuild this ?

If details have been cleared, or customers / vendors deleted, you won't be able to run past period reports to check them against the GL.

I don't think they could have gotten out of historical entry mode with a $42,000 imbalance, possibly they didn't set the payables up until a later period.

ok.. so I ran an AP ledger report for the past fiscal year, there's a vendor with two transactions (250$ payment in Jun 2013 and 1000$ invoice in Mar 2014) - but the AP aged payable summary only shows the 1000$ invoice

as outstanding

the payment is applied to an invoice in the prior fiscal year

I'm concerned that comparing the totals of these two reports by vendor (GL ledger for the AP account and AP aged payable summary) won't allow me to find the data integrity error(s)

or am I using the correct reports, just totaling incorrectly ?

If prepayments made and linked to a prepayment account nbr then the formula seems to be Aged payable total less outstanding prepayments equals 2100 Acct Pay. I wonder if you have a prepayment account that would tie into your situation.

Also if invoices were posted as purchases with payment then it will not show up in the 2100 acct. Have you been able to double click on a few invoices to see how it was posted?

If I remember correctly one cannot advance into a new fiscal year with an imbalance in data integrity (at least this should not be allowed).

doesn't dbutil rebuild this?

Dbutil can only work with what's there, the priority with most any database tool is getting the database from hopelessly broken, into a state where it can at least be worked on / opened in the application.

If some portion of a data item is scrambled, for instance the record ID of a payment in A/P doesn't match any of the invoice records, dbutil may remove the rest of that record in the A/P subledger table, but since there was nothing wrong with the payment entry in the journal table, the entry there stays put.

Dbutil will turn an inconsistent database into a consistent one, but it can't conjure up missing information.  Pay careful attention to messages in the database utility log, sometimes there's a hint in there of what's gone wrong.

You could try to 'drill down' from each AP entry in the detail aged report, back to the G/L entries, to se if there are any 'orphan' entries, but going the other way is much harder.   Also, if you have older backups, you could restore them to a temporary location to see when they were last consistent.

there is no 'prepaid' GL, nor is there a linked prepaid acct under setup - don't think that's it

a search for  281.43 returns a Nov 2011 payable invoice - I can see the invoice under the vendor and I can see the JE for the same amount (and it not reversed) - so I don't think that's it

I've exported the AP aging and the AP account entries for the past two years to excel - and they're all accounted for

according to data integrity, debits = credits, so it must not be a corrupted transaction

any other ideas ?

Sage 50 uses the MySQL database as a 'data store' and there is no 'referential integrity' built into the database.  What that means, in English, is that if something goes wrong in the program, it can store data that isn't consistent.

If there was an issue in a journal table - tJourent, tJently, etc. there would be a debits:credits mismatch, which you don't have.

Simply Accounting / Sage 50 stores data in subledger tables, much like the paper ledgers from the olden days.  Like I said at the start of this post, if something goes wrong, wrong data can get stored.   The payable data is stored in two tables - tVenTr and tVenTrDt  (invoices, and detail transactions).  

If the A/P tables are not lined up with the General Journal table, the system will report a mis-match.  The Advanced Database Check may not find or fix it, or may report in the log that there is a problem, but not fix it.

One test you could do, would be to make a temporary copy of the data to another file (NEW.SAI) and go through it and 'pay' all the payables.  See if this has any effect on the data mismatch problem, and / or if there is an amount showing to pay that doesn't show on any aging reports.  

Another thing to try, is to run a detailed aging report for '999' days for all you vendors, and see if it adds up to the same as a report for zero days.  If not, vary the number of days until you find the point where the totals change.  (i.e. if there is a difference between the total balance owing for '750' days and '500', and '500' is the same as '0', try 625 days)

I did the detailed aging report, the balance is the same

0 154660.89

999 154660.89

ap balance 154942.32

how do I access the advance database check, I can't find it under maintenance ?

database utilities only has repair security and compact database

I will try the 'pay all' idea

using copied company, I paid all suppliers (including inactive) - supplier aging balance = 0

AP account 281.43

so now what ?

Advanced Database Check only shows in the menu when you are logged in as 'sysadmin', in Single User Mode.

What to do now?  I think it's more likely, than not, that the Nov 2011 invoice, or a related transaction such as an auto-generated credit note from deleting a payment, or a payment posted as a deposit, is the problem.  

If you run an October 2011 aging report (including inactive customers) and a December 2011 aging report, how do those match up against the G/L as of those dates?

Randy, thanks for the suggestion, it took a while but I found it

Apr 30, 2010 - year end adjustment JE

db prepaid expenses 731.28

cr ap                           281.43

cr retained earnings      449.85

I'm not sure why 'search' doesn't find it ???? maybe it can only search 2 years ? or ???

I'm also not sure how to fix the imbalance ? unlink ap account, debit ap, credit retained earnings in this FY ?

now to deal with the vacation payable discrepency - is there one payroll report that will show me what is payable ?

the employee detail report allows me to show vacation earned & paid - I presume the difference, if any, is recorded under vacation payable ?

I would suggest running a general journal report one year at a time, and search it for the Vacation Pay G/L account.  Given what you found in the A/P account, I wouldn't be shocked if someone had entered a payroll accrual / adjustment to the G/L account, when it should have been applied to one or more employees.

There doesn't appear to be any report that will show the ending payroll vacation pay balance.  (odd?), although you can view it in the employee record.

You may be able to narrow the search down to the period where it went off the rails, by running reports of the vacation pay earned and paid, and looking at the net amount there, versus the net change in the G/L balance.

I really don't 'get' why some accountants pass piddling amounts like this on to their clients as year end adjustment entries - and worse - plow them into the G/L without recording them in the subledger.  

Randy

I exported all JEs for all years, looking for the vacation payable difference of 381.20, not found

then I compared employee detail (with vacation columns) with the vac payable GL balance, for the last three fiscal years - the employee details match the GL balance

I'd compare more, there are 7 years of JEs, but only three years of employee data - so if I double click on a payroll JE, it opens the payroll journal. if I double on the JE from there it will open the paycheque

until I get to 2010, instead of opening the paycheque, I get no data found - maybe the client cleared employee data, maybe a version update did so - I don't know

I guess the solution is to create a JE to bring the GL balance in line with the employee details

   db misc expense  381.20

   cr vacation payable  381.20

as far as the AP issue, I think accountants know what subledger needs to be updated - but in the preparation of the financial statements they lump it under AP. It's up to the person recording the YE adjustment to request the subledger information

the solution to this one, I guess would be

 db ap                281.43

 cr  misc exp        281.43

both the ap and vac payable accounts will need to be unlinked to do this

You said 'a search for  281.43 returns a Nov 2011 payable invoice - I can see the invoice under the vendor and I can see the JE for the same amount (and it not reversed) - so I don't think that's it'.  Is that JE the same as the one you found for Apr3/10?

I would suggest you reconcile the prepaid expenses first as that may be the account you would credit - not RE.

I hope I haven't sent you on a snipe hunt, if your Edition of Sage 50 supports it the same as in Quantum, there are separate reports for General Journal and the Payroll journal.

There could be multiple entries, which is why I suggested looking in the General (not Payroll, not all journal entries) Journal for the G/L account (by number) rather than the dollar amount, i.e. pressing control-F, and searching for '2295'

(In Sage 50 Quantum Edition, there are separate reports for entries into the journals from the General, Account Reconciliation, Deposit Slips, Purchases, Payments, Sales, Receipts, Payroll, Bill of Materials & Item Assembly, and Inventory Adjustments)

no, there are two 281.43 transactions, one in Nov 2011 and one in Apr 2010 - the search function only shows one - I'm not sure why ?

the apr2010 accountant adjustment entry already cleared the prepaid expense - thus my thinking to credit RE

I would imagine that the one that was posted through the G/L entry screen, was supposed to have been entered through an invoice entry screen.

Second best would have been recording a reversing entry as of the beginning of the next period, and that entry is missing.

As you say, it now would have to go directly against Retained Earnings since you can no longer access the following year to have it go through the regular process.

Yes - I agree with Randy - another JE to A/P and RE - you will need to unlink AP account to do the JE and then link it back up again. That should bring the A/P ledger in balance with the sub-ledgers.

Then the same thing with vac pay - unlinking and then a JE to credit vac pay and debit RE, relinking as you've previously said and that should bring the vac payable ledger in balance to the total of employees vac pay owing. Hopefully your data integrity will now say 'Data OK'.