My name is Salam Abdulhussein and I need this to be done as soon as possible. Please follow the steps one by one and make sure everything is correct

Global Bike, Inc. ERP for Sales / Collection USING ACCESS DATABASE PROGRAM Purpose of the project The purpose of this project is to extend the principles learned in the Excel project to a relational database application. This project creates an enterprise resource management (ERP) system subcomponent, that of the sales to collection business process. This project will result in the development of a set of tables and forms that model in simplicity, the very complex application that comprises the SAP enterprise resource management set of applications. Desired learning outcomes Once you have completed this project you will be able to: 1. describe transactions in terms of the resources, events, agents (REA) model; 2. model a business transaction in a relational database application; 3. create forms that allow the entry and editing of business information; Review of the REA model The \"Resources, Events, Agents,\" (REA) model is used extensively in the design and application of accounting information systems and enterprise resource management systems. REA was originally proposed in 1982 by William E. McCarthy as a generalized accounting model, and contained the concepts of resources, events and agents (McCarthy 1982) i. The REA model replaces several accounting objects that are not necessary in the computer age. Many accounting students initially find this replacement troubling, as the most basic accounting concepts, those of debits, credits, and ledger accounts, are no longer used. In their place, are computer-generated structures of forms, queries, and reports. In an accounting system based on the REA model, the computer is used as a virtual representation of the business. This is accomplished by creating computer objects that represent real-world-businessobjects. By definition, the REA model is an \"ontology ii\" in that it comprises a specification of a conceptualization, defined as three inter-dependent objects: 1 goods, services or money, i.e., resources 2 business transactions or agreements that affect resources, i.e., events 3 people or other human agencies (other companies, etc.), i.e., agents Whereas conventional accounting terms such as asset or liability are defined in terms of concepts, the REA objects are defined as real-world objects. For example, the common accounting asset, goodwill, is not a resource in the terms of the REA ontology. 1 Using REA, each business process in a company is separately modeled. A business process is generally defined as a functional department, or a business function as defined in Porter's \"value chain.\" Some examples of business processes are sales, purchases, manufacturing, human resources, and financing. In each REA model there is a pair of events, linked by an exchange relationship, referred to as the "duality" relation. One of these events represents a resource being used or transferred out, while the other represents a resource being received. For example, in the sales process, one event would be "sales"where goods are given upand the other would be "cash receipt", where cash is received. These two events are linked; a cash receipt occurs in exchange for a sale, and vice versa. This is illustrated in the diagram below: Resou rces Figure 1 The Sales - Cash REA Model M Invent ory Even ts M Sales Order M Age nts M M 1 Custo M M Sale M mer (deliv M 1ery) 1 duality M 1 M Emplo Cash 1 M Cash yee on Receip M 1 Hand t 1 This project demonstrates a step between the conventional manual accounting system and the full implementation of an accounting information system based on the REA model. This project will retain the use of debits and credits while dispensing with the use of ledger accounts. This project will comprise the following steps, in order: 1. 2. 3. 4. 5. 6. using Microsoft Access, create the tables necessary to maintain records of customers; create and use forms for editing the data in the customer records; create inventory tables and forms for maintaining inventory data; create tables for employees; create tables to hold information about cash and cash receipts; create relationship tables and relationships among tables. 2 Accounting for GBI Using Access CREATING THE CUSTOMER TABLE The first table in the sales/collection process we will describe is the customer table. The customer table provides a central location for storing all information about each customer. This makes adding, deleting, displaying, or changing customer information easy and efficient. The customer table needs a primary key field that uniquely identifies each row in the table and exists for every row. To ensure that each CustomerID is unique, many firms assign a sequential number to each new customer. A sequential number scheme gives each new customer a number that is one greater than the largest customer number that currently exists. In Exercise 1, you will begin creating a Customer table called tbl-GBICustomer. You will begin by opening a new table and create its primary key, CustomerID. Global Bike, in this exercise, will a five-digit sequential numbering scheme for its customer numbers, starting with 10001, which can accommodate up to 89,999 customers (10001 through 99999). figure 1.1 tbl-GBICustomer 3 EXERCISE 1. CREATING THE CUSTOMER TABLE Start Microsoft Access, Click on the \"File\" tab (The Windows button in Access 2007). Select \"New\" from the menu. Select \"Blank Database.\" Type a file name for your database with your last name and first initial, i.e. Green_R_GlobalBike_Spring_2014 Click \"Create.\" Click the Create tab, click Table Design from the Tables group, and click the Design View command in the Views group. Access asks you to name the table. Type tblGBICustomer in the Save As dialog box and then click OK to save the newly named table. Access displays the table, as yet undefined, in Design view. Type the name of tblGBICustomer's first field, CustomerNo, and press Tab. When you press Tab, the first row in the Data Type column changes into a combo box, and the Field Properties list appears in the bottom pane of the Table window. Notice, by default, that the first row has a small key in the record selector column. The key indicates that CustomerNo is the table's primary key. You can set the primary key manually or change it to a different column. CustomerNo is, in fact, the table's primary key column, so leave it as is. Also note that the Indexed property sets itself to Yes (No Duplicates). Since you will not be using the CustomerNo field in any calculations, set its data type to Text. The Data Type combo box contains Text as its default selection for all fields other than the initial field named ID. Press Tab to accept the Text default selection. You can use the Description column to store a description of the CustomerNo field if you wish. The Description column is a builtin documentation tool that is especially useful for storing explanations of complex or potentially confusing fields. Press F6 to switch to the Field Properties pane. The cursor will highlight the default Field Size value of 255. Type 5 in the Field Size column to replace the default selection. Press the Tab key three times to move to the Caption property and type Customer # (including the space). This Caption property setting will cause Customer #, rather than the field's name, CustomerNo, to appear as the default text for form and report controls that reference the field. Creating a useful Caption property setting can be a time saver when building reports and forms that use table fields. The panel on the right side of the Field Properties pane provides detailed instructions for each step in the propertysetting process. Figure 1.1, below shows the new Customer table with the CustomerNo primary key field and the caption setting in Design view. 4 EXERCISE 2 SETTING INPUT MASK AND VALIDATION PROPERTIES FOR tblGBICustomer Open tblGBICustomer in Design view and click CustomerNo. Press F6 to switch to the Field Properties pane, and then press Tab twice or click the Input Mask property box. Type the expression 00000;0;_ in the Input Mask box to prevent a user from entering anything other than exactly five digits in the CustomerNo field. Click the Datasheet View icon. Click Yes to save your changes to the table. Try typing in letters or symbols to test that the input mask will not allow them. Now type 1234 and try to Tab to the next field. Access generates an error message to let you know that the value you typed is inappropriate for the input mask specified for this field. Press ESC twice to delete the new record. Return to Design view by clicking the Design View icon on the ribbon and Click CustomerCreditLimit. Then click the Validation Rule property box. Type LL in the Input Mask property box. The > symbol converts lowercase letters to uppercase letters. The LL placeholders in the expression specify that the entry must contain exactly two letters. Move your cursor to the Validation Rule property box. Type NOT (\"AK\" or \"HI\") in the Validation Rule property box. This rule disallows entering either AK or HI for CustomerState. The parentheses apply the \"NOT\" to everything within them. Click the Validation Text property box. Type \"Invalid entry. Customer addresses must be within the continental United States\" into the Validation Text property box. This completes controls for CustomerState. 5 REQUIRED FIELD PROPERTY Although input masks and validation rules control values input into the accounting system, the Required field property ensures that all necessary data is entered into the accounting system. This is accomplished simply by setting the Required field property value to Yes. When set to Yes, a user must enter all required fields before the record can be saved as a valid record. Setting the Required field property to No is appropriate for either optional data or if that particular field will be completed after the initial data is entered. The Allow Zero Length field property seems like it should always be set to No when Required is set to Yes. However, a zero-length field is an empty text string created by typing two double-quotation marks in a row without any characters between them. Therefore, the Allow Zero Length field property value depends on whether or not an empty string is a valid value for that particular field. Additionally, the Validation Rule property setting will override the Allow Zero Length property. Although setting a primary key's Required property to Yes does not alter the database's behavior, doing so is good practice because primary keys are required fields. EXERCISE 3. SETTING THE REQUIRED FIELD PROPERTY FOR tblGBICustomer Take a look at the attributes in tblGBICustomer. Which fields would you require to be completed when a new customer is added to the database? Are any fields optional and therefore not required? Open tblGBICustomer in Design view and set the Required field property for all attributes. Is a null string (zerolength) a valid entry for any fields in tblGBICustomer? If so, set Allow Zero Length to Yes. Otherwise, set it to No INDEXED FIELD PROPERTY Since CustomerNo is the primary key of tbl-GBICustomer, Access requires a unique value to be entered for each record before saving it. That is why Access automatically set the Indexed property for CustomerNo to Yes (No Duplicates), above. Setting the \"(No Duplicates)\" portion of the Indexed property to Yes (No Duplicates) ensures that each customer's identification number is unique, thus, preventing errors caused by two customers having the same CustomerNo. The \"Yes\" portion of the setting causes Access to generate a special operation on that field generating an indexto speed up searches on that field. That is why indexing is set to Yes for primary keysbecause queries and other searches are often based on primary keys. Since foreign keys are also the basis of searches, foreign keys should be indexed, also. However, the index property for foreign keys should be set to Yes (Duplicates OK) because foreign keys are not unique. You can also index non-key fields. For example, bulk mailings need to be sorted by zip code. So, Global Bike should index CustomerZipCode if they frequently send mass mailings to their customers. 6 EXERCISE 4. SETTING THE INDEXED FIELD PROPERTY FOR tblGBICustomer Take a look at the attributes in tblGBICustomer. Are there any foreign keys? Will GBI likely use any other attributes for frequent searches? Open tblGBICustomer in Design view and set the appropriate indexed field property for all attributes. Close the table and save your changes. Default Values Setting the Default Value property is another method to control user input. For example, a salesperson may enter customer information in the database before the customer's credit limit has been set. However, because CustomerCreditLimit is a required field, the salesperson must enter an amount in the CustomerCreditLimit field for Access to save the record. One solution is to set the default value to \"0,\" which is an allowable value (the validation rule is \"LL;;* caption \"Frame Size\" required YES Zero Length NO Indexed YES (No Duplicates) tbl-InventoryColor ColorID field size 3 input mask >LLL;;* caption \"Frame Size\" required YES Zero Length NO Indexed YES (No Duplicates) ColorHue field size 15 required YES Zero Length NO Indexed NO 14 tbl-InventoryType TypeUseID field size4 input mask >LLLL;;* caption \"Bicycle Type\" required YES Zero Length NO Indexed YES (No Duplicates) TypeDescription field size 25 required YES Zero Length NO Indexed NO Populating the Tables with Data Enter at least two records from the list below into the each table as appropriate. This will allow you to test the table for proper properties and behaviors. TypeUseID SizeID GearsID MaterialsID ColorID ROAD TOUR CITY OFFR ACCE 44 46 48 50 52 54 1 3 8 24 CR AL FC TI RED YEL PUR BLU BLK SLV road and racing bicycles touring bicycles city cruiser bicycles off-road bicycles accessories 44 cm frame 46 cm frame 48 cm frame 50 cm frame 52 cm frame 54 cm frame single speed 3 speed internal hub 8 speed derailleur 24 speed derailleur Cro-moly Aluminum Carbon fiber Titanium red yellow maroon blue black silver Enter the inventory records for the Inventory Item table: 15 tbl-InventoryItem EXERCISE 10: Linking Category Tables To tblinventory TypeUseID SizeID GearsID MaterialID ColorID ROAD 44 8 CR RED 1. Close all Access 46 objects. Click the 24 Database Tools tab,CR click Relationships in RED the Relationships ROAD group. Your Relationships window should be empty. ROAD 48 24 CR RED 2. Click Show Table in the Relationships group (on the Design tab), double-click the following ROAD 50 24 CR RED tables to add them to the Relationship window: tbl-InventoryItem, tbl-InventoryType, ROAD 52 24 AL RED tbl-InventoryGears, tbl-InventoryMaterial, and tbl-InventoryColor. ROAD 54 24 AL RED 3. Click Close when you are done. ROAD 44 8 CR YEL 4. Link tbl-InventoryType to tbl-InventoryItem by dragging its primary key, TypeUseID, right on ROAD 46 24 CR YEL top of InventoryTypeID (foreign key) in tbl-InventoryItem. You should now see an Edit ROAD 48 24 AL YEL Relationships dialog box. Under Table/Query you see tbl-InventoryType (the name of the table ROAD 50 24 AL YEL you dragged \"from\") and tbl-InventoryItem (the name of the table you dragged \"to\"). The ROAD 52 PUR attributes on which you linked the24two tables appearsCRunder each table name. To help you make ROADthe correct linkages 54 it is a good practice 24 TI PUR to always drag from the table containing the TOURprimary key (the 54 one side of the24 TI related table containing RED relationship) to the the foreign TOURkey (the many side 50 of the relationship). 8 CR relationship type at YEL Notice that the the bottom of the Access24knows this because TOURdialog box is One-to-Many. 52 CRof the way you set the YELindex property for the linked attributes. TOUR 54 24 CR YEL 5. In the Edit Relationships dialog box, check Enforce Referential Integrity. Enforcing referential TOUR 54 24 CR BLK integrity limits the user to entering values in the foreign key field of the related table CITY 44 3 CR PUR (InventoryTypeID in tbl-InventoryItem) that already exist in the primary key field of the primary CITY 54 8 FC SLV table (TypeUseID in tbl-InventoryType). CITY 54 8 TI RED 6. In the Edit Relationships dialog box, also check Cascade Update Related Fields. When this box CITY 50 1 CR YEL is checked, a change to a value in the primary key field will automatically cause the value in the CITY related foreign key 52 field to be updated. 3 CR YEL CITY 54 8 CR YEL 7. Click Create in the dialog box. The dialog box closes and you now see a One-to-Many CITY relationship between 54 3 CR BLK tbl-InventoryType and tbl-InventoryItem. 8. Repeat Steps 1 through 7 with tbl-InventoryGears, tbl-InventoryMaterial, and tbl-InventoryColor. 9. Save and close the tables when you are done. The Inventory Form Although you can enter data directly into tbl-InventoryItem more easily than you could enter data into tblCustomer, creating an Inventory Data Entry form will make data entry and update tasks easier and less error-prone. This project restricts the inventory to four categories. Although this is unrealistic for an actual business, it provides an illustration of useful form design techniques that can be applied in many different applications. 16 The next task is to create a form to enter new inventory items into the accounting system. Because tbl-InventoryItems records are smallonly six fieldsan entire inventory record can be displayed on one row by using a tabular form layout. This layout allows displaying many records at one time, whereas a columnar form layout like tblCustomer displays one record at a time. Exercise 11: Creating The Inventory Data Entry Form 1 2 3 4 5 Click tbl-InventoryItem in the Tables group of the Navigation Pane to highlight the table name. Click the Create tab and click Form Wizard in the Forms group. The first of a few Form Wizard dialog boxes appears. The available fields in tbl-InventoryItem appear in the Available Fields panel. Click the >> button to move all available fields to the Selected Fields panel. Click the Next button to get to the Layout dialog box. Click Tabular. Click Next and type frm-InventoryItem in the title text box. Click the Finish button, which closes the dialog box and opens frm-InventoryItem in Form view. Unlike Form Tool, Form Wizard lets you determine which fields will appear in the form, even if they Create Tab Form Wizard Forms Group tbl-InventoryItem are from multiple tables or queries. This is particularly helpful when creating a form with a subform as you will do later in this project. Form Wizard also allows you to select the layout of the form columnar in this case. The form created by the wizard is, by default, named exactly the same as the table from which the form was derived. It is necessary to over-ride the defaults to make the form more \"intuitive\" for users to employ. The form also has a very plain appearance. Most designers prefer to \"pretty up\" a form by giving it a descriptive name, changing the font and font colors, and arranging the fields to be easier to understand by just looking at the form. 17 EXERCISE 11: Improving The Appearance Of The Inventory Data Entry Form Open your form in Layout view. Right-click frmInventoryItem in the Navigation Pane and click Layout View in the shortcut menu. Make the InventoryTypeID control box narrower. Click the InventoryTypeID text box control. Place your cursor on the right edge of the box. Drag the right side of the box to the left until the width better fits the data. Change the title text and resize the title textbox. Double-click the form title. Change the text to Inventory Data Entry. Click bottom right corner of the text box so it changes color. Click and drag bottom right corner (cursor will turn into a diagonal two-headed arrow) up and to the right until the entire title just fits inside the text box. Bold the five label controls by pressing and holding the Shif key while clicking each label control. Click the Bold button. (Bold appears in the Font group on the Format ribbon and in the Font group on the Home ribbon). The Layout View allows all properties of the controls and labels to be set as the designer chooses. Test the form by using it to enter the data in the following table into the inventory table. Bike Type TOUR CITY ROAD ROAD Frame Size 54 cm frame 44 cm frame 42 cm frame 42 cm frame Number of Gears 24 gear set 3 speed internal hub 24 gear set 24 gear set Frame Material Cromoly Steel frame Cromoly Steel frame Titanium frame Carbon Fiber frame Frame Color yellow maroon silver color black Improving the usability of the data input form Notice that you have no easy way to look up any of the parameters from within the form. On a small data set such as this one, this is a minor irritant. Suppose that instead of 5 characteristics the inventory included multiple characteristics such as the manufacturer of the shifters, chain rings, and rear cassette; factory-installed accessories such as a bell, fenders, or cargo racks; types of tires such as kevlar belted, steel radial, high pressure, cross-country tread, and width. It would not take very many such additional descriptors in the inventory table to make it nearly impossible for anyone to memorize. To make the form easier for the user to employ (and thus to save money for the company time by reducing the time required for database maintenance), fields in the form can incorporate look-up features such as drop-down lists and combo boxes. Drop-down lists are the familiar features of many web sites that allow a user to choose an entry from a list of entries. Combo boxes differ from drop-down boxes in that the user may either type the entry or choose from a drop-down list, which ever is more convenient. 18 Completing the inventory data entry form Open frm-InventoryItem in the Design View. EXERCISE 12: Adding a Combo Box To the Inventory Data Entry Form 1. Holding down the Shift key, click the TypeUseID, SizeID, GearsID, MaterialsID, and ColorID text boxes then press Delete. Now you can use the Combo Box Wizard to create new controls. 2. On the Form Design Tools tab, select the Design tab and click the Down Arrow at the bottom right of the Controls ribbon 3. Click on Use Control Wizards to highlight it. 4. Next click Combo Box in the Controls group. 5. Move the cursor into the Detail section until the crosshairs are positioned where you want the top-left corner of the combo box (cursor becomes crosshairs with a combo box icon); then click to start the Combo Box Wizard. 19 6. In the first dialog box click the first option button with the caption I want the combo box to get the values in a table or query. Click Next. 7. The second step is to choose the data source for your combo box. Select tblInventoryColor and click Next. 8. In this dialog box, click the ColorHue field and click > to select it. The values for ColorHue will be what you see when a user clicks the combo box down arrow. Click Next. 9. Now, choose the sort order. Ascending order is the default option. Since you are creating the combo box to choose the color of a bike based on description, to avoid having to memorize all of the codes, Select ColorHue as the field Access will use to sort the values in the combo box. Click the Next button. 10. This dialog box allows you to adjust the size of the column displayed, though there is no need to do so in this case. Also, leave the Hide key column (recommended) check box checked because showing the ColorID code will not help the user select the correct color. Click the Next button. 11. Click the second radio button with the caption Store that value in this field; then, click the combo box arrow button and select the InventoryColorID field. This step stores the inventory composition code in the InventoryColorID field of tblInventory. Click Next to open the last dialog box. Enter a descriptive lable for your combo box, e.g. \"comFrame\" and click the Finish button. The Wizard creates a combo box control according to your specifications and places it on the Inventory form. 12. Change the name of the combo box by clicking on the Property Sheet button in the Tools group. Click the Other tab and type comboColor as the value for the Name property, and press Enter. 13. Delete the label for the combo box you just created. Click the label control box, which may be difficult to see because it will overlap the GearingID text box. Press the Delete key. 14. To test the new control, click the View menu button in the Views group to return to Form view. Then, click the Color combo box arrow button to display the six choices black, blue, maroon, red, silver, and Yellow. Return to Design view by clicking the down arrow on the View menu and clicking Design View. You can then adjust the position of 20 the combo box control to match the text box control it replaced by clicking and dragging the combo box to your desired location. 15. Adjust the size of the combo box to be the same as the Composition label. Press and hold the Shift key while you click both the combo box to allow displaying the widest selection (yellow). Now follow instructions 4 through 15 to create drop-down boxes for the four remaining inventory fields in the Inventory Data Entry form. Sales Order Business Process When customers decide to buy products or services, they communicate this desire by sending a purchase order form, sending a letter, making a phone call, telling a sales person, or filling out an online order form. Each of these actions is a type of purchase order. Firms receiving the purchase order often record it in its own records as a sale order. Whether the order arrives as printed paper in the mail, a verbal order over the telephone, or a Web-based order, all sale orders contain common componentsidentifying when the sale order occurred, which customer placed the order, and which inventory item(s) the customer wishes to purchase. In the next part of this project, the over-all structure of the sales/collection business process will be established. For now, instruction will continue in creating specific tables and records that will be related to complete this project. Adding Agents Other Than Customers: Employees When GBI enters a sale into the accounting information system, a record is kept of the salesperson responsible for each sale order. Tracking sale orders by salesperson can be used for control purposes as well as for evaluation. One obvious control is the ability to determine the salesperson responsible for sale order errors. A less obvious control facilitated by this data is to look for fraudulent sale orders by querying the database to determine if the delivery address of any customer is the same as any of the salespersons' addresses. Additionally, GBI can use this information to evaluate salespeople by querying the database to determine the quantity of sale orders and total dollar amount of sales by salesperson. Each salesperson is represented in the sale order table by their employee identification number (EmployeeID). This foreign key links the sale order table to the employee table because EmployeeID is the primary key of the employee table. Both tbl-GBIEmployee and tbl-GBIEmployeeType are used to add employee-related controls to the sale order table and the sale order form. Use the data on the following page to create the tbl-GBIEmployee Table. 21 EXERCISE 13: CREATING THE EMPLOYEE TABLES Create the tblEmployee using the following properties: Field Name Data Type Field Size InputMask Caption Reqd Zero Lng Index EmployeeID (Key Text 3 000;0;_ Employee # Yes No Yes (No Duplicates) EmployeeTypeID Text 2 00;0;_ Type Yes No Yes (Duplicates OK) EmployeeLastName Text 30 Last Name Yes No No EmployeeFirstName Text 20 First Name Yes No No EmployeeMiddleInitial Text 1 >L MI No No No EmployeeSSN Text 11 000\\-00\\-0000;0;_ SSN Yes No No EmployeeAddress1 Text 50 Address Yes No No EmployeeAddress2 Text 50 Address 2 No No No EmployeeCity Text 20 City Yes No No EmployeeState Text 2 >LL State Yes No No EmployeeZip Text 10 00000\\-9999;0;_ Zip Code Yes No Yes (Duplicates OK) EmployeePhone Text 14 !\\(999\") \"000\\-0000;0;_ Phone Yes No No EmployeeStartDate Date/Time 99/99/0000;0;_ Start Date Yex No No EmployeeMarital Text 1 >L Marital Status Yes No No Default value: \"S\" Validation Rule: =\"M\" or \"S\" Validation Text: Enter M for Married S for single. EmployeePayRate Currency Decimal Places: 2 Pay Rate Valadation Rule: >7.50 And =0 Note: This allows for a $0.00 total f. Validation Text: Sale order amount cannot be negative. g. Required: Yes. 3. Close and save the table as tbl-GBISaleOrder. The Order-to-Inventory Table The Order-to-Inventory table records the many-to-many reservation relationship between tbl_GBISaleOrder and tbl-InventoryItem. It will store four fields: 1. The primary key of tbl-GBISaleOrder. 2. The primary key of tbl-InventoryItem. 3. The quantity of each inventory item that appears on each sale order. 4. The price of each inventory item that appears on each sale order. The primary keys from tbl-GBISaleOrder and tbl-InventoryItem combine to form the composite primary key in tbl-GBIOrder-to-Inventory. In Microsoft Access, you create a composite primary key by creating two separate fields:-one for each entity table's primary key and then designating both fields as primary keys. If you are using the Microsoft Access help screens, you may notice that Microsoft uses the term junction table, instead of relationship table, in its other documentation. Before you begin the next exercise, close any open tables or forms. 25 EXERCISE 16: CREATING THE ORDER TO INVENTORY RELATIONSHIP TABLE Create a new table in Table Design view. This table will be named: tblGBIOrdertoInventory First field: Field Name: SaleOrderID Data Type: Text. Field Size: 6 Input Mask: 000000;;_ Caption: Sale Order# Required: Yes. Indexed: Yes (Duplicates OK). Note: The Indexed setting must allow duplicates because a field that is part of a composite primary key will likely contain duplicate values Second field Field Name: InventoryID Data Type: Text Field Size: 4 Input Mask: 0000;; Caption: Item # Required: Yes Indexed: Yes (Duplicates OK). While pressing the Ctrl key, click the record selectors for SaleOrderID and for InventoryID. With both fields selected, click Primary Key in the Tools group. The primary key symbol should appear in the record selectors of both fields. Third field Field Name: QuantityOrdered Data Type: Number Field Size: Long Integer Decimal Places: 0 Caption: Quantity Required: Yes Fourth field Field Name : SOPrice Data Type: Currency Format: Currency Decimal Places: 2 Caption: Price Required: Yes. Close and save the table as tblGBIOrder-to-Inventory. 26 The Sale Order Entry Form The sale order entry task requires a more complex form than either the tasks of customer information entry or inventory information entry required. To enter all of the information contained in customers' sale orders, your database needs the following links: 1. tbl-GBISaleOrder to tblCustomer-to obtain information such as the customer's name and address. 2. tbl-GBISaleOrder to tbl-GBIEmployee-to obtain the employee's name who takes the sale order. 3. tbl-GBISaleOrder to tbl-GBIOrder-to-Inventory-to obtain a list of the inventory item numbers, quantities, and prices for each item on each sale order. 4. tblGBIOrder-to-Inventory to tbl-InventoryItem, and from tbl-InventoryItem to the inventory category tables-to obtain a description for each item on each sale order. Creating Relationships with the Sale Order Table A sale order entry form that meets these objectives will use all of the above tables. The form must read from tblCustomer, tbl-GBIEmployee, tbl-InventoryItem, tbl-InventoryMaterial, tbl-InventorySize, tbl-InventoryGears,tbl-InventoryColor, and tbl-InventoryType, and write to tbl-GBISaleOrder and tblReservation SaleOrderInventory. Because this form will read data from tblCustomer and tbl-InventoryItem, you must have data entered in these tables. EXERCISE 17: CREATING RELATIONSHIPS FOR TABLES USED IN SALE ORDER EVENT 1.Close all open tables, forms, and queries. 2.In the Database Tools tab, select Relationships. 3.Drag all six inventory tables to the left side of the screen to match the \"Resources\" placement in the REA model. (see Figure 2 Inventory Tables, below) 4.Click Show Table in the Relationships group (on the Design tab). Press Ctrl and click on: tblGBICustomer, tbl-GBIEmployee, and tblGBIEmployeeType. Click Add. Size each table to be able to see all the field names. Arrange the tables at the far right of the relationships pane in the Agents position in the REA model. 5.Add tblGBIOrder-to-Inventory, and tbl-GBISaleOrder to the relationships pane. Position them in the \"Events\" position of the REA model. (see Figure 2 on the next page.) 6.Create the relationships among the tables by dragging the key field in a table over the foreign key field in the related table. Check Enforce Referential Integrity and Cascade Update Related Fields check boxes. Check to be sure that you have the identical many-to-one relationships as is shown in Figure 2, below. Note: If you have problems linking the tables, review Exercise 10 on page 17. 27 RESOURCES EVENTS AGENTS Figure 2 Sales Order Event Tables & Relationships Figure 2 Show Tables in Relationship Tools 28 Creating a Sale Order Entry Form Now that all the required tables for the sales order event have been created and the necessary relationships established among them, a Sale Order Form may be created. To ease the difficulty of formatting data within a form which will call upon data from several tables, it is advisable to create one or more queries which will filter the data needed by the form. An advantage of using queries is that queries can format data directly. On the other hand, formatting in a form requires writing extensive Visual Basic for Applications (VBA) code. The sale order form requires customer address information that is stored in the table, tbl-GBICustomer. However, the city, state, and zip code information are stored in individual fields within the table. Thus, the information in CustomerCity, CustomerState, and CustomerZipCode must be concatenated and formatted into a format, such as "San Antonio, TX 78235-1101." The next exercise will provide instructions for creating a query that retrieves, concatenates, and formats these three fields to form a new field, CityStateZip. EXERCISE 18: CREATING A QUERY FOR THE SALE ORDER ENTRY FORM 1. On the Create tab and click Query Design. 2. Double-click tbl-GBICustomer from the list presented in the Show Table dialog box. 3. Select all of the fields in tbl-GBICustomer by holding Shift and clicking on each field name, until all are highlighted. 4. Click and hold the left mouse button and drag the field names into the Criteria Pane, which is the area at the bottom of the window that resembles a spreadsheet. When you \"drop\" the field names, they will be displayed in separate columns. 5. Add the attributes of the fields to the attribute list in the Expression Builder by saving the query as qry-GBICustomerlnfo (by either right-clicking the query's Tab or by clicking Save on the Quick Access Toolbar) before completing the query. 6. Scroll to the right in the Criteria Pane to the first blank column after the field names. Click on the top cell in that column, then click on the Builder Icon in the Query Tools, Design, Query Setup group. 7. In the Expression Builder dialog box, type the name of the query expression: CityStateZip. 8. Follow it with a colon. 9. Then in the expression builder window double-click on the field name, CustomerCity. 10. Type an ampersand (&) (just above the number 7 on your keyboard), space, quotation mark, a comma (,), space, quotation mark, and a second ampersand. 11. Now double-click the field name, CustomerState. Now type another ampersand , space quotation mark, space, quotation mark, space, and a fourth ampersand. 29 The completed CityStateZip expression: CityStateZip: [CustomerCity] & \