“Access” Your Microsoft Office Small Business Accounting Data

By David Ringstrom, CPA

As you’re aware, Microsoft Office Small Business Accounting ships with a robust set of customizable reports. But it seems that every client wants something different. Fortunately, you can meet — or even exceed — your clients’ expectations by using the Small Business Accounting Analysis Tools with Microsoft Access. In this article we’ll explore this capability, customize an Access report, and learn how to generate customer mailing labels.

 

Access the Analysis Tools

 

Before you attempt to follow the steps in this article, ensure that you have Microsoft Access installed on your computer along with Microsoft Office Small Business Accounting. The Analysis Tools are automatically installed as part of Small Business Accounting. But you’ll encounter an error message — and be stopped in your tracks — if you attempt to use an Access report without first installing Microsoft Office Access.

 

Small Business Accounting’s integration with Microsoft Access is contained within the separate Analysis Tools application. You can create your first Access report by following these steps:

 

1. Click Start.
2. Choose All Programs.
3. Choose Microsoft Small Business Accounting.
4. Choose Analysis Tools.
5. If necessary, click the Change button, as shown in Figure 1, to select a company. In this article I use the sampleproductcompany.sbc file that ships with Small Business Accounting.
6. Choose Vendor List.
7. Click Display.

 

Timesavers: You can double-click on Vendor List instead of clicking once on Vendor List and clicking Display. You can also typically skip step 5 unless you need to switch to another Small Business Accounting company.

 

Figure 1: The Analysis Tools can provide customizable reports in Microsoft Office Access.

 

When you initiate the Vendor List report, Small Business Accounting establishes an electronic conversation with Microsoft Office Access, which utilizes a preinstalled database for generating reports.

 

Caution: You may encounter a security prompt when you initiate an Access report from the Analysis Tools, as shown in Figure 2. Click Open to allow Small Business Accounting to launch Access and open your report. Permanently eliminate this prompt by choosing “Always trust files from this publisher and open them automatically.”

 

Figure 2: Click Open on the security prompt to view your report in Access.

 

Once the conversation between Small Business Accounting and Access is complete, your report appears onscreen. You can then click the Print button on the toolbar to print the report, or choose File, then Print. Control report options such as page orientation and margins by clicking the Setup button on the toolbar. Or you can choose the button with the Word icon (next to the Setup button) to choose Publish It With Microsoft Office Word or Analyze It With Microsoft Office Excel.

 

Familiar ground: Microsoft Office Access can be daunting to the uninitiated. Fortunately, any Access reports that you create with the Analysis Tools can be sent to Excel — and from there you can reformat the report to your liking.

 

Parallel track: You can create the following Access reports in the same fashion as the Vendor List:

 

• Payment list
• Item list
• Invoice list
• Employee list
• Customer list
• Profit and Loss Standard

 

Customize Reports for Individual Clients

 

If you’re comfortable with Microsoft Access, you can save customized copies of existing reports or even create new reports., I’ll demonstrate only a simple modification to get you started. Let’s change the Profit and Loss Standard report to portrait orientation instead of landscape by following these steps:

 

1. Double-click on Profit and Loss Standard within the Analysis Tools application.
2. If necessary, click Open on the Security Warning prompt.
3. When the report opens in Access, choose File, and then Page Setup. Alternatively, choose Setup from the toolbar.
4. Click the Page tab, choose Portrait, and then click OK.
5. At this point we need to change the width of the report. To do so, click the Design button on the toolbar — it’s the first button on the left. You can also identify the Design button by its triangle symbol, as shown in Figure 3.

 

Figure 3: Click the first icon on the toolbar to enter Design mode.

 

Helpful: You can tell that Access is in Design mode when the report changes from the ready-to-print view to a series of headers and formulas. In addition, Design mode features a small ruler at the top of the report.

 

6. Grab the right edge of the report with your left mouse button and shrink the width of the report from 10 inches to 5 ½ inches by dragging to the left. 
7. Click the View button, which was the Design button. This multipurpose toolbar icon transforms itself each time you click it. You should return to the print-ready format.
8. Choose File, Save As, and then save the report as Profit and Loss – Portrait.

 

Unfortunately, your customized report won’t automatically appear in the Analysis Tools application. However, you can add it by following these steps:

 

1. Launch the Analysis Tools application.
2. Click the Add button, and then choose Access Report.
3. When the Select Access database file prompt appears, browse to the database file listed below, and then click Open:

 

C:\Program Files\Microsoft Small Business\Small Business Accounting\Templates\AnalysisReports\AnalysisReports.mdb

 

4. Click Open if the Security Warning prompt appears.

 

At this point your customized report should appear in the Analysis Tools application. You can now double-click it — just like any of the standard reports — and the report will appear onscreen.

 

Beware: You don’t have an option to select which Access reports to add to the Analysis Tools application. Using the Add button adds your customized reports along with duplicate copies of all of the standard reports. To delete duplicate reports, click on the report name, and then click Remove. Unfortunately you must do this one report at a time, but the removal process goes quickly.

 

Creating Mailing Labels

 

Even if you have no experience in Microsoft Access, you can still create mailing labels from your customer records. To do so, open the AnalysisReports.mdb file — the path is shown in step 3 of the previous section — then carry out these actions:

 

1. Choose Reports from the Objects list, if needed.
2. Click the New button, and then choose Label Wizard.
3. Select dbo_CustomerAccountView from the Table or Query drop down, and then click OK
4. Specify a label format form the list, such as the commonly used Avery 5160, and then click Next.
5. Change the font settings if you wish, and then click Next.
6. When the Prototype Label appears, double-click on Name from the Available Fields list.
7. In the Prototype Label, click on the second line — just below {Name} — then double-click Address from the Available Fields list.
8. In the Prototype Label, click on the third line — just below {Address} — then double-click Address2 from the Available Fields list.
9. In the Prototype Label, click on the fourth line — just below {Address2} — then double-click City from the Available Fields list.
10. Press the comma key, and then the spacebar, to add a comma and a space after the {City} field.
11. Double-click on State from the Available Fields list, and then add a space.
12. Double-click on ZipCode from the Available Fields list. At this point your screen should look like Figure 4.

 

Figure 4: When you’ve finished adding fields, your prototype label should look like this.

 

13. Click Next, specify sorting criteria if you wish, such as ZipCode, and then click Next again.

 

Expert tip: You may be able to receive discounted postage from the U.S. Postal Service if your mailing is presorted in ZIP code order.

 

14. Specify a name for your labels, such as Customer Labels Sorted by ZIP code, and then click Finish. Accept the default choice of See the Labels as They Will Look Printed.
15. Your labels should appear onscreen, ready for printing.

 

Warning: In some cases you may encounter a Some Data May Not Be Displayed prompt. You can click OK and disregard this prompt as long as your labels display properly onscreen.

 

16. To print your labels, choose File, and then Print.

 

Practical: Click the Publish It With Microsoft Office Word icon to send a copy of your labels to Microsoft Word. This will transfer your labels to a Word document, which can be helpful if you need to share the labels with someone else, such as a printing company.

 

Your mailing labels can be added to the Analysis Tools in the same fashion as the customized report that I discussed previously. As an added bonus, once you’ve created a customized report for one company, you’re free to use it with any other Small Business Accounting company.

 

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.