MGT 20600 Assignment 5



Due DateCheck Concourse for the assignment due date.Total Points20ObjectivesIn this homework you will demonstrate proficiency in using the following:Creating a table within a databaseImporting tables from files created in other application packagesModifying the properties and contents of tablesCreating drop-down lookup boxes Creating relationships between tablesCreating simple single-table and multiple-table queries.To Complete The Assignment:Copy the Assignment 5 folder from your section’s Assignment folder on the Courseware I: drive to your computer’s desktop. This folder contains the four files that you will need to complete this assignment.NEVER open or work on any files directly through WebFile!!!NEVER open Access files from the I Drive or any other drive where you do not have write permissions!!!Always enable macros when prompted.When you are finished, save the Access file and copy it to the location where you keep your assignment backups on the N: Drive. DO NOT DO A SAVE AS! If you’re working on the desktop of a campus computer, make sure that you make another backup copy of the files you just finished onto a USB Flash drive or in another directory on your N: drive. Any data saved on a computer’s desktop in a lab is lost when you log out.Very Important: NEVER open or work on any files directly through WebFile. Work this and all other assignment files from the desktop in a cluster, a personal USB flash drive, or from a designated drive on your personal computer, and make sure that you BACKUP your completed work on your N: Drive.Case Backgroundleft0Worm’s Turn has begun keeping information about their business in an Access database. The basic database structure is nearly complete, but they have requested that you bring it up to date. You also find that you can add a few things to make the data more accessible and more accurate. Open the file and let’s get to work!A. Complete the Documentation FormMake sure that you have copied the Assignment 5 folder containing the MGT20600_A5_S11.accdb file to your computer’s desktop.Before opening your file, rename your MGT20600_A5_S11.accdb file to NetID-MGT20600_A5_S11.accdb where the prefix is your NetID. (Example: littlefi-MGT20600_A5_S11.accdb)Open the NetID-MGT20600_A5_S11.accd database file. The first time you open your file, open and complete the Documentation Form. You must enter accurate information. The Documentation form will automatically provide the current date. Close the Documentation form when you are finished entering your information.B. Creating Tables and Adding RecordsTake a look at the figure below (Figure 1). This graphic shows you the tables that are currently in the database. The database is far from complete; it needs a few more tables to clarify some of the contents and ease data entry. You can see from the graphic that whenever possible, similar information has been stored in the same table, such as all information about Customers, all information about Products, etc. Figure 1: Basic Tables in the Worm’s Turn DatabaseYou will be adding three new tables: BUILDINGS, HARDINESS_CODES, and STOCK_CODES. Begin by creating the STOCK_CODES table using the Design View. Use all capital letters for this table name and all other table names. The records in this table will contain the codes used to describe products in stock, as well as a full description of each code. Once you complete the table design, you will manually enter the data into the table. Use the following information to define the fields and their properties that will be in this table.Field NameData TypeKey InfoField SizeRequired?Stock CodesTextSet as Primary Key11YesDescriptionText50YesAdd the following records to the table:Stock CodesDescriptionCGContainer GrownMPMature PlantsNPNon PlantRSRoot Stock; Bare RootSSeedsSDLNGSSeedlingsYPYoung PerennialsBe sure to type these in accurately.C. Importing TablesImporting data from other files into our database is a faster and more accurate method of populating tables than retyping all the business data into the new database. You have been supplied with some files that contain the information about buildings and plant hardiness (what climate the plant needs, and whether it survives from year to year, etc.).Import the building data from BUILDINGS.csv, a comma-delimited file. The first row can be used for the field names. Set the Building Code field as the primary key. Name the new table BUILDINGS. Do not save the import steps.Import the master list of the different types of hardiness codes and their descriptions from a tab-delimited text file called HARDINESS_CODES.txt. Use the first row for field names and do not set a primary key. Name the new table HARDINESS_CODES. Do not save the import steps.Due to the nature of this business, Worm’s Turn hires seasonal employees. Until now, records for employees were kept in an Excel file. Import the seasonal employees’ information from the EMPLOYEES.xlsx file and append it to the existing EMPLOYEES table. The information you need is contained in a range called SEASONAL_HIRES. Import only that range. Take note that the named range includes column headings. These match the column headings currently in the database and are automatically designated as being selected. Do not save the import steps. Troubleshooting: If you encounter any problems when importing the named range into Access, then you may need to copy the necessary records from Excel and paste them into the EMPLOYEES table.Close all tables. Compact and Repair the database before proceeding.D. Modifying Table StructuresNow that the import process is complete, you need to make some critical changes to the table design and field properties. You’re doing this in order to protect the integrity of the data, remove unnecessary data fields, and limit the contents of fields. On the following pages are some worksheets. Read through the worksheet as you open each table in design view. The shaded areas are given as information, showing the current state of the database. The Current Field Information column is meant to help you understand what certain fields are for. In the unshaded column called YOUR TASK: you will find things that you need to do to that field. Use the given information AND the table data to help you decide how best to complete this task. You do NOT have to type in the Field Descriptions.IMPORTANT NOTE: After changing field sizes, Access will warn you that the field size has changed and some data may be lost. Read each warning that appears, but allow the testing and saving to continue. All data in this database is less than or equal to the new fields sizes; no data will be lost.Open the CUSTOMERS table and complete the specified tasks.Field NameData TypeCurrent Field InformationField SizeYOUR TASK:Customer IDTextCustomer’s unique identifier?255Set this as the primary key. Set the field size to accommodate IDs of 7 characters.LastnameText?30FirstnameText?20Address 1TextPrimary Address30Address 2TextApartment/RR number30CityText?20StateText?2Zip CodeText??255Set an appropriate field size based on existing data. Supply an appropriate input mask for 5-digit zip codes without zip code extensions. Be sure to create an input mask to reflect this and store it without symbols.Telephone NumText? 14AcctNumTextNumber for Business Customers10?BusNameTextName of Business30The name of this field should not change, but should appear as Business Name in datasheet view.Field NameData TypeCurrent Field InformationField SizeYOUR TASK:Preferred Pmt MethodTextApproved payment types are Purchase Order, Check, Cash, Credit Card, or Money Order. A drop-down menu supplies the input.25The name of this field should not change, but should appear as Payment Type in datasheet view. Format the field appropriately so that existing data appears in upper case.Credit Card TypeTextAccepted credit cards25This field should provide the user with a one-column drop-down list of accepted credit cards: VISA, DISCOVER, AMERICAN EXPRESS, and MASTERCARD. Make sure that you can read all credit card names, that the list is in the order shown, and make sure you type the information accurately. Accept the lookup column label provided for you.Credit Card NumberTextCustom input mask supplies hyphens between each group of 4 numbers. The credit card number itself is 16 digits.19?Expiration DateText?50This is a date. Choose the appropriate data type. The data should be in a Medium date format. Special Requests???Add this field to the database, allowing for comments with lengthy text or a combination of text and numbers.Open the EMPLOYEES table and complete the specified tasks.Field NameData TypeCurrent Field InformationField SizeYOUR TASK:Employee IDTextNumber assigned to new employees.5Set this as the primary keyLastNameText?20?FirstNameText?20?TitleTextEmployee's title30?TitleOfCourtesyTextSalutation title.5?BirthDateDate/??Employees must be at least 18 years of age. If an illegal date is entered, an error message should read: The employee must be at least 18! (Hint: How many days are in 18 years?)?TimeHireDateDate/??Default hire date should be the current date. It should appear in each new record automatically. This field cannot be blank. There should be an input mask for data entry, so that the user can type in digits and the slashes will appear automatically, for example, 9/27/1969, and it should have a short date format.?TimeAddressTextStreet or PO box60?CityText?15?StateOrProvinceTextState or Province255Enter an appropriate field size based on existing data and change the caption property to State.Field NameData TypeCurrent Field InformationField SizeYOUR TASK:PostalCodeText?50Change the field name to Zip Code and set the field size to 5. HomePhoneTextPhone Number includes area code?50The field size should be adjusted for the appropriate number of characters for a telephone number with area code and symbols. To be sure you have this right, count the number of characters used in the standard format for a US telephone number including any spaces, e.g., (888) 345-6789. Supply an appropriate input mask stored with symbols.ExtensionText(Remove Field)?REMOVE THIS FIELD FROM THE TABLE.NotesMemoPotentially long descriptions and comments about employees.??Reports to?(Remove Field)?Remove the field Reports to from this table.Open the HARDINESS_CODES table and complete the specified tasks.Field NameData TypeCurrent Field InformationField SizeYOUR TASK:Hardiness CodeTextShort code to describe plant tenderness and climate needs.255Uniquely identifies each type. Set the appropriate key designation and field size based on the fact that no code will exceed 5 characters.Full NameText??255Should accommodate up to and including 25 characters.DescriptionTextMedium-length descriptions, but not to exceed 255 characters in length.?255?Open the BUILDINGS table and complete the specified tasks.Field NameData TypeCurrent Field InformationField SizeYOUR TASK:Building CodeTextKey255Make sure that this field is designated as a primary key field. Set the field size to 4.Building TypeTextGreenhouses or other structures255?Items HousedTextDescriptions of Products in each building. Accommodates the maximum number of characters allowed for this data type.255?Open the PRODUCTS table and complete the specified tasks.Field NameData TypeCurrent Field InformationField SizeYOUR TASK:Item #TextUnique Product Identifier10HardinessTextCode for plant weather tolerance and survival habit5Make this field a lookup field based on the HARDINESS_CODES table, using the Hardiness Code and Full Name fields. No sorting is necessary. Show both columns and be sure to adjust column width so that all entries can be seen. Store the data from the Hardiness Code field, and accept the given label. Allow the relationship to be created when you save the field.CategoryTextGeneral class of product20?Stock CodeTextKind of product in stock6This field should have a drop-down box containing both fields from the STOCK_CODES table. No sorting is necessary. Use both available fields by removing the check mark from the Hide Key Column box. Be sure to adjust column width so that all entries can be seen. Store the data from the Stock Codes field. Accept the given label.TypeTextSubclass of category20?ProductTextProduct Name50?Wholesale PriceCurrencyBase cost/wholesale price of product? ?Standard Markup ValueNumberMultiplier for retail priceDoubleFormat as a percentRetail PriceCurrencyWholesale Price is multiplied by the value in Standard Markup Value to arrive at this Retail Price??Plant LocationTextBuilding code4?Open the TRANSACTIONS table and complete the specified tasks.Field NameData TypeCurrent Field InformationField SizeYOUR TASK:Transaction IDNumberUnique Identifier for each transactionDoubleEnter the appropriate data type for a number that is NOT used in calculations, but only as a unique identifier. The field size should be able to accommodate transaction numbers in the hundreds of thousands (6 characters).DateTextDate of Transaction50Enter the appropriate data type. Make it a medium date format. Enter an input mask that allows a short date entry.Customer IDTextPurchaser's ID number7Sales AssociateTextClerk's ID number5Delivery?TextIndicates if delivery is requested or not50Use the appropriate data type that will indicate whether or not delivery is required by using a checkbox. The default value should be No.ShippingNumberCost of Delivery?Long IntegerChange the data type to Currency.Open the TRANSACTION_DETAILS table and complete the specified tasks.Field NameData TypeCurrent Field InformationField SizeYOUR TASK:Transaction IDTextTransaction ID number 6Item #TextProduct Number of purchased item10QuantityTextNumber of items purchased50This field is used in calculations. Accept the default field size for the data type you choose.Close all tables. Compact and Repair the database before proceeding. You have now created and modified the basic tables of your database. E. Creating Relationships Between TablesRefer to the figure 2 below. Before establishing relationships, add all tables to the relationship window except the Documentation table. Establish relationships between the appropriate fields in all tables. Make sure to maintain referential integrity and cascade update related fields for all relationships. (You may have to edit some existing relationships to accomplish this.) Be sure to save all changes to your relationships window.Figure 2: Table RelationshipsF. Creating Single-Table QueriesWorm’s Turn is interested in knowing which customers are from Bloomington, Indiana. Create a query whose dynaset is a list of customers meeting this criterion. List the Customer ID, LastName, and FirstName. Sort the information in ascending order by LastName. Save the query as Query1. Hint: You must use quotes around IN for this to work. You notice that quite a few customer credit cards are due to expire in 2010. Create a query that shows the FirstName, LastName and Telephone Num of people whose credit cards will expire any time in 2010. Sort your answer in ascending order by LastName. Name the query Query2. A recent heavy rain has flooded three of the outlying greenhouse buildings, Greenhouse 6, Greenhouse 7, and Greenhouse 9. Create a query that lists the Item # and Product of all the products in inventory that are housed there. Sort in ascending order by Product. Save the query as Query3.You would like to know which products can be marketed for salads. Create a query that lists the Item #, Product, and Category for products whose Type field contains the word ‘lettuce’ or ‘greens’. Sort your answer in ascending sequence by Category and save the query as pact and Repair the database before proceeding. G. Creating Multiple-Table QueriesFor the following queries, you must be able to see the relationships in the query design window as shown in Figure 2. If you do not, you MUST go back to the relationships window and correct this. Be aware also that it is possible that a query may actually result in a dynaset with no records.Worm’s Turn sells several flowers that cannot be sold as true perennials because they may not survive a particularly hard winter. These flowering plants need to be labeled clearly so that the business is not held liable for plant death under their standard perennial guarantee. Therefore, management wants a listing of these tender perennial flowers.Create a query that lists all tender perennial plants that are flowers. Show the Item #, Type, Product, and hardiness Full Name of all of the plants that are flowers and that have this hardiness designation. (Hint: Be sure to list the hardiness Full Name – not just the code for hardiness that is?shown in the Products table.) Sort in ascending sequence by Type and then by Product. Save the query as Query5. You would like to perform a delivery sales analysis between sales personnel. Create a query that lists all the transactions made during the month of April 2010. Display the Transaction ID, transaction Date, Delivery, and sales employee Title. Select the following:All transactions requiring deliver and made by sales managers during this month.OR All transactions not requiring delivery and made by sales representatives during this month.Sort in descending sequence by Transaction ID and save as Query6. Compact and Repair the database before proceeding. Submitting the Assignment FileAll assignments are due in your personal CourseWare Dropbox folder on the I: drive on the designated due date. Be sure to create a BACKUP copy of your completed assignment in your personal N: drive just in case there is a problem with the file you have submitted. Keep the following in mind when submitting the assignment:Make sure that the file that you are uploading is named yournetID-MGT20600_A5_S11.accdb. The file MUST have this name in order to be graded.Make sure that you have SAVED and CLOSED your assignment file before uploading the completed file to your personal CourseWare Dropbox folder. IMPORTANT: Make sure that you double-click on your personal folder within the section’s Dropbox folder to open it before placing your completed assignment in the folder. If after uploading your file, you find that you want to make changes to any of your answers, simply upload the revised file and overwrite the previous file. Again, make sure that the filename of the final submitted assignment is yournetID-MGT20600_A5_S11.accdb. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download