Microsoft AccesDatabase Assign - Spring 2020.docx Customer Invoicing: A Database Management System...

Question

Microsoft AccesDatabase Assign - Spring 2020.docx Customer Invoicing: A Database Management System Case Using Microsoft Access INSTRUCTIONS FOR STUDENTS Purpose The purpose of this case is to reinforce database concepts that we have acquired through in-class exercises, using Microsoft Access.  When you finish Parts 1 and 2 of this project you should feel comfortable with Access and the basic concepts of a relational database.  Hopefully, this exercise will give you the confidence to build your own relational database for information that is relevant to you! This project will guide you through the process of building a database invoice system. This process includes the following: 1. Create five relational tables. 2. Establish relationships among the tables. 3. Populate the three master file tables. 4. Create an invoice form for sales transactions. 5. Use the invoice form to input sales data and thereby populate the two transaction file tables. 6. Use the invoice form to display and print invoices. Typographical Conventions ● Bold indicates text that you type. Bold is also used to indicate a menu item or check box that needs to be selected. For example, to cut and paste a text passage, you should highlight the text, select Edit ∙ Cut from the menu bar, then move your curser to the location for the paste and select Edit ∙ Paste. ● Double question marks (??) mean that you need to figure out for yourself the appropriate entry or action. ● “. . . etc.” is used when a similar process or set of steps has been previously explained. When you see “. . . etc,” you should follow the processes described previously for other entries and actions that are similar. ● For field names in the Access tables, ALLUPPERCASE letters are used for primary keys and MixedCase for nonkey fields. ● Notes on Access techniques, database concepts, and accounting software integration are included in boxes. Page 1 Page 2 Creating an Invoice Form for Entering Sales Transactions Begin ● Load Access and select Blank Desktop Database. ● To the right of your screen, you will see a box that asks for your “File Name” – this is the name that you will use to save your database. Click on the folder to the right of the box to select the drive and directory location where you want to save your file. Type in a file name that includes your first and last names, e.g., SuzanneSmith.accdb. Click on Create. ● Make note of your file location so that you can find your file again at a later date. Notice when you are on the main window in an Access database (DB) you can create the following objects: ● Tables ● Queries ● Forms ● Reports ● Pages ● Macros ● Modules (programmed procedures) Understand Information Needs If we were starting a new company, one of the first things we would do is hire employees, so we would need to record information about these employees. Next, we would purchase inventory items. Then, we would obtain customers and make sales to the customers using invoices to document the sales transactions. In an accounting information system database, all this information is recorded in the five tables shown below. Information required Table description Table name Information about the employees Employee master file tblEmployee Information about the inventory items Inventory master file tblInventory Information about the customers Customer master file tblCustomer Information about sales transactions Invoice transaction file 1 tblInvoice Information about specific line items within each sales transaction Invoice transaction file 2 tblInvoiceLin e Page 3 Most of the detailed contents of each table are shown in Exhibit 1 and will be explained more fully as you complete this assignment. In the next section of this assignment, you will begin the process of creating the five tables. Page 4 Create Table Structure Before you can enter data in a table you have to create that table's structure. ● Under Create Tab, select Table Design from the Tables ribbon. (A table in datasheet view will be created by default the first time you create a new database.) Using the Table Design View, you will create the five tables described above. Please try to use the names and abbreviations as shown in the following sections to avoid confusion and to fit in the space provided on the invoice form. Data Structure In the design view, you will be creating the database’s table data structure, also called record structure or internal level schema. The data structure can be viewed as a component of the data dictionary. (For more information about data structure, see reference materials such as Heagy et al., 1999, pp. 10-19, or Bagranoff et al., 2008, Chapters 13-15.) Design tblEmployee The first table structure you create is for the employee table. Enter the fields and attributes as described below. First Field: Name: EMPCODE Make EMPCODE the key field by clicking on the key icon in the menu. Data Type: Short Text Data Type An important component of data coding is determining the data type. For EMPCODE we use short text because the employee code includes an alpha prefix (the letter e). See Exhibit 1. For more information on data types, use the Access help menu and search for field data types. In this project you will be required to use three other field types: number, currency, and date/time. Field Size: 6. This size will accommodate the six-digit employee code. Caption: Employee Code. The caption property provides an alternative name for the field. This alternative name, or alias, appears in Access reports and forms. The captions are provided for your benefit, and you should Page 5 use names that are descriptive and that you will recognize and understand. Required: Yes. Because EMPCODE is the primary key, the Required attribute should be set to yes. Setting the Required attribute to yes provides an internal validation control. When entering data, Access will not allow you to go to the next record without entering a value for EMPCODE. Allow Zero Length: No. It would be possible to have an attribute required, but allow zero length if a field may not be applicable to a particular record. However, you do not have this situation in any of your tables. Indexed: Yes (NO duplicates). Primary keys must be indexed so Access can perform searches for the indexed values. Indexing is not recommended for nonkey fields. You cannot allow duplicates for EMPCODE because this indexed field is your primary key. (You may reference an accounting information systems text, such as Heagy et al., 1999, pp. 10-6 through 10-9, or other supporting materials regarding purpose, advantages, and disadvantages of index files.) Index Files Every time you tell Access to index on a field name, Access creates an index file to increase the speed at which records in the main file are sorted and accessed. This index file has the same number of records as the main file but only two fields—an index key (which is the field you told Access to index on) and the record address. Each time you add or delete a record in the main file, all its related index files also are updated because the index file has to stay sorted on the index key. If you do not expect to sort frequently on a particular field, creating an index file is a waste of resources. You can sort on any field in Access whether or not you have an index file for that field. (See Heagy, et al., 1999, pp. 10-6 through 10-8, or other reference material.) There are several other table structure settings, e.g., Unicode compression. You may ignore these settings as they are not necessary for this introductory case. Second Field: Name: EmpName Data Type: Short Text Field Size: 20. Use your judgment in setting field sizes. 20 to 30 characters is common for last names Caption: Employee Name Required: Yes Allow Zero Length: No Indexed: No. You may index nonprimary keys, but it is not necessary. And if you do index them, be careful when you decide to allow or not allow Page 6 duplicates. If a nonprimary key is a foreign key on the many side of a one-to-many relationship, then you MUST allow duplicates, because there may be MANY of this attribute for each item in the related table. Third  Field: Name: CommRate Data Type: Number Field Size: Single Format: Fixed Decimal Places: 3 Caption: Commission Rate Default Value: Set the default value to zero (0) so if no entry is made, the commission rate will default to zero. Validation Rule: Assume the minimum commission rate for the company in this exercise is zero because middle managers sometimes make sales, but they receive no commission. The maximum commission rate is 20%. Therefore, set the validation rule to >= 0 And

Baljit · Accepted Answer

Answer Attached Below:

Database Assign - Spring 2020.docxCustomer Invoicing:A Database Management System CaseUsing Microsoft AccessINSTRUCTIONS FOR STUDENTSPurposeThe purpose of this case is to reinforce...

Answer To: Database Assign - Spring 2020.docxCustomer Invoicing:A Database Management System CaseUsing...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment