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] 
2. Create a mask for the Date Sold field that only allows the input of a date in short date format. [1] 
3. Use the properties of the Item Name field to cause all text to be displayed in upper case. [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.  [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] 

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] 
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] 
3. Open the Unsold query and modify the join to show only unsold items. [2] 
4. Open and modify the Sum Up query to calculate the number of items each customer has purchased. [2] 
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] 
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] 
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] 

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] 

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] 
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] 
3. Open the Sales report. Force page breaks using header properties to make each salesperson's data appear on a separate page. [2] 

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] 

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] 

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