PHS CyberSchool

Computer Studies SG Grade 12 - 2005

Modified 25 January 2006

Access test - 84 marks

Name: .....................................

From the CSSG directory copy the database AccessSpca (accessspace zipped) to your own login and rename it YourSurnameAccessSpca.
Only do what is required.
If you ask an unnecessary question expect to have 5 marks taken off.
Look at the marks for each question - all or nothing, no half marks
All answers must relate to the topic.
Use your common sense for instructions which are not totally clear and try not to be silly.
When you have finished the whole paper check that everything works. Computer applications have a mind of their own and sometimes there are clashes with coding which give you extra problems to resolve.
When you have completed the test copy your database to Project, Term 1, 05 12 Access Test.

You have been hired as an intern by the SPCA. They would like you to computerise their adoption files. Currently they keep all the information about animals that were placed for adoption on index cards. The cards hold the information about the family, for instance name, address, telephone number and number of children. Information on the animal also is kept, for example, name, type of animal, date of birth, gender, if neutered or spayed and the adopting family.

Tables – 7 marks

You have added all the information to Access. Make sure of the following with the tables.

Families - Make all surnames capital letters. [1]
Families - Format the telephone numbers with spaces to make them more readable. [1]
Families - Make the telephone numbers a required field. [1]
It is very tricky to always remember what the postal code is for different towns. Create something so that when you add new data to the families table you can quickly and accurately add the postal code. [4]
Pets - Do something to add the type of the animal quickly. [1]
Pets - Change the date field so the names of the months show as words and not as numbers. [1]
Pets - Make sure that no OLD animals are added to the database in the future, i.e. no animals born before 1 January 1998. [1]
Pets - Set the field size for the Name field to 30. [1]

Tables/queries - 40 marks

Create the necessary tables and queries to show the director of the SPCA. Save the tables/queries with the numbers indicated in brackets.

List with the name, address and telephone number of all families that have adopted pets. (01 Family details) [1]
List of all animals that have been adopted. The list should include the animal’s name and details. (02 Animal details) [1]
Display which family adopted the animal Romeo. (03 Romeo) [2]
List of all adoptions. The list should include the name of the family, telephone number, the animal’s name and type. (04 Adoptions - Family + Animal) [2]
List of all spade dogs. (05 Spade + dogs) [2]
List of all families living in Pinelands arranged in order by the number of children they have. (06 Pinelands + kids order) [2]
List of all families with 2 or less children. (07 2 or less kids) [2]
List of all adopting families but NOT family no 1. 08 Families + Names – not 1) [2]
List all the animals born after 1 January 2000 adopted by families in Edgemead. List the people’s and the animals' names. (09 Born after 1 January 2000 Edgemead) [2]
List of different types of animals which have been adopted, plus the number of each kind. (10 Type + numbers) [3]
List of all animals that have been adopted with their ages. (11 Animals + Ages) [3]
List of all the people in Pinelands who have adopted a dog. Just list the people’s names. (12 Pinelands – Adopted dog) [3]
List all the animals born between unspecified dates adopted by families in Pinelands. (13 Born when - Pinelands) [3]
List of all the dogs and cats which have been adopted indicating if they have been spade or neutered. (2) Those for whom the designation is FALSE should have the works FALSE in red when viewed on the computer screen.(2) (14 Urgent – Re Spaying/Neutering) [4]
Create a new (crosstab) query to find the numbers and genders of all animals. (15 Animals + Genders) (4)
Create a new (duplicates) query where you list all the animals that are in a family which have adopted more than one animal. (16 Many animals) (4)

Forms – 10 marks

Create a form of the families table with all the fields .
1. Give each field description a colour fill. [1]
2. Make each field description just wide enough. [1]
3. Group and arrange the data near the top of the form. [1]
4. Add a header with the name ‘Adopting families’. [1]
5. Change the tab order so that is obviously silly. [1]
Create a form of the pets table in datasheet layout. [1]
Add the pets table as a subform to the families table using Number as the link between the forms. [2]
1. In the form footer insert a date field to show the current date. [2]

Report – 11 marks

Save the reports with the numbers indicated in brackets.

Create a report of the families table grouping it by people who live in various suburbs. (Rep01 Family grouped suburb) [2]
Create a report of all the pets with a total (of all the entries) in the report footer. (Rep02 Totals pets) [2]
Create a report of the families. In the footer indicate the average number of children per family. (Rep03 Average no children) [2]
Create a report of the pets table showing the totals of each type of pet. (Rep04 Pets type) [2]
Create a report which will show the people who live in each suburb and the names of their adopted pets, grouped by suburb. (Rep05 Suburbs familes and pets) [3]

Table or query or report and ... – 12 marks

Save the reports with the numbers indicated in brackets.

Find out how many children and pets are in each house, i.e. the total number per house who needs care and attention. Display the data per family. (0001 No needs care) [4]
Each family was expected to donate R80 when they adopted an animal. Display the amount that should have been collected from all the adopted anmals. (0002 Money) [4]
Display in graphic and text format the total number of male and female animals. (0003 Genders graph + txt) [4]