could someone please help me these access assignment

Accounting Information Systems Access Assignment S&S Revenue Database Introduction to Microsoft Access: Microsoft Access is representative of database systems. The skills learned in Access are transferrable to other database systems. If you are a first time Access user or haven't used Access recently, watch the Microsoft training videos for beginner users at http://office.microsoft.com/en-us/access-help/training-courses-for-access-2013HA104030993.aspx NOTE: there are 4 topics you should review: Design and build tables for a database (Access basics, part 1) Create table relationships (Access basics, part 2) Create your first Access 2013 database Introduction to queries (Access basics, part 3) You must review the Microsoft on-line training before asking the GAs or professor for help with your assignments. The training videos are applicable to Access 2007 and 2010, although the 2013 features have some minor differences with 2007 and 2010. Assignment Introduction: You will use one Access file for the entire assignment, but you will submit the work in 2 separate Turnitin submissions. Each submission may have a separate due date and will be graded separately. Submission will be through Turnitin. You will submit your Access file to Turnitin. SCREENSHOTS IN THIS INSTRUCTION ARE ACCESS 2013. ACCESS 2007 AND 2010 WILL BE DIFFERENT. YOU CANNOT USE ACCESS 2003 OR OLDER FOR THIS ASSIGNMENT. PART 1 Navigation, Relationships and Queries: 1. Download the S&S Access Database file from Blackboard S&S Access Assignment folder. If prompted, select Save As and Save file as S&SLastnameFirstname. If file automatically saves to your computer, find it and change the name to include your name. 2. Open S&SLastnameFirstname by double clicking on the saved file. Microsoft Access will open and you will see a Security Warning. Click on \"Enable Content\" to activate all functions and data. 3. Review the tool bars noting the functions available for each tab: Home, Create, External Data, Database Tools. You will move between these tabs to find the appropriate functions. 4. On the left of the window you should see a list of tables. Above the word Tables will be a bar titled \"All Access Objects.\" If you don't see this list, select the dropdown menu and select Object Type and All Access Objects. 5. Open the Database Tools toolbar and select Relationships. You will see four tables are connected with lines. You can move the tables by clicking and holding the cursor on the table name. You can expand the size of the box by grabbing the lower right corner and move it. The lines connecting the tables are connected to specific attributes. Note the primary key and foreign key connections. Why does the SalesInventory table have 2 primary keys that are connected to the primary keys in the Sales and Inventory tables? The Sales-Inventory Table is used to implement the Many-to-Many relationship between Sales and Inventory. The 2 Primary keys are combined to create a Concatenated Key. This table allows each sales invoice to have more than one inventory item. Concatenated Keys will be discussed later in this assignment and in Chapters 17 and 18 of the Romney textbook. 6. Microsoft Access uses Referential Integrity to ensure the data integrity between Primary and Foreign keys. Foreign Key must exactly match an existing Primary Key and you cannot delete a Primary Key without first deleting the related Foreign Key data. Right click on the line connecting the Customer and Sales Tables. This will open the following window: Click on box to select Enforce Referential Integrity. This gives the option to select one or both of the next two lines. Select both Cascade Update Related Fields and Cascade Delete Related Records. These rules ensure that any changes to a Primary Key are also changed in the Foreign Key fields. Cllick OK. Note the maximum cardinalities are now added to this one-tomany relationship. 7. Repeat step 6 for the other two relationships. The corrected relationships window is shown below: 8. Close and save the relationships window by clicking on the Close X in the toolbar (don't close Access!!). If asked, save the changes you made to the relationships. Setting the Field Properties (Data Input Controls) The field properties are used to set restrictions and perform edit checks when data is entered or changed. The following steps will show a few of the many controls available. 1. Open the Customer Table by double clicking on \"Customer\" in the Navigation Pane on the left. Note the records and attributes. When you open a table, the table automatically opens in Datasheet View. This view allows you to see the records in the table. Records can be added while in the Datasheet View. Add the following customer: Customer#: 154 CustomerName J. Garcia Street 295 Whiting Ct City Tempe State AZ 2. In the upper left, click the dropdown menu labeled View in the Home toolbar and select Design View. Design view shows a split screen with three columns in the upper pane (Field Name, Data Type, and Description) and Field Properties in the lower pane. Note how each entry under Field Name corresponds to the Customer Table in the Datasheet View. 3. Access provides an explanation of each Field Property at the bottom of the screen. Click on the second column for each property and read the explanation to the right. Which Field Property states, \"A value that is automatically entered in this field for new records?\" (Default Value) 4. Compare the Field Properties for Customer# and CustomerName. What differences do you notice in the Field Properties pane? Compare the rest of the attributes and notice the differences. Why do you think these differences occur? (Each attribute has unique characteristics and the edit controls are tailored to optimize edit checks and database performance.) 5. Click on the field for Customer #. A \"key\" symbol appears to the left of Customer #. This indicates that Customer # has been previously defined as the Primary Key for the Customer Table. When a field is designated as a primary key, Access ensures no two inputs have the same value, and sorts records according to the primary key value. 6. The data type selected for Customer # is Number. Only numbers will be accepted as input in this field. If you type A25 as a Customer #, you will get an error message. Click on the dropdown to see the other data types you can choose. 7. With Customer # selected as the Field Name, move to the Field Properties pane at the bottom of the screen. a. Click on the Field Size field. Note that the Field Size has been automatically set to Long Integer. Click the arrow to open the drop down menu and review the other options. Do not change the Field Size. b. Return to the Datasheet View and try to enter a new customer with Customer # A25. 155You will get an exception message that says \"The value you entered does not match the Number data type in this Column.\" Select Enter New value and add the following new Customer data: Customer # 155 Customer Name A. Wang Street 452 B Street City San Diego State CA c. Return to the Design View. Select Customer #, then move to the Required field property in the Field Properties. Because the Customer # is a primary key, it must be included for every record. The Required field property should be set to YES; if it is set to NO, change it to Yes. d. With the Customer # selected, move to the Indexed field property. Note that Yes (No Duplicates) is selected. Duplicates are not allowed in the Primary Key attribute. This edit check prevents the user from entering duplicate numbers. 8. Adding Attributes: Select the Design View of the Customer Table (if you are not already in Design View.) Click on the next empty field below State in Field Name column and enter ZipCode. a. The Data Type will default to Short Text from the drop down menu. Short Text allows for combinations of text and numbers (alphanumeric). b. Move to the Field Properties and change the Field Size field to 10. This restricts the number of characters entered for a zip code to 10 characters (9 numbers and the hyphen.) c. We want to format the 9 digit zip code with a hyphen after the first 5 digits. Access has predefined formats for commonly formatted fields such as phone numbers, zip codes, social security numbers, dates and time. Click on the Input Mask field and activate the Input Mask Wizard by clicking the button on the right side of the field. If prompted to save the table, click Yes. d. Select Zip Code from the Input Mask Wizard options, then Next. You do not want to change the standard format, so click Next. When asked, \"How do you want to store the data?,\" select \"With the symbols in the mask, like this: 26431-6851\" then click Next. Click Finish. e. You will return to the Design View and will see the Input Mask for ZipCode. Review the other Properties for ZipCode and determine if anything should be changed. In case a customer doesn't know their zip code, we will not require it. We must allow for duplicates as many homes share the same zip code. f. Return to Datasheet View (click yes if asked to save) and enter the following zip codes for the customersdo not type the hyphen when entering data. Customer # 151 152 153 154 155 Zip Code 85003-1805 85014-1234 85008-1805 85004-1956 92101-2341 g. Close the Customer Table by clicking the X in the upper right corner (don't close Access). If asked, click Yes to save your design changes and new data. Reopen the Customer Table to confirm your data was saved, then close it again. 9. Validation Rules: Open the Customer Table and return to the Design View. Select State and note that this is a Short Text data type. The field size is already set to 2 to limit the entry to the common 2 letter state abbreviation (e.g., California + CA). As a short text data type, numbers or letters can be entered in the field. However, the State must be limited to letters only. The Validation Rule can be used for this edit check. a. Click on the field for Validation Rule. Enter: Not Like "*[!a-z]" Note: Include quotation marks around *[!a-z]. The Validation Rule limits the values that can be entered in the field to letters-this is not case sensitive. This prevents entering a number for State. The Validation Rule is very helpful when a specific format is required. The command options for validation rules can be found on the Microsoft website. b. Click on the field for Validation Text and type: Incorrect State format. The State must consist of 2 letters. (Quotes are not required for this entry.) c. Return to the Datasheet View. Save the table if asked. You will be asked if you want the new validation rule to be applied to all existing records. Click Yes. Access will check all existing entries to confirm compliance with the new Validation Rule. d. Try to change the State for customer 151 to A4. Reset the State to AZ. e. Close the Customer Table. Create Queries 1. Prepare the 5 queries in the Romney and Steinbart text starting on page 95 and ending on page 101. 2. Name the queries when you save them as Ainge Sales for Query 1, and as query 2, query 3, query 4 and and query 5 for the remainder. 3. Save the Access Database with all your work. You will use this database for Parts 2 and 3 of the assignment. After Part 3, you will submit a copy of your database for review. 4. Submit your Access file to turnitin: Assignments>Access Part 1. PART 1 of the assignment is complete. Accounting Information Systems Access Assignment S&S Revenue Database Part 2 of 2 Part 1 of the assignment must be completed and submitted to Turnitin before you can begin Part 2. Use the same database file you completed in Part 1. REA Diagrams and Adding a New Table: NOTE: We will draw the REA diagram in class concurrent with the review of Chapter 17. You will NOT submit the REA diagram for grading. 1. REA DIAGRAM: Open the Relationships (Database Tools>Relationships). Draw an REA Diagram that represents the S&S sales database. How is the Sales-Inventory shown in the REA? Which of the REA design rules are not addressed in this diagram? a. The Sales-Inventory Table is a relationship table containing concatenated keys (2 primary keys linked to other primary keys). (This can be shown by adding a rectangle on the link between the Sales and Inventory Entities.) b. Each Event should be connected to at least one other event. Add an entity for Receive Cash c. Each Event should have at least 2 Agents. Add an entity for Salespersons. d. Adding Receive Cash event requires additional entities to complete the REA. Complete the diagram assuming Cashiers handle cash receipts and deposits are made to the bank each day. 2. Add min, max cardinalities to the REA diagram only for the following relationships based on the data in the tables: Inventory to Sales Sales to Customer Sales to Salespersons 3. Complete the remaining cardinalities with the following assumptions: a. Customers can make partial payments or can pay 2 or more invoices with each payment. b. S&S has one bank account for operating activities and one savings account. c. S&S has only one cashier (no Access table is required). 4. Close and save the relationships window by clicking on the Close X in the toolbar window (don't close Access!!). YOU'VE COMPLETE THE REA. You will NOT submit this for grading. 5. ADD A NEW TABLE: The REA indicates a separate table should exist for salespersons. The current design lists salespersons by name. What happens if J. Buck's sister, Jean Buck, is hired. J. Buck cannot represent 2 different people. A better design is to create a new table for salespersons and use a numeric or alphanumeric field to uniquely identify each salesperson. a. Before we add a new table for salespersons, we need to delete the salesperson field from the Sales Table. Open the Sales Table, highlight the salesperson column by clicking on the word \"salesperson.\" Delete the salesperson field by selecting Home>Delete>Delete Column. Click Yes at the prompt. b. Close the Sales Table. NOTE: Deleting the Salesperson attribute invalidates the query you saved as Ainge Sales. You will modify this query later to re-establish the salesperson fields. c. Open the Create toolbar and select Table. A new table will appear. d. Select the design view button under Views in the upper left. When prompted, change the table name to Salespersons. Click OK. 6. Change Field Name to EmployeeID. 7. Note the Data Type is AutoNumber (select AutoNumber if you have a different Data Type). AutoNumber automatically uses the next sequential number when adding a new employee. (This is similar to the standard ERP and accounting system functionality that sequentially numbers all documents/transactions.) 8. Also note that EmployeeID is a Primary Key. 9. Add a field for EmployeeName on the second line. Select Short Text for the Data Type. Make no changes to the Field Properties. 10. Open the Datasheet View. Click Save when asked. 11. Enter the following Employees by typing the name in the EmployeeName field. The AutoNumber function automatically assigns a sequential number. Be sure to type the names in exact order as listed below J. Buck P. Adelphia S. Knight 12. Close the Salesperson Table (you will not be asked to save as Access saves the data entry in real time.) 13. Open the Sales Table in Design View to add the Salesperson as a foreign key. In the Field Name cell below Customer #, type EmployeeID (Note this field name must be typed exactly the same as you typed it in the Salesperson Table.) a. As a Foreign Key, the Data Type must match between the Sales Table and the Salesperson Table. Change the Sales Table EmployeeID Data Type to Number (you cannot use Autonumber herethis can only be used for the primary key.) b. Did the cardinality require every sale be associated with a Salesperson? If yes, then the Required property must be Yes. If no, then the Required property must be No. Change the Required property to Yes. c. Note that duplicates need to be allowed because we expect salespersons to have many sales. d. Return to Datasheet View. Save the data. Click Yes to check the database for your changes (Data integrity rules have been changed.) Select Yes for the next warning, \"Existing data violates the new setting for the \"Required\" property for field \"EmployeeID.\" You will fix this problem by adding the EmployeeID to the sales transactions in the Sales Table. 14. In the Sales Table, enter the EmployeeID as follows: Sales Invoice # EmployeeID 101 1 102 3 103 3 104 1 105 1 15. Close the Sales Table. No Tables should be open. If they are, close all tables. 16. Establish Salesperson Table Relationships: Open the Relationships (Database Tools>Relationships). a. You will not see the new Salespersons Table. Select Show Table from the Relationship Tools/Design toolbar, select Salespersons and click Add. b. Close the Show Table window. c. You will see the Salespersons Table, but it isn't connected to another table. The Salespersons Table Primary Key EmployeeID must be connected the Sales Table Foreign Key. Click on the EmployeeID in the Salespersons Table and drag it to the Sales Table EmployeeID, then release your mouse. d. The Edit Relationships Window will appear. Select Enforce Referential Integrity and both Cascade options. Then click Create. e. Your Salesperson Table is now linked to the Sales Table. Close and Save the Relationships. ADD NEW SALES TRANSACTIONS 1. Open the Sales-Inventory Table. You will see 2 Primary Keys in this table because 2 Primary Keys are required to implement the Concatenated Keys for the Many-to-Many relationship between the Sales and Inventory entities in the REA Diagram. 2. When entering new sales transactions, we must first enter the new sales information in the Sales Table, before we can record the items and quantities in the Sales-Inventory Table. (This order is criticalwithout a valid sales invoice number, you can't add data to the Sales-Inventory Table.) Open the Sales Table and enter the following transactions by entering the appropriate data in each table. Complete each transaction before moving to the next. Sales Table: Sales Invoice Date Customer # EmployeeID 106 11/20/2018 155 2 107 11/21/2018 154 3 Sales-Inventory Table (2 records are required for invoice 106): Sales Invoice 106 107 Item # 10 / 30 40 Quantity 2 / 1 3 3. Close all open tables. Save if asked. RUN QUERIES 1. Open the saved query for Ainge Sales. Access will ask you to \"Enter Parameter Value\" for Sales.Salesperson because the field was deleted from the Sales Table and there is a null value in the query. Click Ok. Note that the query now has blanks under Salesperson. a. Move to design view. Add the Salesperson Table to table view above by selecting Show Table from the toolbar. Highlight the Salesperson Table and click Add. Close the Show Table window. b. Replace the old Salesperson field name with EmployeeID from the new Salesperson Table. Click on the dropdown list and select EmployeeID. Showing the EmployeeID will help distinguish between two employees with the same name. c. Click on the EmployeeName in the Salespersons Table and drag EmployeeName to the first available unused Field box. d. Place the cursor on/above EmployeeName and when the cursor turns to a black arrow, right click to turn the column black. Release the mouse. e. Right click on the blackened EmployeeName field column and drag it to the left of Customer Name field. Release the mouse. f. It is better to use the Primary Key for a search criteria than a name or description of the record. We fixed this for Salespersons, but not Customer. Go to the field for Customer Name and change it to Customer #. Change the Criteria to 151. g. We don't need to show Customer # 151 or D. Ainge on the result since the query title is Ainge Sales. Uncheck the Show box under Customer #. (see screen shot below.) h. Click on ! Run in the toolbar to see you query results. i. Close and save the query (name remains Ainge Sales). 2. REQUIREMENT: Identify the Inventory items purchased by each customer. a. This seems like a simple query. Create a new Query Design and select Inventory and Customer Tables from the Show Table window. Close the Show Table window. b. You will note that the tables are not connected because there isn't a direct relationship between. c. Select the inventory Description for the first field and Customer Name for the second. Will this query deliver the requested information? d. Click !Run. The result is a pairing of all inventory items with all customers. This doesn't satisfy the requirement. The query result is flawed because the Inventory and Customer tables are not directly related. e. To fix this report, return to the design view. Click on Show Tables in the toolbar and add the Sales and Sales-Inventory Tables to the query. Note that the Inventory and Customer Tables are now connected through the Sales and Sales-Inventory Tables. No attributes will be selected from the Sales and Sales-Inventory Tables, but the tables are required to connect Customer Name to Inventory Description in the Customer and Inventory tables. f. Click !Run. The result meets the requirement. Save the query as Customer-Inventory. Add Tables and Data for Cash Receipts (refer to prior instructions or Access Help for detail procedures.) 1. Implement the cash receipt event in Access. Since a many-to-many relationship exists between Sales and Cash Receipts, you need to include a relationship table. Select appropriate Data Type and Field Properties. Set Primary Keys. In the Cash Receipts-Sales Table, you will need to set both the CashReceiptID and the Sales Invoice # as Primary Keys (this is a concatenated key.) Make sure the name and data type of Foreign Keys match the related Primary Key. Create the following tables and attributes: a. Cash Receipts Table: CashReceiptID (Set Data Type to Autonumber), CRDate, Customer #, CustomerCheck#, BankAcctID b. Cash Table: BankAcctID (set as Number, not AutoNumber), Bank, BankAcct#, AccountType c. Cash Receipts-Sales Table: CashReceiptID, Sales Invoice #, AmountReceived (This is a relationship table. Set the Data Types for CashReceiptID and Sales Invoice # to match the primary key in the related tables. Do not use Autonumber againyou can only use this on the Cash Receipts Table. These Primary Keys should be required and will allow duplicates.) d. Review Primary and Foreign Key data types to ensure they are consistent across tables, eg., Customer # should be Data Type \"number\" in the Cash Receipts Table. 2. Return to the Database Tools>Relationship screen to add your new tables and link them to the appropriate tables. After you're done, the Access tables should be linked consistent with your REA diagram and enforce Referential Integrity. 3. Add Bank Account data (This must be entered before the Cash Receipts Table data because you will need the BankAcctID field populated to implement the Foreign Key in the Cash Receipts Table.) 4. Add Cash Receipts data, which is split between Cash Receipts Table and Cash Receipts-Sales Table. Enter the Cash Receipt Table data before entering the Cash Receipts-Sales Table data. The CashReceiptID will be automatically generated in the Cash Receipt Table and you will use this autogenerated number in the Cash Receipts-Sales Table: Add cost attribute to inventory 1. Close all open tables and windows. Open the Inventory Table. We want to change the data for Unit Price to include pennies. In the Design View, select the Unit Price field. In Field Properties, change Format to Fixed (will show 2 decimal places.) 2. In the Design view, add a new attribute named Cost and select Data Type \"Number.\" In Field Properties, change Format to Fixed (will show 2 decimal places.) 3. Return to Datasheet View. Save and click Yes, if asked, to acknowledge data integrity rules have been changed. Add the following cost data for the inventory items: Create Sales Order Gross Profit Report 1. Understanding the profitability of individual orders and customers gives management information that can be used to change sales strategies, pricing or product mix. 2. This exercise will show you how to use a query to calculate amounts that can be used in subsequent data analysis. Each saved query becomes another table of data, some of which is replicated from original source tables and some with newly created attributes/fields. 3. Neither the Sales table nor the Sales-Inventory table includes the total sales amount of each invoice. We'll create a query to calculate the extended sales amount for each invoice/item combination. Create a new query and select the Sales-Inventory table and the Inventory table. Select Sales Invoice #, Item #, Quantity, and Unit Price for the first 4 fields in the query. For the fifth field, use the Builder function to calculate \"Extended Price.\" (place the cursor on the empty field and click on the Builder icon in the Design toolbar. Or you can right-click and select \"Build\".) Type Extended Price:, then use the Expression Elements and Expression Categories to select the attributes for your formula. The formula is Quantity * Unit Price. If appears in your formula, delete it from the formula in the Builder window. Click OK. 4. Run the query. You will see all Sales Invoice # and Item # combinations with an extended price. However, the Extended Price doesn't show cents. Return to Design View, select the Extended Price field and click on the \"Property Sheet\" button in the upper right of the Design toolbar (or rightclick and select Properties). This will open a column on the right side of the window that allows you to further customize the field. Change the Format to \"Fixed\" using the drop down list. Run the query again and you will have 2 decimal places on the Extended Price. Close the Property Sheet when done. 5. We need the costs of goods sold for each item sold. Return to Design View and add Cost as the next field. Follow the steps in 3. and 4. above to calculate Extended Costs and add 2 decimal places. 6. Run the query and save as \"Extended.\" 7. We now have a query with the extended prices and costs for all sales transactions. We will use this table to determine the amount of Gross Profit for each Invoice, for each Item # and for each Customer. We'll start with Invoice. a. Create a new query and select the Sales Table. Move to the Queries tab and select the Extended Query. These should be linked by Sales Invoice #. b. Select Date and Sales Invoice # from the Sales Table. Select Extended Price and Extended Cost from the Extended Query. In the next field, use the Builder function to calculate gross profit, which is price minus cost. You will need to select the attributes from the Queries list>Extended. Click OK when finished. c. We want to add cents to the Extended Price, Extended Cost, and Gross Profit output. Follow the steps in 4. above to add 2 decimal places. Close the Property Sheet when done. d. We want to aggregate the Gross profit by Sales Invoice. In the Design View, Click on the Sum button in the Design toolbar. This will add the Total line to the design. We want to \"Group by\" Date and Sales Invoice # (There is only one date for each invoice so the grouping will be on the invoice #.) We want to show the total Extended Price, Extended Cost and Gross Profit for each invoice, so select Sum for each of these fields. The Sum function will total the attributes for each unique \"Group by\" attribute. Run the query. e. Save the query as GP by Invoice. f. Next we want to create a query to determine gross profit by inventory item. Begin a new query and select Inventory Table and Extended Query. The tables are linked by Item #. Select Item # and Description from the Inventory Table (we need description because the number doesn't tell us much about the item.) Select Extended Price and Extended Cost from the Extended Query. g. Follow the procedures in b. and c. above to calculate and format Gross Profit. h. We want to aggregate the Gross profit by Item. In the Design View, Click on the Sum button in the Design toolbar. This will add the Total line to the design. Select \"Group by\" for Item # and Description. Select Sum for Extended Price, Extended Cost and Gross Profit for each item. Run the query. i. We can easily add totals to query results. Click on the Totals button in the Datasheet View, Home toolbar. This will add a row titled Total. Select Sum for sales, costs and gross profit. Save the query as GP by Inventory. Problems-Run queries to provide the information required. You do not have detail instructions and screen shots. 1. Sum the cash receipts for the month of November 2018. The output should be a single figure showing the total for the month. Save the query as Cash Receipts for November. 2. Aggregate the sales, cost of sales and gross profit by customer for all of 2018. In the Datasheet view, use the Sum function to total the columns of all data. Save as GP by Customer. 3. Determine the Outstanding Accounts Receivable balance, which is total invoices less total cash receipts. There can be different ways to calculate Outstanding Accounting Receivablehere is a suggestion. You will need the following data: Sum of all invoices and sum of all cash receipts. A suggestion is to run 3 queries. The first is to determine total invoices save as AR 1. The second is to determine total cash receiptssave as AR 2. Now use these queries to calculate Outstanding Accounts Receivablesave as AR 3 (see output below.) PART 2 SUBMISSION INSTRUCTIONS: 1. Save the Access Database with all your work. 2. Submit your Access file to turnitin: Assignments>Access Part 2 3. Email your completed database to aisassignments@gmail.com CONGRATULATIONS: YOU ARE FINISHED WITH THE S&S ACCESS ASSIGNMENT!! \f