Day 20: Homework # 4 overview - PowerPoint PPT Presentation

giulia
day 20 homework 4 overview n.
Skip this Video
Loading SlideShow in 5 Seconds..
Day 20: Homework # 4 overview PowerPoint Presentation
Download Presentation
Day 20: Homework # 4 overview

play fullscreen
1 / 17
Download Presentation
Day 20: Homework # 4 overview
100 Views
Download Presentation

Day 20: Homework # 4 overview

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Day 20:Homework # 4 overview AkhilaKondai akhila.kondai@mail.wvu.edu October 28, 2013

  2. ANNOUNCEMENTs • Homework # 4 is due on 11/01/2013 • MyITLab Lesson D is due on 11/04/2013 • Homework # 5 is now posted and available at http://cs101.wvu.edu/instructors/kondai/assignments/. It would be due for 11/08/2013 • Start working on them right away! • Email me or visit open lab for assistance in home works or MyITLab.

  3. Homework 4 Overview Open the directions file online to follow the flow of the assignment: • Create database and 3 tables • Populate and relate as described • Create 5 Queries • Create a Form with Subform • Create a Report • Analysis Questions

  4. Step2 • Create ribbon->table • Switch to design view and name it as GoverningBodies • Remove the Id field • Create the fields GoverningBodyAbbrvandGoverningBodyName with text datatype • Enter the records into table

  5. Step3 • Create a table with name Colleges • Switch to design view and remove the Id field • Create the fields CollegeAbbrv, CollegeName, GoverningBodyAbbrv with text datatype • Database Tools->Relationships • Add the two tables GoverningBodies and Colleges andcreate a relationship between them • Enforce referential integrity and enable cascade updates, but do not enable cascade deletes. • Enter the records into table

  6. Step5 • Create a table named Enrollment • Leave the Id field as primary key field with autonumber data type. Rename Id to EnrollmentID. • Create the fields • DateYear (Fixed type number field with no decimal places • CollegeName(which looks up the values in Colleges table) • InStateFTE(Fixed type number field with 1 decimal place) • OutOfStateFTE(Fixed type number field with 1 decimal place) • Appropriations (Currency with no decimal places) • Enter the records into table

  7. Step6A • Create->Query Design • Add tables Colleges and Enrollment • [Colleges] CollegeName • [Enrollment]DateYear, InstateFTE, OutOfStateFTE, Appropriations • Give ascending order for CollegeName and then for DateYear. • Run the Query and save it as Query 6A.

  8. Step6B • Create->Query Design • Add Enrollment and Colleges table • Add the fields CollegeName and DateYear • Insert the calculated field in next empty column • Design->Query Setup group->Builder • Add the calculated fields • Appropriation per In-state: [Enrollment.Appropriations]/[Enrollment.InStateFTE] • Appropriation per total : [Enrollment.Appropriations]/([Enrollment.InStateFTE] + [Enrollment.OutOfStateFTE])

  9. Step6C • Create->Query Design • Add Colleges and Enrollment table • Add the fields CollegeName, InStateFTE, OutOfStateFTE, Appropriations • Add the totals row • Change the contents of total row from Group By to Avg for InStateFTE, OutOfStateFTE and Appropriations fields.

  10. Step6D • Create->Query Deign • Add the tables Enrollment and Colleges • [Enrollment] DateYear,InStateFTE, OutOfStateFTE, Appropriations • [Colleges] CollegeName • Add the Totals row and change the content of total row to sum for InStateFTE, OutOfStateFTEand Appropriations

  11. Step6D continued… • Change the content of total row to Where for the CollegeName field • Give "West Virginia University" Or "Potomac State College of WVU" Or "WVU Institute of Technology" Or "WVU Parkersburg“ as criteria for Collegename field

  12. Step6E • Create->Query Design • Add the tables GoverningBodies and Colleges • [GoverningBodies] GoverningBodyName • [Colleges] CollegeAbbrv • Add the Totals row and change the content of total row to Count for CollegeAbbrv field • Give Ascending order for GoverningBodyname field

  13. Step7 • Create->Form Wizard • Select Colleges table and add CollegeName field • Select Enrollment table and add all the fields from that table • Click Next 3 times and change the Names of form and subform as indicated in instructions • Click Finish

  14. Step8 • Create->Report Wizard • Select Query6A and add all the fields • Click next and select by Colleges • Click next two times and give sorting order for the fields as indicated in instructions • Click next and select stepped layout and landscape orientation • Click next and give the name for the Report and click finish.

  15. Step8 Continued…. • Switch to layout view and adjust the width of the fields in Report

  16. Step 9 • New Table ‘AnalysisQuestions’ with 2 fields • QuestionNumber • Field values will be the question designators from the assignment (9a, 9b, 9c…) • Answer • Field values will be your answers to the given question • Make it of data type “Memo” • This table is not related to the others, merely a place to put the answers to your Analysis Questions. • Make sure answers are RECORDS in datasheet view, and NOT TYPED IN DESIGN VIEW ! ! !

  17. Questions ?