Put a Database to Work

This article will demonstrate some of the time-saving tasks a database can do. We will use the same example—a clothing distributor—that we used in the prior tutorials, tapping data created in those articles. We will show how to design automated information reports, customer invoices, address labels to mail those invoices and profit-margin reports.

ACCESSING DATA

Since we’ve already designed the queries needed for on-screen reports, open the database file Cust_Track_2001 . Then click on the Forms tab and evoke the previously created Ware_House_Ship form by highlighting the selection and clicking on the Open button ( exhibit 1 ).

Exhibit 1

Key to Instructions To help readers follow the instructions in this article, we use two different typefaces. Boldface type is used to identify the names of icons, agendas and URLs. Sans serif type indicates commands and instructions that users should type into the computer and the names of files.

This screen displays a report ( exhibit 2 ) for warehouse employees that tells them what orders they need to fill.

Exhibit 2

Data protection. Since the report is available to all the warehouse staff, you may wish to protect the underlying data. To do that, click on the Design View button , then on the section to designate the entire form’s properties ( exhibit 3 ) and make sure the box pointed out by the arrow is black.

Select the entire form’s properties by clicking here.	Exhibit 3

If this form design toolbar (see below) is not already displayed, click on V iew, Toolbars and Form Design as shown in exhibit 4 .

Then click on the Properties button to evoke the Form box ( exhibit 5 ). Click on the Data tab and change the settings from Yes to No for Allow Edits, Allow Deletions and Allow Additions.

Exhibit 4		Exhibit 5
		Close the Form box and test the changes by clicking on the View button . Now you shouldn’t be able to change the underlying data.

Exhibit 6

Warehouse employees can now use the navigation bar at the bottom of the form to advance to the customer Fly-by-the-Seat of Our Pants by clicking on the advance-record button on the bottom left; that will move them to the next record. The warehouse employees can then select the 12/17/99 invoice by clicking on the other advance-record button under the Select Order Date to Fill. This should display the 12/17/99 order for the warehouse employees to fill ( exhibit 6 ). The on-screen report directs the employees to the shelf location—aisle 1 for the 10 pairs of jeans and aisle 2 for the 15 shirts.

When finished, close the form by clicking on the bottom X in the top-right corner of the form, and save the changes by clicking on Yes when prompted to save changes.

DESIGNING INVOICES

Once an order is filled, we want Access to create a report that will be an invoice for that order.

Don’t be overwhelmed by the number of steps needed to design the invoice. Most of the instructions, as you’ll see, are only cosmetic—a more attractive placement of type. On first blush, the instructions seem complex, but once you’ve designed a few forms in Access, you’ll realize the steps are actually very intuitive and easy.

Let’s get started. Begin by clicking on the Reports tab (under Objects ), then highlight Create report by using wizard ( exhibit 7 ), and click on the New button .

Exhibit 7		Exhibit 8
Highlight the Report Wizard option, click on the button to evoke a drop-down menu of available tables and queries, highlight Invoice Query ( exhibit 8 ) and click on OK .

Since we need to access all the data in the query, we will move all of the fields from the Available Fields to the Selected Fields by clicking on the Move all button and then clicking on Next . When asked How do you want to view your data? accept the prefilled selection of by Customers by clicking on the Next button . To ignore the grouping (a function we’ll use later), click on the Next button when asked Do you want to add any grouping levels? Since we do want to total the invoices, click on the Summary Options button, check the Sum box next to Extended Price ( exhibit 9 ) and click on OK . This will take you back to the What sort order and summary information… screen.

Click on the Next button, then accept the preselected layout of Stepped by clicking on the Next button. Now accept the prefilled Corporate style by clicking on the Next button again. Change the prefilled Customers to Invoice when asked What title do you want for your report? By clicking on the Finish button, you should get a first rough draft of the invoice ( exhibit 10 ).

Exhibit 9		Exhibit 10

Exhibit 11

