Access Database ICE2 - Relationships and Queries

Access Database ICE2 - Relationships and Queries - IT305...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
IT305 – Database ICE #2 Introduction to Microsoft Access – Relationships and Queries You should have completed the Introduction to Microsoft Access – Databases and Tables ICE prior to starting this ICE. You should also have added the tables of the design being implemented (see figure below) into Company.mdb. NOTE: The current version of MS Access is 2003 and all directions are for that version. cadetID <<PK>> cadetLastName cadetFirstName major classYear Cadet disciplineID <<PK>> disciplineType amount disciplineDate reason isFromBoard cadetID Discipline sportName <<PK>> academicYear <<PK>> Intramural positionID <<PK>> positionTitle academicYear CompanyPosition positionTitle <<PK>> Position cadetID <<PK>> sportName <<PK>> academicYear <<PK>> sportRole CadetPlaysIntramural sportName <<PK>> Sport M M M 1 M -cadetID <<PK>> -positionID <<PK>> CadetHoldsCompanyPosition M Data Reference Tables academicYear <<PK>> AYSemester 1. The most important aspect of a database is being able to query the data, to get an answer to a question based on the data stored in the database. Simple Query with Wizard; Sorting MS Access provides two ways to create a query. For simple queries, the Wizard can be used. To create a query using the Wizard, click on Queries under Objects and double click on the Wizard option. In the Simple Query Wizard window, you will need to select the table(s) or query(ies) on which to base your query. To get a listing of the cadets with their class years, use the pull-down to choose The Cadet table from the list of tables and queries (no queries are there yet) – select the following fields (in this order) for your query (highlight each field and use the arrow “>”): Field cadetLastName cadetFirstName classYear Click on Next and name the query, StudentGradYears. Select to open it for viewing, and click on Finish. View the results. From the main tool bar, switch to Design View. Fall 2008 Page 1 of 8 MS Access – Relationships & Queries
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
o To add cadet majors to the query, click on the major field and drag it to the first open column in the query. Check out the results in Datasheet View. Go back to Design View. o To change the result to be in class year order, click in the "Sort" row under the classYear field. From the pull-down, select Ascending. Check out the results. o To also alphabetize by name, sort by last name and first name. Notice that you have lost the class year order. To have the names alphabetized within the class year, MS Access requires that the classYear column in the query must be farther to the left than those of the name columns (last name must also be to the left of first name). In Design View, highlight the classYear column, click on the thin gray box above the classYear field and drag the classYear column to the first column of the query. (If this is difficult, you can always delete the classYear column, re-drag it from the Cadet table to the first column of the query, and sort again by classYear.) Check results, and close the query, saving changes. The
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page1 / 8

Access Database ICE2 - Relationships and Queries - IT305...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online