Fluent Essays

Access Assignment Two different assignments for two different people Using Excel And access final_assessment__itad1100_advanced_it_skills___group_c.docx itad1100___final_assessment___section_3___set_c__1_.pdf Unformatted Attachment Preview May 21, 2020 [ITAD1100/ADVANCED IT SKILLS] INSTRUCTIONS TO STUDENTS • The student must personally answer the assignment questions and submit it electronically through the Word document. • The assignment should be uploaded on the e-learning. • The e-learing submission platform will be closed as per the due date and time. • The final submitted assignment within the 48 hours period will be considered as final (Maximum number of submissions is 3). • The maximum acceptable level of similarity is 25\%. • In case of duplicated assignments between two students or more, a zero mark will be given and the case will be investigated. • Illegal programs or methods used to conceal simialrites are prohibited and studnets will be investgated. • References must be documented properly. • It is prohibited to share the assignment with anyone or any form. Violators will be sanctioned as per the college policies. This Final Assessment is CONFIDENTIAL. Total Marks : 40 General Instructions: • • • • Final Assignment of Advanced IT Skills is from 21-05-2020 at 9:00 AM to 23-05-2020 at 9:00 AM. Final Assignment contains two(2) parts. Part-A (Access) and Part-B (Excel). For any questions or clarification students can ask from 9:00 AM to 2:00 PM Daily.(Not all the time/Day). After finishing all the questions in Assignment,Please send the Assignment through the Turnitin link in E-Learning in two(2) separate parts- Part-A (Access) and Part-B (Excel) . 1 May 21, 2020 [ITAD1100/ADVANCED IT SKILLS] MS ACCESS 2016 (20.0 Marks) I. Using MS Access 2016 create a blank database and save it as LoanDB. Create the following tables, relationship and forms for the database LoanDB. (0.25 Mark) 1. Design a new table and name it as Client with the following data fields and set their properties. (0.25 Mark) Field Name Data Type Field Size ClientID Number ClientName Short text IssueDate Date/Time Gender Lookup Wizard (Male, Female) Offers Yes/No Format (0.6 Mark) Integer (0.6 Mark) 35 (0.6 Mark) Medium Date (0.6 Mark) (0.6 Mark) Yes/No 2. Apply a required field to ClientName. (0.5 Mark) 3. Apply a primary key to ClientID. (0.5 Mark) 4. Insert the following records into Client table. (1.00 Mark) ClientID ClientName IssueDate Gender Offers 101 Shivakumar 24-Nov-16 Male Yes 102 Sheelavathy 12-Feb-17 Female No 103 Archana 11-Oct-19 Female No 104 Naveenchand 26-Mar-20 Male Yes 105 Bindushree 10-Sep-19 Female Yes 5. Design a new table and name it as Loan with the following data fields and set its properties. (0.25 Mark) 2 May 21, 2020 [ITAD1100/ADVANCED IT SKILLS] Field Name Data Type Field Size LoanID Number Integer ClientID Number Integer LoanName Short text 35 LoanAmount Number Integer Format (0.6 Mark) (0.6 Mark) (0.6 Mark) (0.6 Mark) (0.35 Mark) MonthlyPayment Currency 6. Apply a required field to LoanName. (0.5 Mark) 7. Apply a primary key to LoanID. (0.5 Mark) 8. Insert the following records into Loan table. (1.0 Mark) LoanID ClientID LoanName LoanAmount Monthly Payment 201 101 Personal 14000 $100.00 202 102 Car 16000 $200.00 203 102 Furniture 22000 $400.00 204 104 Education 17000 $300.00 205 105 Housing 20300 $350.00 9. Apply a relationship between Client table and Loan table. (0.5 Mark) 10. Design a Form based on the Client table and save it as ClientForm. (0.5 Mark) 11. Design a Form based on the Loan table and save it as LoanForm. (0.5 Mark) II. Create the following Queries and save each query with question number (e.g. Query1): 1) Create a query to display Client id, Client name and gender and sort them in descending order by Client name. 2) (1.0 Mark) Create a query to display Client name of those with offers. 3) Create query to display the Client id and Client name of all female Customers. (1.0 Mark) (1.0 Mark) Create a query to display the Client ID and Issue date in which Issue date is after 10-Mar-19. 3 4) May 21, 2020 [ITAD1100/ADVANCED IT SKILLS] (1.0 Mark) 5) Create a query to display LoanID and Loan name that starts with the letter “E”. 6) Create a query to display Customer name that has letters “an”. 7) Create a query to display the total of Monthly Payment. 8) Create a query to display the Client names with their lowest Monthly Payment. (1.0 Mark) (1.0 Mark) (1.0 Mark) (1.0 Mark) MS-EXCEL 2016 (20.0 Marks) III. Scenario: The below table shows the details of Stock in different countries. As per the below given information, create the table in MS-Excel 2016 same as given below, save it as STOCK , and fill the formulas in the Excel Sheet. Be sure to write the data in the correct column and row to avoid errors in the formulas. (2.5 Marks) 4 May 21, 2020 [ITAD1100/ADVANCED IT SKILLS] IV.INSTRUCTIONS / FORMULAS MARKS 1. Insert Roman Number for SNO in the column B3:B7. 0.5 2. Calculate Total Stock as the sum of Stock Recieved, Stock Available, and Stock Shipped. 1 Display in H3:H7. 3. Display the Remarks in J3:J7 as HIGH STOCK if the Total Cases is above or equal to 800; 1 otherwise, display LOW STOCK. 4. Find the Sum of all Total Stock and display in H8. 0.5 5. Find the median of Total Stock and display in H9. 0.5 6. Display the number of countries without Stock Available in H10. 1 7. Display the number of HIGH STOCK countries and fill in H11. 1 8. Find the country name with maximum Total Stock and display in H12 1 9. Apply conditional formatting for Remarks = HIGH STOCK with “Green Fill with Dark Green 1 Text”. 10. Display the second highest Stock Shipped in H13. 1 11. Count those with Stock Shipped above or equal to 200 and display in H14. 1 12. Find the total of Stock Received above 600 and fill in H15. 1 13. Insert a 2D Stacked Bar chart for Country and Total Stock. 1 14. Apply chart style as Style 4. 0.5 15. Apply Data Labels to the Inside End chart. 0.5 16. Calculate the percentages of Stock Received and Stock Shipped from total Stock and display 2 in C20:C24 and D20:D24, respectively. 17. Insert a 3D pie chart by selecting the row of Germany and the header row. 1 18. Apply chart style as Style 6. 0.5 19. Apply Legend at top of the chart. 0.5 20. Apply filter to Country column (in the first table) and display only Spain details. 0.5 21. Rename Sheet1 as “Stock” and change the tab color to “Orange”. 0.5 -- End of Final Assessment –Review your answer. Aim for excellence! ☺ 5 May 21, 2020 [ITAD1100/ADVANCED IT SKILLS] ‫الكلية التقنية بشناص‬ Shinas College of Technology FINAL ASSESSMENT (ASSIGNMENT) AY 2019-2020 - SEMESTER- II INFORMATION TECHNOLOGY ‫تعلـــــيمات للطــــــلبة‬ INSTRUCTIONS TO STUDENTS • The student must personally answer the assignment questions and submit it electronically through the Word document. • The assignment should be uploaded on the e-learning. • The e-learing submission platform will be closed as per the due date and time. • The final submitted assignment within the 48 hours period will be considered as final (Maximum number of submissions is 3). • The maximum acceptable level of similarity is 25\%. ‫• يجب حل االتقييم من قبل الطالب شخصيا ويكون إلكترونيًا باستخدام برنامج‬ .Word .e-learning ‫• يجب تحميل إجابات التقييم على منصة التعليم اإللكتروني‬ ‫ ساعة) من بداية الوقت المحدد للتقييم‬48( ‫• سيتم غلق منصة التسليم بعد‬ .‫النهائي‬ 3 ‫ وبحد أقصى‬،‫ ساعة‬48 ‫• سيتم اعتماد آخر تسليم قدمه الطالب خالل فترة الـ‬ .‫مرات‬ .\%25 ‫• الحد األقصى المسموح به للتشابه هو‬ ‫• في حالة وجود تطابق بين طالبين او أكثر في حل الواجب الدراسي سوف يعطى‬ • In case of duplicated assignments between two students or more, a zero mark will be given and the case will be investigated. ‫ ) من‬80 ( ‫الطالبان صفر و سيتم إحالتهما الى لجنة التحقيق و ذلك وفقا للمادة‬ • Illegal programs or methods used to conceal simialrites are prohibited and studnets will be investgated. • References must be documented properly. • It is prohibited to share the assignment with anyone or any form. Violators will be sanctioned as per the college policies. This Final Assessment is CONFIDENTIAL. .‫قانونية وفي حالة اكتشاف ذلك ستطبق على الطالب األنظمة المتبعة في ذلك‬ 1 .‫اللوائح الداخلية للكليات التقنية‬ ‫• يحظر على الطالب استخدام أي برامج أو طرق تخفي نسبة التشابة بطريقة غير‬ .‫• يجب على الطالب كتابة المراجع المستخدمة‬ ‫ منصة‬/‫• يحظر مشاركة التقييم النهائي بأي شكل من األشكال مع أي شخص‬ ‫وسيتم معاقبة المخالفين وفقًا لسياسات الكلية (هذا التقييم النهائي سري وملك‬ .)‫للكلية‬ [ITAD1100/ADVANCED IT SKILLS] May 21, 2020 Total Mark: 40 General Instructions: • • • • Final Assignment of Advanced IT Skills is from 21-05-2020 at 9:00 AM to 23-05-2020 at 9:00 AM. Final Assignment contains two(2) parts. Part-A (Access) and Part-B (Excel). For any questions or clarification students can ask from 9:00 AM to 2:00 PM Daily.(Not all the time/Day). After finishing all the questions in Assignment,Please send the Assignment through the Turnitin link in E-Learning in two(2) separate parts- Part-A (Access) and Part-B (Excel) . . PART -A MS ACCESS 2016 (20.0 Marks) I. Using MS Access 2016 create a blank database and save it as AirplaneDB. Create the following tables, relationship and forms for the database AirplaneDB. (0.25 Mark) 1. Design a new table and name it as Passenger with the following data fields and set their properties. (0.25 Mark) Field Name Data Type PassengerID Number PassengerName Short Text CurrentMiles Number LoyaltyType Lookup Wizard (Gold, Silver) FrequentFlyer Yes/No Field Size Format Integer (0.6 Mark) 40 (0.6 Mark) Integer (0.6 Mark) (0.6 Mark) Yes/No 2. Apply a required field to PassengerName. (0.5 Mark) 3. Apply a primary key to PassengerID (0.5 Mark) 4. Insert the following records into Passenger table. (1.00 Mark) (0.6 Mark) PassengerID PassengerName CurrentMiles LoyaltyType FrequentFlyer 5510 Hamed Ali Salem Al-Hamdani 25000 Gold Yes 5511 Rajesh Sherimon 11500 Silver No 5512 Harold Bariuan 32200 Gold Yes 5513 Maryam Naif Moosa Al-Amri 8100 Silver Yes 5514 Marina Batobalani 10600 Silver No 2 May 21, 2020 [ITAD1100/ADVANCED IT SKILLS] 5. Design a new table and name it as Flight with the following data fields and set its properties. (0.25 Mark) Field Name Data Type Field Size Format FlightNum Number Integer PassengerID Number Integer (0.6 Mark) (0.6 Mark) Destination Short Text 25 (0.6 Mark) DepartureDate Date/Time AirFare Currency Medium Date (0.6 Mark) (0.35 Mark) 6. Apply a required field to FlightNum. (0.5 Mark) 7. Apply a primary key to Destination. (0.5 Mark) 8. Insert the following records into Flight table. (1.0 Mark) FlightNum PassengerID Destination DepartureDate AirFare 225 5511 Australia 05-Jan-20 522.500 226 5513 Georgia 17-Jan-20 187.400 227 5511 Japan 20-Jan-20 443.225 228 5511 Malaysia 14-Feb-20 250.000 229 5513 Turkey 17-Feb-20 236.210 9. Apply a relationship between Passenger table and Flight table. (0.5 Mark) 10. Design a Form based on the Passenger table and save it as PassengerForm. (0.5 Mark) 11. Design a Form based on the Flight table and save it as FlightForm. (0.5 Mark) II. Create the following Queries and save each query with question number (e.g. Query1): 1) Create a query to display flight number, destination, and airfare and sort them in descending order by destination. (1.0 Mark) 2) Create a query to display the passenger names with their highest airfare. (1.0 Mark) 3) Create a query to display the destination and departure date in which departure date is before 14Feb-20. (1.0 Mark) 4) Create a query to display flight number and destination that contains the letters “AL”. (1.0 Mark) 5) Create a query to display the sum of current miles. (1.0 Mark) 6) Create a query to display passenger name of those who are frequent flyers. (1.0 Mark) 3 May 21, 2020 [ITAD1100/ADVANCED IT SKILLS] 7) Create a query to display passenger name that has “A” as its second letter. (1.0 Mark) 8) Create query to display the ID and name of all passengers who has loyalty type of “Gold”. (1.0 Mark) PART-B MS-EXCEL 2016 (20.0 Marks) III. Scenario: The below table shows the details of online food ordering apps that have become the trend since restaurants has closed due to the COVID-19 pandemic. As per the below given information, create the table in MS-Excel 2016 same as given below, save it as FoodOrder, and fill the formulas in the Excel Sheet. Be sure to write the data in the correct column and row to avoid errors in the formulas. 4 (2.5 Marks) May 21, 2020 [ITAD1100/ADVANCED IT SKILLS] IV. INSTRUCTIONS / FORMULAS 1. Insert Roman Number for SNO in the column B3:B7. 2. Calculate Total Orders Per Day as the sum of Cakes, Meals, and Groceries in H3:H7. 3. Display the Remarks in J3:J7 as POPULAR if the Total Orders is above or equal to 4,000; otherwise, display LESS POPULAR. 4. Find the sum of all Total Orders and display in H8. 5. Find the median of Meals and display in H9. 6. Display the number of apps with no services for Cake orders in H10. 7. Display the number of LESS POPULAR apps and fill in H11. 8. Find the app with maximum number of Groceries and display in H12. 9. Apply conditional formatting for Remarks = POPULAR with “Light Red Fill”. 10. Display the fourth highest Total Order in H13. 11. Count those with Meals below or equal to 3,000 and display in H14. 12. Find the sum of Groceries above 1,500 and fill in H15. 13. Insert a 3D Pie chart for App and Total Orders. 14. Apply chart style as Style 7. 15. Apply Data Labels at Outside End of chart. 16. Calculate the percentages of Cakes and Groceries from Total Orders and display in C20:C24 and D20:D24, respectively. 17. Insert a Clustered Column chart by selecting the row of Akeed and the header row. 18. Apply chart style as Style 5. 19. Apply Legend at the bottom. 20. Apply filter to App column (in the first table) and display only Daleel 1010 details. 21. Rename Sheet1 as “ORDERS” and change the tab color to “purple”. -- End of Final Assessment –Review your answer. Aim for excellence! ☺ 5 MARKS 0.5 1 1 0.5 0.5 1 1 1 1 1 1 1 1 0.5 0.5 2 1 0.5 0.5 0.5 0.5 ... Purchase answer to see full attachment