00 Creating the Driving School Database

A2 ICT

Driving School Database

/Creating the Driving School Database Database Part A - Design the tables.

KGV ICT Dept.

169850738.doc

1

A2 ICT

KGV ICT Dept.


169850738.doc

2

A2 ICT


Part B - Set up relationships. Lookups will have set up relationships automatically. Set up other relationships. Enforce referential integrity.

Part C - Enter test data Enter the test data from Appendix A into separate tables through the database window. Enter 10-20 records. The validation checks can be tested while this is being done. In the test data for the date of the lesson in the lesson table ensure that you enter today’s date for some of the lessons, as this is relevant to one of the queries to follow.

KGV ICT Dept.

169850738.doc

3

A2 ICT


Part D - Set up the queries necessary to bring data together.

Query Name

Based on Table(s)

Fields to Include

Criteria

Results

1.

qryLessons 31 July

tblLesson

Lesson No StudentID InstructorID Date Start Time Length of Lesson

Date 31/07/00

Lesson No’s = 17, 18, 20, 24

2.

qrySearch for Student

tblStudent

StudentID Surname Forename Tel No

Surname Watson

Student ID = 9

3.

qryInstructo r Addresses

tblInstructor

InstructorID Surname Forename Address1 Address2 Town Postcode

4.

qryDates

tblLesson

Lesson no StudentID InstructorID Date Collection point

5.

qryAnd

KGV ICT Dept.

tblLesson

StudentID InstructorID Date Start time Collection Point Lesson type 169850738.doc

Instructors 1, 2, 3

Date between 30/07/00 and 02/08/00

Lesson Nos 17, 18, 19, 20, 24, 27

>=#30/07/00# and <=#02/08/00# Instructor 2 Student on 30/07/00 Ids 1, 6

4

A2 ICT

Driving School Database Query Name

Based on Table(s)

Fields to Include

Criteria

Results

6.

qryOr

tblStudent

Mickleover or Littleover

Student ID 3, 5

7.

qryToday

tblLesson

Surname Forename Address1 Address2 Town Postcode Lesson No StudentID InstructorID Date Start Time

Date =Date()

Lesson No. StudentID Date Start time Length of Lesson

StudentID

Any records you entered with today’s date ID = 1

8.

9.

10.

qrySearch By StudentID

tblLesson

qrySearch on Lesson Date

tblLesson

qryInstructo r Lessons by Date

tblLesson

ie Today’s date

[Enter the ID Number]

Lesson Nos 15, 24, 32

StudentID InstructorID Lesson No. Date Start time Length of Lesson Collection point Lesson Type

Date

02/08/00 Lesson Nos 25, 29, 30, 31

Lesson No. StudentID InstructorID Date Start Time Length of Lesson Collection Point

Instructor ID

[Please enter the Date]

ID = 2 on 02/08/00 [Enter the Lesson Instructor ID] Nos 25, 30, 31 Date [Enter the Date]

11.

qryLesson and names query

KGV ICT Dept.

tblLesson

Lesson No StudentID InstructorID Date Collection point

169850738.doc

5

A2 ICT


Based on Table(s)

Fields to Include

Criteria

Results

Start time

12.

qryInstructo r search

tblStudent

Surname

tblLesson

Lesson No StudentID InstructorID Date Start time

InstructorID 1 [Enter Instructor ID] Sort by date

tblInstructor Surname

13.

qryFull Details

tblLesson

All fields

tblInstructor Surname * Forename* tblStudent

Surname* Forename*

qryFull Details By Date

Base on qryFull Details

15.

qryNext Weeks Lessons

Base on qryFull Details (Enter some lessons for next week in your in the lessons table)

16.

qryLesson Cost

tblLesson

14.

* Re-name these fields appropriately

Date [Please enter the date] Start time ascending Between Date() and Date()+7

All fields

tblLessonType Cost

KGV ICT Dept.

tblInstructor

Forename Surname

tblStudent

Forename Surname Address1 Address2

169850738.doc

6

A2 ICT


Based on Table(s)

Fields to Include

Criteria

Results

Add a new calculated field TotalCost:[Length of Lesson]*[Cost]

