Activity 2 (2 hours)

a) Create an efficient database structure that minimises data duplication. Ensure you use all and only the fields provided
Screen-print relationships in the database, making sure table names, field names and relationships can be clearly seen

b) Use the correct date types and key fields for your database.
Produce screen-prints in DESIGN view of each of your tables showing only the field names, data types and primary keys

c) An efficient database must include suitable validation.
(Must name validation used)

Screen-print in design view ONE example of range check on an appropriate field. Ensure you can clearly see the field it is applied to and the range specified

Screen-print in design view one example of presence check on an appropriate field. Ensure you can clearly see the field it is applied to.

Screen-print in design view give one example of a list check or table lookup on an appropriate field. Ensure you can clearly see the field

d) Import the data from the text files and enter the additional data provided at the beginning of this activity into the tables you have created
Screen-print each table showing at least 5 records, or all records if there are fewer than five and the full record count. (If the fields are too wide to fit on one page, truncate data is allowed)

Activity 3 (4 hours)

a) A form is required that will allow Vicky to add the details of students who wish to take part in productions. (i) Create a data entry form for Vicky to use.
For each student the form should collect their: * Name * Level of study * Date of birth * Gender * Address * Preferred production job

(ii) A StudentNumber consists of 1314 followed by a sequential four digit number one higher than the last number currently used. Generate the StudentNumber.
Screen-print the form in DESIGN view.
It does not need any annotations.
Ensure you show how the StudentNumber is generated.

(iii) Customise the form to make it easier to use.
Screen-print the form in FORM view.
On the screen-print, identify one of the features you have used to customise the form. Explain how this feature makes the form easier to use.

b) An automated method of saving a new student record is required.
Create an automated method of saving a student’s details.
The automated method of saving should: * Ensure the students name is present * Ensure their level of study is present * Ensure their gender is present * Ensure their preferred production job is present * Include a message to indicate the record has been saved
Screen-print in DESIGN view any macros, code and/or queries you have used.
Ensure the detail can be seen in full.

c) A single form is required that will Allow Vicky to assign production jobs or characters for a production.

(i) Create this form

Put a suitable title here
Select the production

Select the production job * Actor * Other * Actor * Other

ACTOR Select the character

Gender required

Number needed Number assigned Still needed Select the actor

Other Select the job role Select the student
Screen-print the form in FORM view.
It does not need any annotations.
Screen-print the form in FORM view.
It does not need any annotations.

(ii) The production combo box must contain details of the productions.
Screen-print the combo box source
It does not need any annotations

(iii) The ‘Actor’ section of the form needs to be developed.

* Create a query that will find characters still needing actors when a production is selected in the production combo box. * When a character is selected display:

* The gender required * The number of actors needed * The number of actors already assigned * Calculate and display the number of actors still needed for a character. * Create a query to find suitable actors when a character is selected. Assign this query to the actor combo box.

(iv) The ‘Other’ section of the form needs developing. * Create a query that find all non-actor production jobs. Assign this query to the production job combo box * Crete a query that will find suitable students when a production job is selected. Assign this query to the student combo box.

(v) The ‘Actor’ options must only be available for use if Vicky has selected ‘Actor’ from the production job choices. The ‘Other’ options must only be available if ‘Other’ has been selected.

d) Create an automated method of saving an assignment.
The automated method of saving should include: * Ensuring relevant data is selected * Storing the record in the relevant table * Carrying out any relevant updates
Screen-print in DESIGN view all macros, code and queries you have used.
Ensure that the detail can be seen in full.