Moreshire Cinema Club – Guide B Contents

Overview ....................................................................................................................................................................1 Tables and Relationships .............................................................................................................................................1 Tables in Detail ...........................................................................................................................................................2 Registration Form .......................................................................................................................................................7 Overview ................................................................................................................................................................7 Building the Registration Form - in Detail ................................................................................................................7 Developing the Username .......................................................................................... Error! Bookmark not defined. Add New Member Using an Append Query .............................................................................................................7 Add a Command Button and a Macro to Register a New Member .............................. Error! Bookmark not defined. Finishing Touches ....................................................................................................... Error! Bookmark not defined. Extra .......................................................................................................................... Error! Bookmark not defined. Variation on Registration Form .................................................................................. Error! Bookmark not defined. Login Form .................................................................................................................................................................. 8 Overview ................................................................................................................................................................8 Detail – Build the Form............................................................................................................................................8 Detail – Build the Query ............................................................................................. Error! Bookmark not defined. Reflection................................................................................................................... Error! Bookmark not defined. Take member to another form ............................................................................... Error! Bookmark not defined. Deal with Invalid Data ............................................................................................ Error! Bookmark not defined. Alternative Login Form................................................................................................... Error! Bookmark not defined. Tracking Payments ......................................................................................................... Error! Bookmark not defined. The task ..................................................................................................................... Error! Bookmark not defined. Overview of Solution .................................................................................................. Error! Bookmark not defined. Payment Tracking Form in Detail ................................................................................ Error! Bookmark not defined. Printing Film Reviews ..................................................................................................... Error! Bookmark not defined. Overview ................................................................................................................... Error! Bookmark not defined. Create Some Reviews ................................................................................................. Error! Bookmark not defined. Create Form to Record Reviews for Films Seen........................................................... Error! Bookmark not defined. Create Options for Reviews ........................................................................................ Error! Bookmark not defined. Report Showing All Reviews by Film ........................................................................... Error! Bookmark not defined. Final Note ................................................................................................................................................................... 8 Pages marked Error are found only in the full guide!

Overview The key to success is practice. My small group of sixth formers, whom I would like to thank for their input, have built numerous versions with various refinements to reinforce skills and gain confidence.

Tables and Relationships Historically, candidates have been able to satisfy the exam requirements with four related tables. A possible structure is shown below.

The members table holds details of the students who have registered to join the Club. The performance table holds details of the performances they may attend as members. My assumption here is that the Club will organise a trip to the local cinema to see a specific film on a specific date and that if a member misses that film, s/he will not be able to see it again on a Club event; s/he would have to go alone or with friends but not as a club trip. Consequently, the film details would not be duplicated. The visit table is similar to a booking table. A member attending one specific performance (ie a named film on a certain date) constitutes a visit for which a specific VisitID number is allocated. You could think of this as a booking; I could have named the table as tblBooking and used BookingID instead of VisitID. One member can be included on many visits: consequently, there is a one to many relationship between MemberID in the member table and MemberID in the visit table. Remember that a visit is one student attending a specific performance: if ten members want to see the same performance,

1

Moreshire Cinema Club – Guide B Clifford French, April 2011

each will be allocated a unique and different VisitID. Consequently, there is a one to many relationship between PerformanceID in the performance table and PerformanceID in the visit table. Each time a student makes a payment, a PaymentID is generated to store details of the payment. We know that members are allowed to pay in instalments: therefore, there is a one to many relationship between PaymentID in the visit table and PaymentID in the payment table. It is worth remembering that even if members decide not to make any visits, the relationship is still one to many because they could make one or more visits. Panic guide: when you build the tables and create the relationships, make sure that at the “one end” the Indexed property of the primary key is set to Yes (No duplicates) and at the many end the corresponding field (which is then called the foreign key) has its Indexed property set to Yes (Duplicates OK). If you get a one to one relationship, look at the Indexed property of the foreign key – ie the field where you expect to see the infinity symbol ∞.

Tables in Detail You will have to decide in the exam which data fields to include in each table, based on the text file you are given. Here are the data fields used in this example database, with an explanation of the validation used. In tblMember (shown on the next page), UserName is the primary key because the scenario requires that a unique username is generated for each new member, based on a combination of letters from her/his name and a sequential number. This is a required field – a form of presence check – and is a text field rather than a number field in this database example because it consists of letters and digits. The section on building the Registration form will guide you through various versions of generating this username. It has been set to a length of 7 characters in this example; you will need to look at the data supplied to make a decision on its length in the exam.