KGV ICT Dept.

169850738.doc

7

A2 ICT


Driving School – Action Queries Make Table, Append and Delete Queries

After a lesson has taken place, move the lesson to a table of old lessons – tblOldLesson (Append query) and remove them from the Lesson table (Delete query) After a period of 1 year remove them from the old lesson table all together. Set up the table tblOldLesson with a Make Table query as follows NB This only needs running once to set the empty table up!! 1. Create a query in design view – based on tblLesson with all fields and with the criteria for Lesson No as 0 – This avoids copying any records into tblOldLesson but creates the table structure. 2. Run the query and check that tblOldLesson has been created properly. 3. Make a copy of tblLesson because you are going to be changing dates and may want to use the existing data again. – in the database window, tables section, click on tblLesson 4. Click on the Copy button, then on the Paste button and set the table name to CopyoftblLessonOld and copy the Structure and the Data. 5. Open tblLesson and change the dates as follows – a. 01/08/00 to today’s date b. 02/08/00 to tomorrow’s date c. 31/07/00 to yesterday’s date d. 30/07/00 to a date exactly one year ago today 6. Close tblLesson and create a copy of it in CopyoftblLessonNew 7. Change the field type of Lesson No in tblOldLesson to Long Integer – the Append query will not work if the field type is Autonumber!! Set this field as the key field. 8. Create a query qryOld Lesson Append based on tblLesson , all fields. Set the criteria in the Date field to
KGV ICT Dept.

169850738.doc

8

A2 ICT


10. Run the query and check tblOldLesson to see that the records have been added correctly Now set up the delete query to remove the lessons from tblLesson as follows; 11. Create a query qryOldLessonDelete , based on tblLesson , all fields with the criteria for the date field set to
Argument Warnings On Query name Query name Query name Message

Argument Value No QryOld Lesson Append qryOldLessonDelete QryOver OneYear Function completed successfully

15. Set your data back to its original state (Remember step 7!!) and test the macro

Managing Lesson Prices – Update Query

Automatically increase the price of lessons by 10% using an Update query as follows; 1. Create an Update query qryPrice Update based on the cost field from tblLessonType . In the Update to row enter [Cost]*1.10 (ie 110% or 10%

KGV ICT Dept.

169850738.doc

9

A2 ICT


increase) and run the query. 2. Check the price changes 3. Try updating the prices by £1 – [Cost]+1

Totals Query

1. We can total and count in a query – Create a query qryLessonsData based on qryLesson Cost fields Lesson No and Cost. 2. In design view, click on the Totals button on the toolbar to insert a Total row. Change the Lesson No entry in the Total row to Count and that for the Cost field to Sum. 3. Test the query

Exercise

Use an Append query and two delete queries to archive students in a table tblOldStudents who have passed both theory and practical tests and do not require Pass Plus

KGV ICT Dept.

169850738.doc

10

A2 ICT


Part E - Set up forms and sub forms as necessary Set up the following forms frmStudent, using Autoform: Columnar, frmInstructor using the Form Wizard and frmLessonType using using Autoform: Tabular We want to improve the appearance of the form by creating better navigation buttons as shown below.

1. Open frmStudent in design view 2. Make sure that the Toolbox is showing and that the Control wizards tool is selected. 3. Select the command button and drag out a button on the form. 4. Select Record Operations in the wizard categories. 5. Select Add New Record in the Actions List and click on Next. 6. Choose Picture, Goto New2 (If you choose Text you can type in the text you want) and click next. 7. Call the button cmdAdd. 8. Add extra buttons from the Record Navigation category with Actions – a. Goto First Record b. Goto Previous record c. Goto Next record d. Goto Last record

9. Add the close Form button from the Form Operations category 10.Test that the buttons work correctly. 11. Re-arrange the buttons to the order shown above 12.Align them using Format, Align Top

KGV ICT Dept.

169850738.doc

11

A2 ICT


