[PDF] Essential Access Exercises.pdf

View - Download Essential Access Exercises.pdf

Previous PDF

Next PDF

Essential Access

Exercises

Exercises to accompany Essential Access Course

Books1& 2

Sample files for use with these exercises can be found on the shared Teaching(T:) drive, accessible from any classroom PC and other centrally managed computers. The folder needed is: the Essential Access course filesfolderto your own documents location. The sample file to be used is indicated at the start of each set of exercises.

Version: January2020

1 ~ Open the Access database StudentRecords1.accdbfor these exercises Open the Students table in datasheet view and enter the following new student either by navigating to the empty field or using the 'New record' button. Note the pencil symbol when editing:

10510PaulHitchp.hitch@gmail.co.uk

Enter yourself as a new student. Save by moving to a different record or using the Save button.

Find student number ϭϬϯϯϬ, Tara Maples. Correct her email to LJĂŚŽŽ͘ĐŽŵ.

Close Students and open the Modules table. Find the Plasma Physics module (moduleID

PHY201) and set theroomIDto G/A/114.

Correct the spelling of POL103 to 'War and Peace'.

Extension

Prof Leo Richards is the new tutor for the module CSC203, AJAX Development. Locate his TutorID(Tutorstable) and update the tutorIDfor this module in the Modules table. Joseph Smith has received a mark of 79 in Nuclear Chemistry. Enter this into the 2 ~ Continue using the Access database StudentRecords1.accdb for these exercises

1Create a new query in Design view. Add the Studentstable. You may want to resize the table

so you can see all the fields listed. (Try using both the double-click and drag-and-drop methods and see which you prefer) Use the View button to switch to Datasheet view. Find student 10091 and correct her name to Suzy Watson. Note:Even though queries do not store data, the results of a query are from the underlying table, and a change made here is changing the data stored within the table.) Switch back to Design view. Apply a Sort to order surnames alphabetically and check the results in the datasheet view. Amend your query to sort first by surname, then by forename. (Hint: Access applies sort orders from left to right. You will need the surname field to appear first in the Query By Examplegrid) and apply a filter to only show only 1st years.(170)

Save the query as qryFirstYears

Create a new query based on the Students table that will show thenames and IDof all female 2nd year students. (93)

Save the query as qrySecondYears

Extension

Create a new query that will display the studentID, name and date of birth for all students born on 26/09/1995. (1) Modify the query to find students bornin 1995 (iebetween 01/01/1995 and 31/12/1995). (57) Hint: See Conditions, ranges and wildcards on p 10 Modify the query again to only show 1st years born in 1995. Also include their email address and sort bysurname. (24)

Save the query as qryDob

Create a query to show all 2nd and3rd year students. (330)

Save the query as qrySecondAndThirdYears

Essential Access: Exercises

2.1 - Continue using the Access database StudentRecords1.accdb for these exercises.

1We want to view information about the modules the students are taking. This will require

data from two related tables, Students and ModuleChoices.Both tables contain studentID fields which are linked with a 1-to-many relationship. view the datasheet. (502records) There are about 3 times as many results asbefore. This is because each student studies several modulesand is listed once for each module they take. student is taking.

McDonald).

It would be helpful to seethe module titles, and you may also need to view other module information.To do this we need to add a third table to our query. Add the Modules table to the design and include the moduleNameand credits fields.

View the datasheet again.

Clear the studentIDfrom the criteria and save the query as qryModules.

Extension

Createa new query that will list each moduleand the tutor who teaches it. Show the module Modify the design to display only History modules.(6) Hint: Usewildcards to find only the moduleswith IDswhichstart with HIS. You may find p11 of Booklet 1 useful. Modify the query to show all modules again. Add the room IDand capacity. You will need to add a 3rdtable to dothis. (40) Why do you think adding the rooms table to the query has reduced the number of records returned? Looking carefullyat the Modules table may help. Modify your query to show the college that the rooms are in.

Save the query with a suitable name.

3 ~Editing Table Design(Week 2)

3.1 - Open the Access database StudentRecords2.accdbfor these exercises. the data type of each, and the different field properties available. display in uppercase.

Hint: See p17 of Booklet 1.

There are never more than 100 credits awarded for a module. Adjust the number field size to the most appropriate type. Seta validation rule to prevent entries higher than 100and enter suitable validation text. Hint: See Number field properties on p17 and Other useful properties on p18 Format as Short Date and set the default value to be the current date.

Hint: See p18of Booklet 1.

Save your changes to the table design and switch to datasheet view. Pay attention to the warning messages. Enter an imaginary new module. Try entering: The changes you made should prevent this. You should see your default values in the

A new table

Field NameData TypeField properties

tutorIDAutoNumberPrimary Key titleShort Textfield size 10 firstNameShort Textfield size 50 lastNameShort Textfield size 50 dateOfBirthDate/Timeformat as short date

Essential Access: Exercises

Switch to datasheet view and enter a new tutor. Note tutorIDis automatically completed when you start entering data in any other fields. 3.2 - Use the Access database StudentRecords3.accdbfor these exercises. enforce referential integrity. table)and enforce referential integrity. Close the Relationship window, saving the changes. The Politics department have changed and are now the department of Politics, Economics and Philosophy. Open the Departments table in Datasheet view and attempt to change deptIDfrom POL to PEP. If you set up the relationship correctly you will receive an error. (Note: This is because there are related records in the Modules table that have the name was changed in the Departments table.) Attempt to delete the Politics department. This will also fail for similar reasons. Close the table. errors. Open the Modules table. Politics modules will now show this new PEP department code. warning that because of the cascade delete this will not only delete Politics from the to the modules table to confirm that all Politics modules have now gone. (Note: cascade delete should be used very carefully) 3.3 - Change backtothe Access database StudentRecords2.accdbfor these exercises. information. Import this file into a new table. A unique 3 character department code is included in this spreadsheet. Select this as the primary key rather than letting Access add its spaces which are normally best avoided. Rename the fields: a)Department Code -> deptID b)Department Name -> deptName c)Head of Department -> headOfDept Set the field size of deptIDto prevent new entries being longer than 3 characters.

Extension

Continue using the Access database StudentRecords2.accdbfor this extension. Access folder, whichcontains a table of room information and a table of college information.quotesdbs_dbs7.pdfusesText_5

[PDF] ms access query criteria pdf

[PDF] ms access syllabus pdf

[PDF] ms crm developer jobs in chennai

[PDF] ms crm developer jobs in hyderabad

[PDF] ms crm developer jobs in india

[PDF] ms dynamics crm developer jobs in chennai

[PDF] ms dynamics crm functional consultant job description

[PDF] ms excel 2007 formulas with examples pdf free download

[PDF] ms excel 2007 shortcut keys pdf

[PDF] ms excel 2013 marathi notes pdf

[PDF] ms excel advanced pdf in hindi

[PDF] ms excel advanced tutorial pdf

[PDF] ms excel exercise 2 space weight

[PDF] ms excel formatting exercise

[PDF] ms excel notes in hindi pdf free download