This problem is a continuation of Problem 5-1.

a. “Search” the database for all customers with a specific state (choose a state that is common to at least two but not to all of your customers). Obtain a printout of your search algorithm and a list of customers whose records met the search parameter.

b. “Sort” the database in descending order of credit limit amounts. Obtain a printout of your sort algorithm and a printout of the sorted list of customers.

c. Create a “Customer Status Report” (the report title). Observe the following specific requirements: (1) Provide column headings, in left-to-right order, for customer name, credit limit, accounts receivable balance, open orders, and back orders. (2) For each state, print subtotals of the accounts receivable balance, open orders, and back orders columns.

These problems should be completed with a database software package, such as Access. For Problems 5-1 through 5-3, you may use data that you (or your instructor) have downloaded from an accounting database. Problem 5-4 provides an alternative to Problems 5-1 through 5-3 by using the database structure and sample data from Figure 5.10. This problem also may be completed using the software of your choosing.

Data from problem 5-1

Before starting this problem, you should consult the customer master data record layout in Figure 5.2.

Using the database software indicated by your instructor:

a. Create the “structure” for the records in the customer data. Use Figure 5.2 as a general guide to the data elements to be included in the customer records. However, observe the following specific requirements:

(1) For the customer address, use five separate fields, one each for street address, additional address information (e.g., apartment number), city, state, and ZIP code.

(2) Provide for three additional data elements that are not shown in Figure 5.2 (because they normally would be accessed from other files)—open sales orders, back orders, and accounts receivable balance.

b. If the software package supports a function to design input screens, create the screen format to be used for entering customer data.

c. Create example customer records and enter them into the database. Use a variety of names, street addresses, states, ZIP codes, open sales order amounts, accounts receivable balances, and credit limits. (The number of records will be indicated by your instructor.)

d. Obtain a printout of the database records.

Figure 5.2

Transcribed Image Text:

FIGURE 5.2 Record Layouts Under an Applications Approach to Business Event Processing These fields repeated for each item ordered ** These fields repeated for each shipment (a) Sales order record layout (partial) Sales order number Sales order date Customer number Customer order number Sales territory Sales- Item Item person number name Price Quantity Quantity ordered shipped shipped Date Item number Item name Unit cost Unit price Quantity on hand Sales order number Customer Customer Sales- Quantity Quantity number territory person ordered Date shipped shipped These fields repeated for each sales order **These fields repeated for each shipment Assumes average or standard cost (b) Inventory record layout (partial) Customer number Customer name: Customer Credit Sales Sales- address limit territory person Year-to-date sales (c) Customer record layout (partial) Links to primary keys Same (or similar) data names (and same data?) Primary key