13.Tidy up by using Format, Horizontal spacing, Make equal 14.Make the buttons all the same size It is common practice to keep user buttons away from data entry areas. You are going to add a background to give a control panel effect. 15.Add a “Control Panel” around the buttons as follows:16.From the toolbox drag out a rectangle big enough to cover the buttons 17.Set its colour to Light blue, set its Special effect to sunken 18.Select the rectangle and use copy paste to make a copy of it. Make it slightly larger than the first and set its Special effect to Raised 19.Position the smaller rectangle over the larger and centre the buttons on the panel. If the buttons are hidden by the rectangles you may have to use Format, Bring to Front. 20.The system navigation buttons are now redundant so we can remove them and also change some other properties – In design view, double click on the form selector box (at the top left hand corner of the form). This displays the form properties window 21.Set the following properties a. Caption – Student details b. Scroll Bars – none c. Record selectors – No d. Navigation Buttons – No e. Dividing Lines – No f. Auto centre – Yes g. Max min Buttons – None

22.Repeat for the other 3 forms you have created.

KGV ICT Dept.

169850738.doc

12

A2 ICT


Creating the Lesson Booking form

Select forms click on new. Select the form wizard and choose the Lesson Cost query. Select all of the fields - Next Select Columnar - Next Select Standard - Next Call the form frmLessonbooking and click on finish. We now need to customise the form to give it the same look and feel as the Student and Instructor Forms Make the following changes: •

•

•

•

•

•

Move the fields down to make way for a heading Add a title Lessons in the same font, size and colour Edit the labels for Instructor and Student name control by removing the text and underscore Add the control panel (not the buttons) by using copy and paste from one of the other forms Add the buttons using the wizard as before Set the form properties to the same as the other forms

KGV ICT Dept.

169850738.doc

13

A2 ICT


Form with Tab Control Lesson and instructor Availability

1. Ensure that qryFull Details contains the calculated field End Time: DateAdd(“h”,[Length of Lesson], [Start Time]) – Format as Short Time 2. Load frmLesson Booking in Design view 3. Create a Tab control on the right hand side of the form – you may need to enlarge the form and move some existing controls in order to make space 4. The tab control will have 2 pages or tabs – set the caption property of the first tab to Daily Timetable and of the second one to Weekly Timetable 5. Click on the Daily Timetable tab and use the toolbox – with the control wizard switched on – to create a subform on the tab. Base the subform on qryFull Details – fields Instructor ID, Student_Forename, Student_Surname, Date, Length of Lesson, Start Time and End Time 6. You have to define your own linking fields – InstructorID in both form and subform and Date in both form and subform 7. Call the subform sfrmDaily Timetable 8. Save the form (as frmLesson Booking) 9. Test the form – it will need resizing repositioning and editing:a. To change the column headings, in design view, click on each of the following text boxes in turn and edit the Name property as indicated – Student_Forename to Forename, Student_Surname to Surname, Start Time to Start, End Time to End and Length of Lesson to Length b. Set the Text align property for Student_Forename, Student_Surname to Left and for Length of Lesson to Centre c. In form view – adjust the width of the columns by dragging the column dividers. Right click on the Instructor ID column and hide it. Change the form property Autoresize to No to keep your settings 10.Ensure that qryNext Weeks Lessons includes the calculated field End Time 11.Now set up the Weekly Timetable Tab – based on qryNext Weeks Lessons – with fields Instructor ID, Student_Forename, Student_Surname, Date, Length of Lesson, Start Time and End Time 12.Link on the Instructor ID field only (not the date) and call the subform sfrmWeekly

KGV ICT Dept.

169850738.doc

14

A2 ICT


13.Tidy up the form

KGV ICT Dept.

169850738.doc

15

A2 ICT


Search and Sort Options Add an Extra Tab and Add Filters

1. 2. 3. 4. 5.

Use the form frmLesson Booking Add another tab to the tab control (insert menu Tab Control Page) Set its name to Search Options Close and save the form Create a new query, qryStudent Lesson, based on the Lesson table – add all the fields from the table. Student ID is to be entered as a parameter by the user 6. Create a new macro, mcrSearch by Student, with Action Apply Filter – argument Filter Name as qryStudent Lesson 7. Open frmLesson Booking in design view and add a command button to the search optionds tab to run the macro mcrSearch by Student 8. Test the button – scroll through the records to view just this student’s lessons 9. Set up another button to add a Filter by Date option – based on qrySearch on Lesson Date. Call the macro mcrSearch by Date 10.The filter needs removing afterit has been used – Create a new macro mcrShow All Records – It has one action ShowAllRecords – Add a command button to run the macro 11.Test the buttons Add Sort Options

