Put a database to work: perform complex jobs with a few clicks.What makes a database so useful is its unique ability to selectively harvest from its vast fields of stored facts just what you need--no more and no less--and then to assemble that information in such a way that you can perform complex and tedious tasks with just a few keystrokes or mouse clicks. 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 au·to·mate v. au·to·mat·ed, au·to·mat·ing, au·to·mates v.tr. 1. To convert to automatic operation: automate a factory. 2. 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 on·screen or on-screen adj. & adv. 1. As shown on a movie, television, or display screen. 2. Within public view; in public. reports, open the database file Cust_Track_2001. Then click on the Forms tab and evoke e·voke tr.v. e·voked, e·vok·ing, e·vokes 1. To summon or call forth: actions that evoked our mistrust. 2. the previously created Ware_House_Ship form by highlighting the selection and clicking on the Open button (exhibit 1, below). [ILLUSTRATION OMITTED] This screen displays a report (exhibit 2, above) for warehouse employees that tells them what orders they need to fill. [ILLUSTRATION OMITTED] 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 des·ig·nate tr.v. des·ig·nat·ed, des·ig·nat·ing, des·ig·nates 1. To indicate or specify; point out. 2. To give a name or title to; characterize. 3. the entire form's properties (exhibit 3, at right) and make sure the box pointed out by the arrow is black. [ILLUSTRATION OMITTED] Select the entire form's properties by clicking here. If this form design toolbar A row or column of on-screen buttons used to activate functions in the application. Many toolbars are customizable, letting you add and delete buttons as required. Toolbars may be fixed in position or may float, which means they can be dragged to a more convenient location in the (see below) is not already displayed, click on View, Toolbars and Form Design as shown in exhibit 4, at right. [ILLUSTRATION OMITTED] Then click on the Properties button to evoke the Form box (exhibit 5, page 71). Click on the Data tab and change the settings from Yes to No for Allow Edits, Allow Deletions and Allow Additions. [ILLUSTRATION OMITTED] 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. Warehouse employees can now use the navigation bar A set of buttons or graphic images typically in a row or column used as a central point that link you to major topic sections on a Web site. If the navigation bar is a single graphic image with multiple selections, it is known as an imagemap. See imagemap. 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 An itemized statement or written account of goods sent to a purchaser or consignee by a vendor that indicates the quantity and price of each piece of merchandise shipped. A consular invoice is one used in foreign trade. 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, page 71). 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. [ILLUSTRATION OMITTED] 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 o·ver·whelm tr.v. o·ver·whelmed, o·ver·whelm·ing, o·ver·whelms 1. To surge over and submerge; engulf: waves overwhelming the rocky shoreline. 2. a. 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 Noun 1. first blush - at the first glimpse or impression; "at first blush the idea possesses considerable intuitive appeal but on closer examination it fails" , 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, below), and click on the New [??] button. [ILLUSTRATION OMITTED] Highlight the Report Wizard option, click on the [??] button to evoke a dropdown menu of available tables and queries, highlight Invoice Query (exhibit 8, above) and click on OK. [ILLUSTRATION OMITTED] 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, page 73) and click on OK. This will take you back to the What sort order and summary information ... screen. [ILLUSTRATION OMITTED] 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 rifle do you want for your report? By clicking on the Finish button, you should get a first rough draft of the invoice (exhibit 10, at right). [ILLUSTRATION OMITTED] View the form by clicking on the Design [??] View button. To give the reader a picture of where we are headed, see exhibit 11, below--a design view of the final invoice. You can refer to this exhibit to be sure you've followed our instructions correctly. [ILLUSTRATION OMITTED] To continue creating the invoice, delete To remove an item of data from a file or to remove a file from the disk. See file wipe, trash and undelete. 1. (operating system) delete - (Or "erase") To make a file inaccessible. the information in the Report Header (1) In a disk or tape file, a set of data that resides permanently at the beginning. It may be used for identification only (type of file, date of last update, etc.), or it may describe the structural layout of the contents, as is common with many document and database formats. section by clicking on the box that contains the word Invoice (see right) followed by the delete key  On computer keyboards, the delete key (sometimes shortened "Del"), should, during normal text editing, discard the character at the cursor's . To eliminate the extra space, place the cursor (1) The symbol used to point to some element on screen. On Windows, Mac and other graphics-based screens, it is also called a "pointer," and it changes shape as it is moved with the mouse into different areas of the application. just above the title Page Header Common text that is printed at the top of every page. It generally includes the page number and headings above each column. 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 In a document or report, common text that appears at the bottom of every page. It usually contains the page number. section. Delete the extra items by highlighting them in the OrderDate Footer, CompanyName Footer, Page Footer A page footer or simply footer in typography is that material which is separated from the main body of text and appears at the bottom of a printed page. Word processing programs usually provide for the creation and maintenance of page footers, which are often the same from and Report Footer (as show in exhibit 12, page 74) 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. 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, page 74. [ILLUSTRATIONS OMITTED] 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 1/2 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 font or typeface or type family Assortment or set of type (alphanumeric characters used for printing), all of one coherent style. Before the advent of computers, fonts were expressed in cast metal that was used as a template for printing. 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 “Tampa” redirects here. For other uses, see Tampa (disambiguation). Tampa is a United States city in Hillsborough County, on the west coast of Florida. It serves as the county seat for Hillsborough County.GR6. 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 subtotal /sub·to·tal/ (sub-to´t'l) less than, but often almost, complete. , tax, shipping and total box (see the OrderDate Footer section in exhibit 11). The business charges 6.5% sales tax sales tax, levy on the sale of goods or services, generally calculated as a percentage of the selling price, and sometimes called a purchase tax. It is usually collected in the form of an extra charge by the retailer, who remits the tax to the government. 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 below. Close the Text Box: by clicking on the X. Click on the copy and paste To copy files from one location to another or to copy text and images from one document to another. All modern operating systems and applications have a copy and paste capability that is typically selected from an Edit menu. See cut and paste and Win Copy between windows. keys to create a new data box as shown below. While the newly created data box is highlighted, click on the properties button [??] and change the name to Tax as below. 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 as needed prn. See prn order. 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 =|If([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 at the right. This will take you to an Expression Builder box. Highlight and delete the formula displayed and click on the Equals button [=]. Scroll To continuously move forward, backward or sideways through the text and images on screen or within a window. Scrolling implies continuous and smooth movement, a line, character or pixel at a time, as if the data were on a paper scroll being rolled behind the screen. See auto 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, above. Then click on OK to accept the formula. [ILLUSTRATION OMITTED] 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 See toolkit and toolbar. 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 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, page 76) and click on OK. [ILLUSTRATION OMITTED] Select the Filter by manufacturer option to match your brand of labels (exhibit 16, at right). 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. [ILLUSTRATION OMITTED] Add the fields to the Prototype label to match exhibit 17, at right and click on the Finish button. [ILLUSTRATION OMITTED] Finally, clean up the City, State and Zip line to include a "-" between the Zip code zip code System of postal-zone codes (zip stands for “zone improvement plan”) introduced in the U.S. in 1963 to improve mail delivery and exploit electronic reading and sorting capabilities. 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 syntax: see grammar. syntax Arrangement of words in sentences, clauses, and phrases, and the study of the formation of sentences and the relationship of their component parts. 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, page 77) and click on OK. [ILLUSTRATION OMITTED] When back at the Report Wizard box, click on the Next button and sort the records by OrderDate as in exhibit 19, page 77, 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. [ILLUSTRATION OMITTED] 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, page 77, including changing the data labels. You should now have a nicely formatted report. [ILLUSTRATION OMITTED] These are just a few of Access's extensive reporting capabilities (on-screen forms, invoice reports, mailing labels and parameter (1) Any value passed to a program by the user or by another program in order to customize the program for a particular purpose. A parameter may be anything; for example, a file name, a coordinate, a range of values, a money amount or a code of some kind. 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. Key to Instructions To help readers follow the instructions in this article, we use two different typefaces This is a list of typefaces. Serif Here you can find a graphical version of this table.
Boldface See boldface font. type is used to identify the names of icons, agendas and URLs. Sans serif Short horizontal lines added to the tops and bottoms of traditional typefaces, such as Times Roman. Contrast with sans-serif.  type indicates commands and instructions that users should type into the computer and the names of files. Articles on Databases This is the fourth in a series of JofA tutorials on how to create and use databases. The previous articles, all by David C. Hayes and James E. Hunton, were * "Building a Database from Scratch," Nov. 99, page 63. * "Working with Databases" May00, page 70. * "When Querying Databases, You've Got to Ask the Right Question," Feb.01, page 35. To demonstrate how to use the database in typical business applications, the authors took readers step-by-step in building a database for a fictitious Based upon a fabrication or pretense. A fictitious name is an assumed name that differs from an individual's actual name. A fictitious action is a lawsuit brought not for the adjudication of an actual controversy between the parties but merely for the purpose of clothing distributor, and in this article they continue to use that database to illustrate other things the application can do. If you wish to follow along in this article using the database created in the earlier articles, download it "Download It" is Clea's debut single. It was released in the UK on September 22, 2003 and missed the top 20 charting at #21. The single had average promotion, being performed in shows like Top of the Pops. from http://ftp. aicpa.org/publli/download/pubs/jofa/cust_track_2001mdb. And if you want to download To receive a file transmitted over a network. In any communications session, "download" means receive, and "upload" means send. The download/upload often implies a big/little scenario, in which data is being downloaded from the "big" server into the "little" user's computer. the completed database, including all the functions created in this article, download it from http://ftp.aicpa.org/public/download/pubs/jofa/cust_track_ 2001B.mdb. To use this database, you must be running a version of Microsoft Access A database program for Windows, available separately or included in the Microsoft Office suite. Access is programmable using Visual Basic for Applications (VBA). Access can read Paradox, dBASE and Btrieve files, and using ODBC, Microsoft SQL Server, SYBASE SQL Server and Oracle data. as recent as the 2000 edition. An Invitation If you have a special how-to technology topic you would like the JofA to consider for inclusion in this series, or an application shortcut (1) In Windows, a shortcut is an icon that points to a program or data file. Shortcuts can be placed on the desktop or stored in other folders, and double clicking a shortcut is the same as double clicking the original file. you devised and would like to share with other professionals, contact Senior Editor Stanley Zarowin. His e-mail address See Internet address. e-mail address - electronic mail address is zarowin@mindspring.com. DAVID C. HAYES, CPA (Computer Press Association, Landing, NJ) An earlier membership organization founded in 1983 that promoted excellence in computer journalism. Its annual awards honored outstanding examples in print, broadcast and electronic media. The CPA disbanded in 2000. , is an accounting doctoral student at the University of South Florida • • [ , Tampa. His e-mail address is dhayes@coba.usf.edu. JAMES E. HUNTON, CPA, PhD, is the Quinn Eminent Scholar of Accounting Information Systems at the University of South Florida. His e-mail address is jhunton@coba.usf.edu. |
|
||||||||||||||||||
Printer friendly
Cite/link
Email
Feedback
Reader Opinion