PHS CyberSchool

Computer Studies SG 

Auctioning Items database test (37 marks)

Items table - 3 marks

1. In the properties of the Sale Price field apply the Rand currency format. [1] (Easy)
2. Create a mask for the Date Sold field that only allows the input of a date in short date format. (In design view go to Date Sold field and click on Input mask, Short date) [1] 
3. Use the properties of the Item Name field to cause all text to be displayed in upper case. (This means BIG letters)[1] 

Sales table - 2 marks

1. Create a validation rule that will not allow the input of a date into the Date of Joining field that is earlier than 2005. Create the following validation text message No recruitment until 2005. Apply this rule to new data only. (Look at maintaining Taxi and School Supporters. It was hoped validation would have been used in the Orchestra database.) [2] 

Relationships - 6 marks

1. Create a relationship between the Customers and the Items tables. Apply referential integrity. [2] 
2. Modify the data type of the Salesperson ID field in the Items table so that a one to many relationship with referential integrity between the Items and Sales tables can be applied. Apply a one to many relationship between the tables. [2] 
3. Modify the data type of the ID field in the Private Data table so that a one to one relationship with referential integrity between the Private Data and Sales tables can be applied. Apply a one to one relationship between the tables. [2] 

(We spoke about relationships in Taxi, School Supporters, Orchestra and Hospital.  Number c is tricky.)

Queries - 14 marks

1. Create a new query called R500 or more based on the Items table that shows only the Item Name and Sale Price fields and the items that sold for R500 or more. Save the resulting query output as a separate table called High Price. [2] (Make table done in Election.)
2. On the Items table create a query that will calculate the average number of days it took to sell the items. Save the query as Length of time. [2]  (Look at answer - have done all of these.)
3. Open the Unsold query and modify the join to show only unsold items. [2]  (You will have to think and explore questions on a paper.  You have the Help menu..)
4. Open and modify the Sum Up query to calculate the number of items each customer has purchased. [2] (Done in Election, Taxi, School Supporters and Orchestra)
5. Create a new query called Replace based on the Sales table that shows the First Name and Last Name fields. Use an update query to replace the last name Jones with the last name Smith. Update the Sales table by running the query. [2]  (Found in Taxi and School maintaining.)
6. Open the Profit query and add an expression named Profit that will calculate the profit made for each item. Format the field as Rand currency. [2]  (Easy - Do a subtraction)
7. Open the Range query. Add criteria under the Sale Price field so that when the query is run the user is prompted to enter values for both the lower and upper limits. The run query will then show all values greater than the lower limit entered and less than the upper limit entered. Check that eleven records are returned when the query is run looking for all values greater than 24 and less than 70. [2]  (Found in Taxi and School Supporters queries)

Forms - 2 marks

1. Open the Sales form and insert a new control that will multiply each salesperson's commission % by 1.02  Use the caption IncreasedCom for the new control. [2]  (Simple - just do as asked)

Report - 6 marks

1. Open the Customers report. Delete the First Name and Last Name controls. Insert a new control that will concatenate the First Name and Last Name fields with a space between them. Use the caption Full Name for the new control. [2]  (You were shown concatenate - it has been on the board all week.)
2. Open the Item Totals report. On the last page of the report add a control that will calculate the smallest sale price for any item. Format it as currency. Use the caption Minimum Price for the control. [2] (Found in Taxi and School Supporters presenting data and in Orchestra)
3. Open the Sales report. Force page breaks using header properties to make each salesperson's data appear on a separate page. [2]  (Put a page break, found on the tool bar, in the detail section below the other controls. Not taught.)

Importing - 2 marks

1. Import the text file Potential Customers in tab delimited format as a new table called Potential Customers. The first row of the text file contains the field names for the table. Make the ID field the primary key. [2]  (Found in Election but using a spreadsheet.)

Macro - 2 marks

1. Open the Commission form and apply the showcom macro to the command button named Show Commission. This macro will display the Commission caption and control when the Show Commission button is clicked. (This will only work if the Relationships are correct.) [2]  (I did not mark this properly in all examples.)

This test is based on the sample for the Advanced ICDL Access test.