1. Create a new macro – mcrSort lesson – i. Action GoToControl Argument Lesson No ii. Action RunCommand Argument Sort ascending 2. Add a button to frmLesson Booking, Search options tab – Sort by Lesson 3. Add further buttons to Sort by instructor and sort by Student 4. Test the form

KGV ICT Dept.

169850738.doc

16

A2 ICT


Forgotten Student ID

A student rings to book a lesson – Operator clicks on Book a Lesson to open frmLesson Booking If the student has forgotten their ID the operator needs to open frmStudent and use the drop down to find the student’s ID The ID needs to be automatically transferred to frmLesson Booking when the operator clicks a button 1. Open frmLesson Booking in design view. Set up a button on the Search Options tab to Find a Student. 2. Test that the button opens frmstudent 3. Create the following macro mcrSelect student to paste the details into frmLesson Booking Action GoToControl RunCommand Close

Open Form GoToControl RunCommand

Argument Control Name Command Object Type Object Name Form View Control Name Command

Value Student ID Copy Form frmStudent frmLessonBooking Form Student ID Paste

4. Load frmStudent in design view and add a button labelled select student to run the macro mcrSelect Student 5. When you test this procedure, you will find that the system places the ID into the booking form but you have to press enter to activate the form. This can be avoided by creating amcro caleed mcrUpdate with the single action

KGV ICT Dept.

169850738.doc

17

A2 ICT


RunCommand with argument Refresh Page 6. This needs to be attached to the On Change propert of the student ID field on frmLesson Booking. Cancel a Lesson

1. Add a button Cancel booking to the Search options tab. Use the control wizard to set up the delete operation – use delete carefully!! Driving School Further Forms Add a combo Box to Enter Student Details

1. Open frmStudent in design view 2. Select the sex control and delete 3. Ensure that the control wizard is switched on and put a combo box in place of the deleted sex control 4. In the wizard – type in the values M and F and store the value in the Sex field 5. Change the label to sex and save the form. 6. Test the form

Add a combo box to look up student details

1. Continue to work on frmStudent in design view 2. Create room for a new control under the Students label at the top of the form 3. Put a combo box in the space created and use the Find a record on my form based on a value I selected in my combo box 4. Put the surname field into the selected fields and give the combo box the name Find record. NB. Look at properties of the combo box and see where the Source is set.

KGV ICT Dept.

169850738.doc

18

A2 ICT


5. Save the form and test the combo box.

Display the names in the combo box in alphabetical order

1. In design view, select the Find record combo box 2. Right click on the combo box and click on Properties – select the data tab 3. Click on the Row Source property and then on the … to the right 4. The SQL Statement Query window opens – this looks like the query window. Set the surname field to ascending order. 5. Close the window and save changes 6. Test the combo box and close the form

Create a Lesson Booking Form – Data from more than one table

1. Open qryLesson Cost (No 16 on queries sheet) – This query brings together data from tblLesson, tblLessonType, tblInstructor and tblStudent which are needed when a booking is made. 2. Close the query and create a new form based on qryLesson Cost – as a columnar form. 3. Save it as frmLesson booking Form 4. Switch to data view and find the option Size to Fit Form (this only shows if the form is not maximised), check and test the form, then close it. Subforms Lessons by Instructor – subform by Method 1

1. Use the Form Wizard to set up a new form based on tblInstructor – fields InstructorID, Surname, Forename and on tblLesson – fields StudentID,

KGV ICT Dept.

169850738.doc

19

A2 ICT


Date, Start time, Collection point – do NOT click next until you have selected all these fields – use Back if you missed some of them! 2. View as a form with subform and View by Instructor 3. Tabular layout – naming the form frmInstructor Main and the subform sfrmLesson 4. Check the form 5. In design view – click on the form selector of the main form (top left hand corner) and remove the scroll bars, Record selectors and Dividing lines 6. Remove the Navigation buttons from the subform. 7. Check the form

