day 20: homework # 4 overview akhila kondai october 28, 2013
DESCRIPTION
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 QuestionsTRANSCRIPT
![Page 2: DAY 20: HOMEWORK # 4 OVERVIEW Akhila Kondai October 28, 2013](https://reader034.vdocuments.us/reader034/viewer/2022051123/5a4d1adc7f8b9ab059974f81/html5/thumbnails/2.jpg)
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.
![Page 3: DAY 20: HOMEWORK # 4 OVERVIEW Akhila Kondai October 28, 2013](https://reader034.vdocuments.us/reader034/viewer/2022051123/5a4d1adc7f8b9ab059974f81/html5/thumbnails/3.jpg)
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
![Page 4: DAY 20: HOMEWORK # 4 OVERVIEW Akhila Kondai October 28, 2013](https://reader034.vdocuments.us/reader034/viewer/2022051123/5a4d1adc7f8b9ab059974f81/html5/thumbnails/4.jpg)
STEP2
• Create ribbon->table• Switch to design view and name it as
GoverningBodies • Remove the Id field• Create the fields GoverningBodyAbbrv
and GoverningBodyName with text datatype
• Enter the records into table
![Page 5: DAY 20: HOMEWORK # 4 OVERVIEW Akhila Kondai October 28, 2013](https://reader034.vdocuments.us/reader034/viewer/2022051123/5a4d1adc7f8b9ab059974f81/html5/thumbnails/5.jpg)
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
and create a relationship between them• Enforce referential integrity and enable cascade
updates, but do not enable cascade deletes. • Enter the records into table
![Page 6: DAY 20: HOMEWORK # 4 OVERVIEW Akhila Kondai October 28, 2013](https://reader034.vdocuments.us/reader034/viewer/2022051123/5a4d1adc7f8b9ab059974f81/html5/thumbnails/6.jpg)
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
![Page 7: DAY 20: HOMEWORK # 4 OVERVIEW Akhila Kondai October 28, 2013](https://reader034.vdocuments.us/reader034/viewer/2022051123/5a4d1adc7f8b9ab059974f81/html5/thumbnails/7.jpg)
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.
![Page 8: DAY 20: HOMEWORK # 4 OVERVIEW Akhila Kondai October 28, 2013](https://reader034.vdocuments.us/reader034/viewer/2022051123/5a4d1adc7f8b9ab059974f81/html5/thumbnails/8.jpg)
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.InState FTE]• Appropriation per total :
[Enrollment.Appropriations]/([Enrollment.InState FTE] + [Enrollment.OutOfStateFTE])
![Page 9: DAY 20: HOMEWORK # 4 OVERVIEW Akhila Kondai October 28, 2013](https://reader034.vdocuments.us/reader034/viewer/2022051123/5a4d1adc7f8b9ab059974f81/html5/thumbnails/9.jpg)
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.
![Page 10: DAY 20: HOMEWORK # 4 OVERVIEW Akhila Kondai October 28, 2013](https://reader034.vdocuments.us/reader034/viewer/2022051123/5a4d1adc7f8b9ab059974f81/html5/thumbnails/10.jpg)
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, OutOfStateFTE and Appropriations
![Page 11: DAY 20: HOMEWORK # 4 OVERVIEW Akhila Kondai October 28, 2013](https://reader034.vdocuments.us/reader034/viewer/2022051123/5a4d1adc7f8b9ab059974f81/html5/thumbnails/11.jpg)
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
![Page 12: DAY 20: HOMEWORK # 4 OVERVIEW Akhila Kondai October 28, 2013](https://reader034.vdocuments.us/reader034/viewer/2022051123/5a4d1adc7f8b9ab059974f81/html5/thumbnails/12.jpg)
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
![Page 13: DAY 20: HOMEWORK # 4 OVERVIEW Akhila Kondai October 28, 2013](https://reader034.vdocuments.us/reader034/viewer/2022051123/5a4d1adc7f8b9ab059974f81/html5/thumbnails/13.jpg)
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
![Page 14: DAY 20: HOMEWORK # 4 OVERVIEW Akhila Kondai October 28, 2013](https://reader034.vdocuments.us/reader034/viewer/2022051123/5a4d1adc7f8b9ab059974f81/html5/thumbnails/14.jpg)
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.
![Page 15: DAY 20: HOMEWORK # 4 OVERVIEW Akhila Kondai October 28, 2013](https://reader034.vdocuments.us/reader034/viewer/2022051123/5a4d1adc7f8b9ab059974f81/html5/thumbnails/15.jpg)
STEP8 CONTINUED….
• Switch to layout view and adjust the width of the fields in Report
![Page 16: DAY 20: HOMEWORK # 4 OVERVIEW Akhila Kondai October 28, 2013](https://reader034.vdocuments.us/reader034/viewer/2022051123/5a4d1adc7f8b9ab059974f81/html5/thumbnails/16.jpg)
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 ! ! !
![Page 17: DAY 20: HOMEWORK # 4 OVERVIEW Akhila Kondai October 28, 2013](https://reader034.vdocuments.us/reader034/viewer/2022051123/5a4d1adc7f8b9ab059974f81/html5/thumbnails/17.jpg)
Questions ?