2

Moreshire Cinema Club – Guide B Clifford French, April 2011

The next two fields – FirstName and LastName – are required fields in this example database and have a length of 20 characters; again, you will have to make your own decisions in the exam on the length of the fields. The next field is Form, which has been set to a length of 4 characters because this will accommodate the Year group number and form name used in many schools, eg 11Z.

3

Moreshire Cinema Club – Guide B Clifford French, April 2011

The data supplied in the exam may or may not include this field. The next field – Password – will definitely appear in the exam data file. In the example below, the length has been set to 13, to enable students to have “strong” passwords. However, the default has been set to “teapot” (without the quotation marks). My interpretation of the scenario is that when students register, they are not actually given the option to change their passwords: these are automatically set to “teapot”.

4

Moreshire Cinema Club – Guide B Clifford French, April 2011

The scenario requires that your prototype system must check that the student is old enough to join. Therefore, the data will include either age or date of birth. This sample database has an Age field; however, I will also show you how to use Date of birth. For the purposes of this sample database, I am assuming that the student has to be 13 or older; the exam paper will specify what “old enough to join” means and you will have to make the necessary changes. In the screenshot below you can see the validation rule: >=13 and the message that appears if the student is younger than 13. Note that the order is important: >= rather than =>. Age is a required field – a form of presence check. It has been set to have no decimal places: a student cannot enter “12.5” or similar.

5

Moreshire Cinema Club – Guide B Clifford French, April 2011

The next field – AgreeRules – appears in this sample database to illustrate how to use check boxes. It is usual to require people to agree to rules or terms and conditions when signing up to a club or to a website. The default value has been set to No, which is shown by an empty check box. The student has to tick the box to agree to the rules of the Cinema Club; at this stage, the value stored in the member table changes from 0 for No (“have not agreed”) to -1 (minus one, meaning “have agreed”).

See full guide for details of other tables.

6

Moreshire Cinema Club – Guide B Clifford French, April 2011

Registration Form Overview In this sample database you will build a form using unbound controls to collect data from a student who wishes to join. An unbound control is one that is not directly linked to a table. The data you collect will not be added directly to the member table; you will use a macro (1) to check that the student is old enough to join (2) to check that all the fields have been completed, ie that all the data has been entered, and then (3) to run an update query to add the student to the members table if s/he is old enough. Next, the macro will provide a message telling the student that s/he has registered successfully or is not old enough to join. Lastly, the macro will close the form ready for the next student.

Building the Registration Form - in Detail Choose Create  Forms  Blank Form and switch to Design View by right clicking on the blank form. If you see the Field List, close it because you will not be linking your form directly to the member table. You will collect the data using unbound controls and then you will use a macro to determine whether to save the data to the member table or not. Close the Field List by clicking on the cross.

Right click in the form design area and choose Form Header/Footer. Right click on the form’s tab (which currently shows Form1) and save the form as frmRegister. See full guide for details of how to complete the form.

Add New Member Using an Append Query The next step is to build an append query which will collect data from the form frmRegister and append it to the members table. You will use this query in a macro which will ensure that a new member’s details cannot be added if s/he is not old enough to join and that appropriate messages are displayed.

7

Moreshire Cinema Club – Guide B Clifford French, April 2011

Login Form Overview Charlotte wants members to be able to login. You will build a login form where a member will enter her/his username and password. If the member has not changed the password, a warning message will be displayed. The scenario does not require that you then offer a means of changing the password. Of course, the exam might include this as a requirement. You will then use a query to test whether the username and password combination match a member in the member table. A macro with a condition will be used to display an appropriate message and (optionally) to open a blank form if the username and password are valid – purely as a check that the login process works correctly. The issue of setting/ storing a default password and allowing a member to login without changing it raises important issues for the evaluation which forms the last question.

Detail – Build the Form The full guide takes you through the process step by step with screenshots and full explanation.

Payment Tracking and Reports Detailed with screenshots in the full guide which runs to over 60 pages.

Final Note This is only one solution and it may not match the data set you are given in the exam. However, if you work your way through this guide you should equip yourself with the understanding and skills you will need. Build the database, delete it and rebuild it. Try variations and troubleshoot your errors. If you practise building the database, you will be able to approach the exam with confidence.

8

Moreshire Cinema Club – Guide B Clifford French, April 2011