Lessons by Student – Subform by Method 2

1. Set up frmStudentNew using Form Wizard – based on tblStudent – fields StudentID, Title, Forename, Surname, Address1, Address2, Town and Postcode 2. Check the form 3. Go into design view and rearrange the fields into 2 columns of 4 at the top of the form 4. From the toolbox click on the Subform/Subreport icon and drag out a rectangle below the existing fields 5. Use Existing Tables 6. Select table - tblLesson – fields Lesson no, Date, Start Time, Collection point and Lesson type 7. Leave the option at Choose from list – Show Lesson for each record in tblStudent using StudentID – This is defining how the form and subform are to be linked 8. Call the subform sfrmLesson Details 9. Save the form as frmStudent Lesson details

KGV ICT Dept.

169850738.doc

20

A2 ICT


10.Look at the form in data view 11. Make changes to the designs of the form and subform to improve the appearance – Record selector, scroll bars, dividing lines

Lessons by instructor Subform by Method 3

1. Create a new form frmSTMain – based on tblStudent – fields StudentID, Surname and Forename – Columnar form. Save and close it. 2. Set up another form frmLT – based on tblLesson – fields Lesson No, Date and Start Time – a Tabular layout. Save and close it 3. Open frmSTMain in design view 4. Press F11 to view the database window and drag and drop the icon for the frmLT onto the lower area of the open form frmSTMain 5. View the properties of the subform (by right clicking on the border of the subform – not in the top left corner) and notice that the StudentID has been identified as the linking field – LinkChildFields and LinkMasterFields properties

More than one subform can be placed onto a main form if required!

KGV ICT Dept.

169850738.doc

21

A2 ICT


Part F - Set up reports Instructor Report 1. Produce a tabular report based on tblInstructor and including the fields – InstructorID, Surname, Forename, Address1, Address2, Town and Postcode. 2. Save as rptInstructor 3. Set the orientation to Landscape 4. In design view, change the title in the report header to Instructor Report 5. Add the field Mobile No to the right of the Postcode field 6. Increase the font size of the data to size 10 (currently size 8) 7. Delete/edit the labels in the Page Header to read Instructor ID, Name, Address and Phone 8. Rearrange the address fields so that they are arranged under the address heading as Address1 Address2 Town Postcode

9. Move controls as necessary to tidy up the layout.

Student Report

1. Produce a tabular, landscape report rptStudent based on tblStudent including all fields. 2. Ensure that the report fits into one page width

Membership Card Report

KGV ICT Dept.

169850738.doc

22

A2 ICT


1. Produce a columnar report rptMembership Card based on qrySearch by StudentID 2. Change the border style for all controls to Transparent 3. Left align the StudentID and Date of Birth controls 4. Change the title in the report header to Membership Card 5. Put the following message with border into the page footer (shift and enter will force a new line) Please keep this membership card safe and bring it with you to lessons Please inform us if any information is incorrect

Instructors’ Timetable Report

1. Use report wizard for the following report 2. Base the report on qryFull Details by Date – fields Student_Forename, Student_Surname, InstructorID, Start time and Length of Lesson. 3. View the date By Lesson 4. Add a grouping level – Instructor ID 5. Sort by Start Time 6. Align Left1 7. Corporate 8. Call the report rptInstructors’ Timetable 9. Test with the date 30/07/00 10.The report should be grouped on Instructor but is unsatisfactory because – a. The instructors names are not on the report b. The date is not on the report

KGV ICT Dept.

169850738.doc

23

A2 ICT


c. The column headings of Student_Forename and Student_Surname are unsatisfactory

11. In design view, alter the Student_Forename heading to read Name and delete the Student_Surname column heading 12.Drag Date from the Field list onto the report header and format it to long Date. Delete the label for the Date field. 13.Put the Instructor_Forename and Instructor_Surname onto the Instructor ID Header. Delete the labels for these fields. 14.Test the report with 30/07/00 as before 15.To put each instructor onto a new page – click on the Sorting and Grouping Icon, (or use View menu, Sorting and Grouping option) 16.Click on the Instructor ID entry and change Group footer to Yes 17.An Instructor ID footer has now appeared in design view – set the property Force New Page to After section 18.Check the report. Driving School Calculations in Reports

