Day 20:Homework # 4 overview AkhilaKondai firstname.lastname@example.org October 28, 2013
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.
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
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
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
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
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.
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])
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.
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
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
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
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
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.
Step8 Continued…. • Switch to layout view and adjust the width of the fields in Report
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 ! ! !