View the form by clicking on the Design View button . To give the reader a picture of where we are headed—see exhibit 11 —a design view of the final invoice. You can refer to this exhibit to be sure you’ve followed our instructions correctly. To continue creating the invoice, delete the information in the Report Header section by clicking on the box that contains the word Invoice (see below) followed by the delete key.

To eliminate the extra space, place the cursor just above the title Page Header until it changes to a double-ended arrow, hold down the left mouse button and drag the Page Header section up to the Report Header section. Next delete the extra items provided by the report wizard. Be careful not to delete the Sum and =Sum([Extended… box in the OrderDate Footer section. Delete the extra items by highlighting them in the OrderDate Footer, CompanyName Footer, Page Footer and Report Footer (as show in exhibit 12 ) and holding down the Shift key, clicking in the boxes and pressing the delete key. An easy way to select several items is to hold down the left mouse button and draw a box around the items to select them.

Exhibit 12

Now eliminate the extra space in the Report Footer, Page Footer and CompanyName Footer with the same technique used in the Report Header above. When finished, the form should look like exhibit 13 .

Exhibit 13

Now we want to move the order detail information (Company Name, etc.) from the Page Header section to the OrderDate Header section. To make room for that, move the detail bar down about 1 12 inches by placing the cursor just above the detail bar, left-clicking when the double-headed arrow appears and pushing the detail bar down.

Highlight the box OrderDate in the OrderDate Header section and drag it to the bottom of the OrderDate Header section. Highlight all the boxes in the Page Header section and click on the Cut button .

Click on the OrderDate Header bar and the Paste button to move the order detail information to the OrderDate Header section. Move the order detail to just above the OrderDate box (refer to exhibit 11 for placement).

Eliminate the extra space in the Page Header as you did in the Report Header process above. Now delete the Company Name box from the OrderDate Header section (not the CompanyName Header section). Move the remaining Company Name box from the CompanyName Header section to the OrderDate Header section and close the CompanyName Header section.

Next add the company information in the OrderDate Header . Click on the Label button , change the font size to 12, and click in the white space just below the OrderDate Header bar. Type the name of our business ( Wholesale Clothes ). In a similar way, add our address ( 4202 East Fowler, Tampa, Florida 12345-6789 ) and two label boxes with Sold To: and ***Invoice*** just below our company address ( exhibit 11 for placement). On occasion, click on the Preview report button to see the changes made.

Format the purchase information and arrange the data and label boxes to match those in exhibit 11 . Highlight the Product Name label box in the OrderDate Header section and make it bigger and center the information by clicking on the Centering text button like this:

Add the word Ordered in the Quantity label box. In a similar way, move Order Date, Product Name, Product Description, Quantity Ordered, Sale Price and Extended Price data and label boxes to match the placement in exhibit 11 .

INSERTING FORMULAS

Set up the invoice’s subtotal, tax, shipping and total box (see the OrderDate Footer section in exhibit 11). The business charges 6.5% sales tax on all orders and 5% shipping for all orders less than $1,000.

Change the word Sum to Invoice Subtotal and move it closer to the =Sum[Extended] data box. Make more room in the OrderDate Footer section by moving down the CompanyName Footer bar at the top.

Add a label box and type Sales Tax 6.5% . Highlight the =Sum[Extended] box and click on the Properties button . Click on the All tab and change the name from Sum Of Extended Price to InvoiceSubtotal , as shown at right. Close the Text Box: by clicking on the X .

Click on the copy and paste keys to create a new data box as shown at left.

While the newly created data box is highlighted, click on the properties button and change the name to Tax as shown at right.

Now click in the Control Source line and type =[InvoiceSubtotal]*0.065 as shown below.

Close the text box: by clicking on the X .

Preview the invoice by clicking on the Preview report button . Adjust the box widths as needed to display all the data by clicking on the Design View button and adjusting the box widths.

The next step is to create the shipping line. Highlight both the Sales Tax label and data boxes and perform a copy and paste. That action will produce a copy of the text and data box directly below the original.

Change the new label from Sales Tax 6% to Shipping 5% . Highlight the shipping data box and click on the Properties button. Change the Name to Ship and the Control Source to =IIf([InvoiceSubtotal]>999,0,0.05*[InvoiceSubtotal] ).

To get customers to consider placing larger orders, create a label and type Orders of $1,000 or more receive free shipping ( exhibit 11 for placement). Now click on the Line button and draw a total line below the shipping data box. Highlight both shipping boxes and perform a copy and paste, as you did above and change the Shipping 5% label to Invoice Total . Highlight the Invoice data box just created and click on the properties button.

Another way to change the Control Source information is to put your cursor in the information and then click on the three dots that appear below.

Exhibit 14

This will take you to an Expression Builder box. Highlight and delete the formula displayed and click on the Equals button . Scroll down the middle column until you see InvoiceSubtotal and double-click on it. Click on the add button , double-click on Tax and the add button again, and double-click on Ship to get the same formula as in exhibit 14 . Then click on OK to accept the formula. Now close the Properties box and add two total lines below the total invoice data box. Add a label box and type We appreciate your business below the invoice total.

Since we want each of the invoices to print on a new page, we must add a page break. Click on the Page break button in the toolbox and put the break in the left margin, below your last message in the OrderDate Footer section (see the “……” in exhibit 11 ). Your invoices should now be complete. Exit the report and save when prompted.

MAILING LABELS

Exhibit 15

Next we will use the report wizard to design mailing labels. First highlight the Create report by using wizard option and click on the New button. Highlight the Label wizard option and choose the Customers table ( exhibit 15 ) and click on OK . Select the Filter by manufacturer option to match your brand of labels ( exhibit 16 ). Change the Unit of Measure and Label Type options to match your labels. Scroll through the options to find your product then click on the Next button. Set the fonts and colors you desire and click on the Next button.

Add the fields to the Prototype label to match exhibit 17 and click on the Finish button.

Exhibit 16		Exhibit 17

Finally, clean up the City, State and Zip line to include a “-” between the Zip code numbers. Click on the Design view button . Edit the bottom line to match the following: =Trim([City] & “, “ & [StateOrProvince] & “ “ & Mid([PostalCode],1,5) & “ - “ & Mid([PostalCode],6,4)). The “Mid” command returns a number of characters from a text string that you specify. The syntax is Mid([Text], Start #, # of Characters). Close the report and save it when prompted. Your mailing labels should now be ready to print.

TRACKING PROFITS

Small businesses need to keep a close watch on profit margins, so we’ll design a report that provides weekly information on that information. We’ll use a query, Q_Net_Income_Down , that we designed in the February 2001 JofA database article.

To access it, click on the Reports tab and highlight Create report by using wizard and click on the New report button . Select the Report Wizard, choose the query Q_Net_Income_Down , and click on OK . Use the Move all button to move all the information to the Selected Fields side and click on the Next button. When asked Do you want to add any grouping levels? double-click on OrderDate, then click on Grouping intervals. Change the interval from Month to Week ( exhibit 18 ) and click on OK .

When back at the Report Wizard box, click on the Next button and sort the records by OrderDate as in exhibit 19 and again click on Next . Leave the Layout as stepped , but change the orientation from Portrait to Landscape and click on the Next button. Accept the prefilled Corporate style by clicking on Next . Finally, change the name of the report to Profit Margins and click on the Finish button.

Exhibit 18		Exhibit 19

You will be asked to input the beginning sales date: Type 1-01-99 and click on OK . Type 12-31-99 as the sales ending date and click on OK ; type Jeans and click on OK . This will get you to the first rough draft of the report.

Notice that the numbers listed in the left margin are the weeks of the year. In week 51 we had three sales of jeans. To edit the appearance of the report, click on the Design view button . Arrange the text and boxes to resemble that of exhibit 20 including changing the data labels. You should now have a nicely formatted report.

Exhibit 20

These are just a few of Access’s extensive reporting capabilities (on-screen forms, invoice reports, mailing labels and parameter reports). As you can see, databases are powerful tools that can help maximize the value of your business information. Admittedly, they take time to learn, but once you master them, they can save you loads of time and make your work run more smoothly.