From the Oct. 2008 Issue
As my friend Greg LaFollette puts it, “It’s a great time to be
in public accounting.”
If you’ve followed The Sleeter Group for long, you know that our group
of more than 600 consultants serve somewhere around 180,000 small and mid-sized
businesses. We focus where the rubber meets the road. That is, we provide the
critical link between what businesses require, what software products can do,
and how the software is used by each client. What makes this field so fun is
working with so many different types of businesses, people and technology solutions.
So yes, it really is a great time to be in public accounting, and I think the
part that is the most fun is accounting software consulting. Specifically, the
area of helping people find the right solutions, and then getting them set up
and teaching them how to properly use the software to manage their business.
What continually surprises me is just how crazy things get when seemingly
simple parts of the software are misunderstood by clients, and how that leads
to “misuse” and often disaster. I suppose it underscores the difficulties
of creating software that is, on one hand, powerful and feature-rich but, on
the other hand, so simple that every user understands how to use it, without
any training or manual-reading. Ha! I’ve spent my lifetime on this topic,
and although we’re seeing great progress, I don’t think we’ll
ever really get there. Consultants who help troubleshoot and fix disasters will
never be out of a job.
Here is a great example from our consultants’ forum that shows how the
Archive Copy in QuickBooks is one of those powerful features that is simple
to use, but when misunderstood can be quite dangerous.
I have a client who had his company archived back in 2002 because of file space.
The person who archived it, however, started using the archive company instead
of the new smaller company file. Is there any way that I can archive this copy
now and possibly get rid of the “Archive Copy” line across the top
without setting up a new company file, or is that my only option at this point?
The short answer is no. The archive copy of the company file will always have
the words “Archive Copy XX/XX/XXXX of Companyname.QBW” at the top
of the window in QuickBooks. You can rename the file on the hard drive, but
you won’t be able to remove the words Archive Copy from the top of the
QuickBooks window for that file.
For the long answer, and for the fix to the problem, you have to go much deeper.
So let’s go on a little journey of why archive files exist, how they’re
created, how they should be used and, finally, how you can unwind a client who
has unwittingly gotten mixed up between their real company file and the archive
of their file.
Archive Files: The Process, Purpose & Problem
When you use the Clean Up Data Utility, QuickBooks “condenses” the
data file by deleting all the transactions you no longer need and replacing
them with monthly summary entries. At the same time, this utility will create
two files. One is an archive of the data file (in QBW format), and the other
is a backup of the file (QBB).
If your company file is called DougsCompany.QBW, then two things happen when
you run the Clean Up Company Data function. First, QuickBooks forces you to
create a backup (QBB) of the file, which will be named DougsCompany (Backup
Dec 31, 2008 2:00pm).QBB. Then QuickBooks creates the “archive file.”
This is a complete copy of the pre-condensed data file, named “Archive
Copy 12-31-2008 DougsCompany.QBW.” Then, QuickBooks continues with the
“condense” process, removing old transactions from the original
file (DougsCompany.QBW) according to the options you select in the wizard.
The whole purpose of the archive file is to provide you with a copy of your
company file as it was before you removed transactions. You can use the archive
copy to view data or run reports for older periods in which transactions have
However, in case it seems that the archive file is the same as a backup copy,
you shouldn’t confuse the two. If you discover file corruption in QuickBooks
that cannot be fixed by other means, you should always restore from the backup
(QBB), not the archive copy. Of course, the QBB and the archive company file
will have the exact same transactions in them (assuming they were both created
during the same Clean Up Company Data session), but the archive data file is
meant to be read-only. In fact, QuickBooks Help screens erroneously say the
archive copy is read-only, but there is nothing that prevents users from entering
transactions into the archive file.
So about now, you might be asking why we have this archive file at all. Since
it’s not read-only and QuickBooks forces you to create a backup file when
you use the Clean Up Company Data function, then why have the archive data file
at all? I suppose Intuit would answer that question by saying it’s about
convenience for the user. It’s much faster to be able to open the archive
file to research older data as opposed to restoring from a backup file.
But the problem with the archive file is that most users don’t really
understand its purpose. And the lack of understanding often leads to misuse,
as evidenced by the original question above. The unfortunate result is that
people get in trouble by entering data into two different files (their main
data file, and the archive file), so that neither of their files have all of
the real data. This situation is really hard to remedy because you have to tediously
go through both files and see which data was entered where and then enter the
missing data into the one you consider your true master data file.
That’s very time consuming and usually very expensive because clients
will either pay overtime or incur big consulting fees. Transaction copier programs
are available from Karl Irvin (see the Data Transfer Utility at www.q2q.us),
and from Big Red Consulting (see Transaction & List Copier for QuickBooks
from www.bigredconsulting.com), but even with these products, the issue is that
a human has to go through and identify which transactions need to be copied
over to the real data file.
PREVENTING THE PROBLEM IN THE FIRST PLACE
Here are a few tricks you can use to prevent the “two files out-of-sync
problem,” or at least reduce the likelihood of clients getting out of
sync between their current, live data file and their archive file. When you
finish the Clean Up Company Data process, open the archive copy and change the
1. Set the color scheme in the file to a different color so that it’s
immediately obvious when you open the file that you’re in the old file.
Go to Edit >Preferences, then click Desktop
View and pick a Color Scheme.
2. Set the closing date to the date of last transaction in the file (or
later). Go to Edit > Preferences. Then click
Accounting > Company Preferences. Then click
Set Date/Password and set the date. This prevents
users from entering transactions before that date.
3. Change the Admin password on the file to something different from your
main file. Make sure you keep the password in a special place so you can look
it up if you forget it.
I know a lot of consultants never use the clean up data feature due to this
and other problems and instead prefer to start over with a new file when the
file becomes too large. If you do use it, make sure you clearly explain to your
clients how the archive file works, why it’s created, and that the client
should never enter data into that file. Also, use our tricks above to help prevent
the clients from getting mixed up between their real data file and the archive
– – – – – – – – – – – – – –
Mr. Sleeter is the founder of The Sleeter Group, a national group of QuickBooks
and accounting software consultants who serve small and medium-sized businesses.
He is the author of several books including the QuickBooks Consultant’s
Reference Guide, and the market leading college textbook “Learning QuickBooks
Step by Step.” For more information, call 888-484-5484 or visit www.sleeter.com.