Including a Total in a report

1. Create a new report rptIncome based on qryLesson Cost with fields (in this order) – Instructor ID, Instructor_Forename, Instructor_Surname, Student_Forename, Student_Surname, Date and Total Cost. If the records are not grouped on instructor ID by default then add Instructor ID as a grouping level. Sort by Date and use Align Left1 layout.

2. Test the report 3. In design view, click on the sorting and grouping icon (or View menu, sorting and grouping option). Click on Instructor ID and change the Group Footer property to Yes

KGV ICT Dept.

169850738.doc

24

A2 ICT


4. Close the dialog box 5. Put a new text box into the Instructor ID Footer area and set the following properties a. Control Source

=Sum([TotalCost])

b. Format c. Font weight

Currency Bold

Shift | F2 will open a zoom box if you need more room.

6. Edit the text in the attached label box to Total 7. Drag Instructor_Forename and Instructor_Surname from the Detail section into the Instructor ID Header section 8. Delete the Instructor_Forename and Instructor_Surname column headings 9. Test the report

KGV ICT Dept.

169850738.doc

25

A2 ICT


Count the records in a Report

1. Add a new text box to the InstructorID Fooetr section to the left of the Total boxes 2. Set the Control Source property of the text box to =Count([TotalCost]) 3. Change the label to Number of Lessons 4. Test the report 5. Save the report

Running totals in a Report

1. Click on the Total Text box Control 2. Set the Running Sum property to Over All 3. Change the Total to running total

Reports with no records

1. Set up a macro mcrNo Data which displays a message box with the message No data in this report. 2. Open rptInstructors timetableand set the On No Data event in the properties of the report to mcrNo Data 3. Test the report 4. Attach the mcrNo Data macro to the Membership Card report in the same way

KGV ICT Dept.

169850738.doc

26

A2 ICT


Part G - Automate with buttons / macros Driving School Macros Macro to open frmstudent 1. In the database window click on Macros and new 2. In the Action Column click on OpenForm 3. Set the Action argument Form Name to frmStudent 4. Close the macro window and save as mcrStudent 5. Test the macro by running it 6. Macros can have more than one action – open mcrStudent in design view 7. Add a second action – GoToRecord with Argument New 8. Save the macro and test it – What effect does the new action have? 9. The macro can open the form for adding only – re-open mcrStudent in design view and delete the GoToRecord Action 10.Change the Data mode argument of the OpenForm action to Add 11. Save the macro as mcrStudentAdd and test it. 12.Set up macros mcrInstructor and mcrLesson to open frmInstructor and frmLesson.

Setting up a Message Box

1. Create a new macro mcrAbout with Action MsgBox, Argument “Created by your name © 2002” – NB © can be produced by typing ( c ) – with no spaces! 2. Set arguments

KGV ICT Dept.

169850738.doc

27

A2 ICT


a. Beep Yes b. Type Information c. Title

My Driving School

3. Test the macro

To set up a Menu as a Front end

1. Create a new form in design view. Save the form as frmMenu 2. Use the command button icon from the toolbox to set up buttons with appropriate messages to run the macros mcrStudent, mcrInstructor and mcrLesson Booking (Miscellaneous/run Macro option in the control wizard) 3. Set up a button to run mcrAbout 4. Set up a button to quit the application – (Application/Quit option in the command wizard) 5. Set up buttons to run the reports rptInstructor, rptStudent, rptMembership Card and rptInstructors timetable. 6. A macro called AutoExec is run when the database is opened – set this up as follows a. Action Echo HourGlass RunCommand OpenForm RunCommand

Argument No Yes WindowHide frmMenu DocMaximise

7. Test the autoexec macro NB F11 will display the database window

KGV ICT Dept.

169850738.doc

28

00 Creating the Driving School Database

Recommend Documents