Computing Module 3 Writeup Essay

essay B
  • Words: 2397
  • Category: Database

  • Pages: 9

Get Full Essay

Get access to this section to get all the help you need with your essay and educational goals.

Get Access

Examiner (ExaminerNumber, Forename, Surname, Address, Postcode, SubjectReferenceCode)Centre (CentreNumber, NumberOfCandidatesEntered, ExaminerNumber, SubjectReferenceCode)Subject (SubjectReferenceCode, SubjectName, Payment)Examiner Table:Field NameData TypeExaminerNumberNumberForenameTextSurnameTextAddressTextPostcodeTextTaxDeductionFlagYes/NoSubjectReferenceCodeNumberCentre Table:Field NameData TypeCentreNumberNumberNumberOfCandidatesEnteredNumberExaminerNumberNumberSubjectReferenceCodeNumberSubject Table:Field NameData TypeSubjectReferenceCodeNumberSubjectNameTextPaymentCurrencyData Entry Form For A Centre:Data Entry Form For An Examiner:Implementation Of Tables In A Database:Centre Table:As you can see, the fields CentreNumber and SubjectReferenceCode have been made joint primary keys. This is so that a centre can enter candidates for more than one subject. For example, if CentreNumber was the primary key on its own, then we could only enter candidates from the centre for one subject because if the CentreNumber was repeated, it would not be unique and therefore fall foul of the uniqueness check.

Therefore, with both fields, CentreNumber and SubjectReferenceCode being made joint primary keys, we can enter candidates for more than one subject from a particular centre, because the CentreNumber and SubjectReferenceCode combination will be unique, so it will pass the uniqueness validation rule.Validation Rules:FieldValidation CheckValidation RuleCentreNumber(Primary Key)Range CheckBetween 10000 and 79999Input Mask00000 ( must have 5 digits)Presence CheckRequired = YesUniqueness CheckIt is the Primary KeySubjectReferenceCodePresence CheckRequired = YesInput MaskOptions from drop-down box (20094, 28181, 64773) by using the following Row Source code in Lookup: Select Subject.SubjectReferenceCode From Subject;ExaminerNumberPresence CheckRequired = YesInput Mask000000 (must have 6 digits)Range CheckBetween 100000 and 999999NuberOfCandidatesEnteredPresence CheckRequired = YesExaminer Table:Validation Rules:FieldValidation CheckValidation RuleExaminerNumber (Primary Key)Range CheckBetween 100000 and 999999Uniqueness CheckIt is the Primary KeyInput Mask000000 ( must have 6 digits)Presence CheckRequired = YesForenamePresence CheckRequired = YesSurnamePresence CheckRequired = YesAddressPresence CheckRequired = YesPostcodeInput Mask>LL00 0LL;O;_ (Must be in this form)Presence CheckRequired = YesSubjectReferenceCodePresence CheckRequired = YesInput MaskOptions from drop-down box (20094, 28181, 64773) by using the following Row Source code in Lookup: Select Subject.SubjectReferenceCode From Subject;Subject Table:Validation Rules:FieldValidation CheckValidation RuleSubjectReferenceCode (Primary Key)Uniqueness CheckIt is the Primary KeyPresence CheckRequired = YesSubjectNamePresence CheckRequired = YesPayment (to 2.d.p)Presence CheckRequired = YesValidation Of Data Entered In Visual Basic:For entering data to the Add Centre Form, I used the following validation:Private Sub CmdAddCen_Click()If (Validate() = True) ThendatCentre.

Recordset.UpdatedatCentre.Recordset.AddNewEnd IfEnd SubPrivate Sub CmdCloseCen2_Click()Unload MeEnd SubPrivate Sub Form_Load()datCentre.Recordset.AddNewEnd SubPublic Function Validate() As BooleanValidate = TrueIf (txtCentreNo.

Text = “”) ThenMsgBox (“Centre number has to be entered”)Validate = FalseExit FunctionEnd IfIf txtCentreNo.Text < 10000 ThenMsgBox (“Centre number must be between 10000 and 79999”)Validate = FalseExit FunctionEnd IfIf txtCentreNo.Text > 79999 ThenMsgBox (“Centre number must be between 10000 and 79999”)Validate = FalseExit FunctionEnd IfIf (txtExaminerNo.Text = “”) ThenMsgBox (“Examiner number has to be entered”)Validate = FalseExit FunctionEnd IfIf txtExaminerNo.Text < 100000 ThenMsgBox (“Centre number must be between 100000 and 999999”)Validate = FalseExit FunctionEnd IfIf txtExaminerNo.

Text > 999999 ThenMsgBox (“Centre number must be between 100000 and 999999”)Validate = FalseExit FunctionEnd IfIf (txtCandidatesEntered.Text = “”) ThenMsgBox (“Number of candidates entered has to be entered”)Validate = FalseExit FunctionEnd IfEnd FunctionIn the validation rules for the Add Centre form listed above, txtCentreNo.Text = “” means that the field has been left blank, as demonstrated above. This produces a message box, which displays the error.The validate value starts off True. If any of the validation criterion are breached, then this validate value is made False and this value is taken up into CmdAddCen Public Sub and this stops the database from being updated, as it will only be updated if Validate=True.

The validation checking loop starts off at the top. So, if no value had been entered for Centre Number, a message box would pop up displaying the error. Once this has been rectified and the validation rule passes, validate gets reset to True and the loop continues. If Examiner Number has been left blank or is out of the specified range, then another message box would pop up displaying the error.For entering data to the Add Examiner Form, I used the following validation:Private Sub Form_Load()datExaminer.Recordset.

AddNewEnd SubPublic Function Validate() As BooleanValidate = TrueIf (txtExaminerNo.Text = “”) ThenMsgBox (“Examiner number has to be entered”)Validate = FalseExit FunctionEnd IfIf txtExaminerNo.Text < 100000 ThenMsgBox (“Centre number must be between 100000 and 999999”)Validate = FalseExit FunctionEnd IfIf txtExaminerNo.Text > 999999 ThenMsgBox (“Centre number must be between 100000 and 999999”)Validate = FalseExit FunctionEnd IfIf (txtSurname.Text = “”) ThenMsgBox (“Surname has to be entered”)Validate = FalseExit FunctionEnd IfIf (txtForename.

Text = “”) ThenMsgBox (“Forename has to be entered”)Validate = FalseExit FunctionEnd IfIf (txtAddress.Text = “”) ThenMsgBox (“Address has to be entered”)Validate = FalseExit FunctionEnd IfIf (txtPostCode.Text = “”) ThenMsgBox (“Postcode has to be entered”)Validate = FalseExit FunctionEnd IfEnd FunctionFor Subject Reference Code, I made sure that the Codes entered are 5 digits and unique by using a combo box to list the available options for the user to select. The user cannot alter the values in the combo box unless changes are made to the Subject table.

By doing this, I am reducing the chances of errors being made during data entry by the user.Data Being Viewed In Visual Basic:This screenshot shows the centres which have entered candidates for UKAB examinations, which subject they have entered them for, the examiner who will be marking the paper for that particular subject and the number of candidates entered for that subject from that particular centre.The data has been ordered by Centre No. ascending and then by Subject Code ascending.

This makes it much easier to read as related data are grouped together and if editing of a record needs to take place, that record can be easily found.To edit records, the user would select a record and press EDIT. This would open up the Add Centre form, but the selected details would already be in the relevant fields, making editing easier. Once the user has edited the record, they only have to press ADD and the record is amended in the database.To delete records, the user would select a record and press DELETE.

This would cause a message box to appear asking for conformation of the delete. Once confirmed, the record is deleted from the database.This screenshot shows the examiner who mark papers for UKAB. Their Examiner No.

Forename, Surname, Address and Postcode, Tax Deduction Flag and Subject Code.The data has been ordered by Examiner No. ascending. This makes it much easier to read as related data are grouped together and if editing of a record needs to take place, that record can be easily found.Under the Tax Deduction column, the value -1 means that the examiner is on a low income and will not have any tax deducted. The value 0 means that they will have 22% tax deducted from their gross pay.

To edit records, the user would select a record and press EDIT. This would open up the Add Examiner form, but the selected details would already be in the relevant fields, making editing easier. Once the user has edited the record, they only have to press ADD and the record is amended in the database.To delete records, the user would select a record and press DELETE.

This would cause a message box to appear asking for conformation of the delete. Once confirmed, the record is deleted from the database.Report 1:For my system, I will be printing out the relevant details for each examiner separately. This is why I have included a drop down box, so that the user can select the specific examiner they want the details for.Once the print button is pressed, the data currently being displayed is sent to the printer to be printed.

The hardcopy for this report for Examiner Number: 357862 Natasha Foster is shown below.357862 Natasha FosterCentre No. No. Of Scripts46310 3965456 6575548 5379995 47Private Sub cmbExaminer_Click()Dim ExaminerNo As StringDisplayExaminerNameIf (cmbExaminer.

ListIndex = 0) ThendatCentre.RecordSource = “Select CentreNumber, NumberOfCandidatesEntered From Centre Order By CentreNumber ASC”ElseExaminerNo = cmbExaminer.TextdatCentre.RecordSource = “Select CentreNumber, NumberOfCandidatesEntered From Centre Where ExaminerNumber = ” & ExaminerNo & ” Order By CentreNumber ASC”End IfdatCentre.

CommandType = adCmdTextdatCentre.RefreshEnd SubPublic Sub LoadExaminers()cmbExaminer.ClearcmbExaminer.AddItem (“All Examiners”)datExaminer.

Recordset.MoveFirstWhile datExaminer.Recordset.EOF = FalsecmbExaminer.AddItem (datExaminer.Recordset(“ExaminerNumber”))datExaminer.

Recordset.MoveNextWendcmbExaminer.ListIndex = 0End SubPrivate Sub Form_Load()datExaminer.RefreshLoadExaminersEnd SubPublic Sub DisplayExaminerName()Dim ExaminerNo As StringIf (cmbExaminer.ListIndex = 0) ThenlblExaminerName.Caption = cmbExaminer.

TextElseExaminerNo = cmbExaminer.TextdatExaminer.Recordset.MoveFirstdatExaminer.Recordset.Find (“ExaminerNumber = ” & ExaminerNo)lblExaminerName.

Caption = datExaminer.Recordset(“Forename”) & ” ” & datExaminer.Recordset(“Surname”)End IfPrivate Sub CmdPrint_Click()’Printer.Print .

Fields.Item(“Centre Number”).Value ; vbTab ; .Fields.Item(“Subject Reference Code”).

Value ; vbTab ; .Fields.Item(“Examiner Number”).Value ; vbTab ; Str$(.

Fields.Item(“Number Of Candidates Entered”).Value) ; vbNewLinersCentre.MoveNextEnd WithWendPrinter.EndDocEnd SubEnd FunctionReport 2:Examiner: 136872Examiner Name: Lee WrightAdmin Payment: �95Script Rate: �3.

75Scripts Marked: 204Gross Pay: �860.00Tax Deducted: �189.20Net Pay: �670.80Private Sub cmbExaminer_Click()CalcEnd SubPublic Sub LoadExaminers()cmbExaminer.

CleardatExaminer.Recordset.MoveFirstWhile datExaminer.Recordset.EOF = FalsecmbExaminer.AddItem (datExaminer.

Recordset(“ExaminerNumber”))datExaminer.Recordset.MoveNextWendEnd SubPrivate Sub cmdClose_Click()Unload MeEnd SubPrivate Sub Form_Load()datExaminer.RefreshLoadExaminersEnd SubPublic Sub DisplayExaminerName()Dim ExaminerNo As StringExaminerNo = cmbExaminer.TextdatExaminer.Recordset.

MoveFirstdatExaminer.Recordset.Find (“ExaminerNumber = ” ; ExaminerNo)lblExaminerName.Caption = datExaminer.

Recordset(“Forename”) ; ” ” ; datExaminer.Recordset(“Surname”)lblAdminPayment.Caption = “95.00”End SubPrivate Sub cmdPrint_Click()Printer.Print .Fields.

Item(“Examiner Number”).Value ; vbTab ; .Fields.Item(“Examiner Name”).Value ; vbTab ; .

Fields.Item(“Admin Payment”).Value ; vbTab ; Str$(.Fields.Item(“Script Rate”).Value) ; vbTab ; .

Fields.Item(“Gross Pay”).Value ; vbTab ; .Fields.Item(“Tax Deducted”).Value ; vbTab ; Str$(.

Fields.Item(“Net Pay”).Value) ; vbNewLineEnd SubrsCentre.MoveNextEnd WithWendPrinter.EndDocEnd SubPublic Function ScriptRates()datExaminer.RecordSource = “Select SubjectReferenceCode from Examiner where ExaminerNumber = ” ; ExaminerNoEnd FunctionPublic Sub Calc()Dim ExaminerNo As StringDim TotalScriptsMarked As LongDim ScriptRate As DoubleDim GrossPay As DoubleDim TaxDeducted As DoubleDim NetPay As DoubleDisplayExaminerNameExaminerNo = cmbExaminer.

TextdatCentre.RecordSource = “Select SubjectReferenceCode, NumberOfCandidatesEntered From Centre Where ExaminerNumber = ” ; ExaminerNodatCentre.RefreshTotalScriptsMarked = 0′ calculate the total scripts markeddatCentre.Recordset.MoveFirstScriptRate = GetScriptRate(datCentre.

Recordset(0))While (datCentre.Recordset.EOF = False)TotalScriptsMarked = TotalScriptsMarked + datCentre.Recordset(1)datCentre.Recordset.MoveNextWendGrossPay = (ScriptRate * TotalScriptsMarked) + lblAdminPayment.

CaptionIf (GetTaxDeductedFlag() = False) ThenTaxDeducted = (GrossPay / 100) * 22ElseTaxDeducted = 0End IfNetPay = GrossPay – TaxDeducted’ display the totallblGrossPay.Caption = Format(GrossPay, “�#,##0.00”)lblScriptRate.Caption = Format(ScriptRate, “�#,##0.

00″)lblScriptsMarked.Caption = TotalScriptsMarkedlblTaxDeducted.Caption = Format(TaxDeducted, “�#,##0.00”)lblNetPay.

Caption = Format(NetPay, “�#,##0.00”)End SubPublic Function GetScriptRate(SubjectCode As Long) As DoubledatSubject.RecordSource = “SELECT Payment FROM Subject WHERE SubjectReferenceCode = ” ; SubjectCodedatSubject.RefreshGetScriptRate = datSubject.Recordset(0)End FunctionPublic Function GetTaxDeductedFlag() As BooleanDim ExaminerNo As StringExaminerNo = cmbExaminer.TextdatExaminer.

Recordset.MoveFirstdatExaminer.Recordset.Find (“ExaminerNumber = ” ; ExaminerNo)GetTaxDeductedFlag = datExaminer.Recordset(“TaxDeductionFlag”)End FunctionReport 3:For my system, I will be printing out the relevant details for each subject separately. This is why I have included a drop down box, so that the user can select the specific subject they want the details for.

Below is the hardcopy of the centres entering candidates for SubjectReferenceCode: 28181 (Computer Science) and the number of candidates from each centre and the total number of candidates for that subject.28181 Computer ScienceCentre No. No. Candidates Entered Total Number Of Candidates Entered:10001 45 81812375 5825487 4825589 5434695 4934962 5237951 6646310 3946795 3546981 5058746 4265456 6567135 4875548 5378998 6779995 47Private Sub cmbSubject_Click()Dim SubjectCode As StringDisplaySubjectNameIf (cmbSubject.ListIndex = 0) ThendatCentre.

RecordSource = “Select CentreNumber, NumberOfCandidatesEntered From Centre Order By CentreNumber ASC”datCentreTotal.RecordSource = “Select SUM(NumberOfCandidatesEntered) From Centre”ElseSubjectCode = cmbSubject.TextdatCentre.RecordSource = “Select CentreNumber, NumberOfCandidatesEntered From Centre Where SubjectReferenceCode = ” ; SubjectCode ; ” Order By CentreNumber ASC”datCentreTotal.RecordSource = “Select SUM(NumberOfCandidatesEntered) From Centre Where SubjectReferenceCode = ” ; SubjectCodeEnd IfdatCentre.CommandType = adCmdTextdatCentre.

RefreshdatCentreTotal.CommandType = adCmdTextdatCentreTotal.RefreshtxtTotal.Text = datCentreTotal.Recordset(0)End SubPrivate Sub cmdClose_Click()Unload MeEnd SubPublic Sub LoadSubjects()cmbSubject.ClearcmbSubject.

AddItem (“All Subjects”)datSubject.Recordset.MoveFirstWhile datSubject.Recordset.EOF = FalsecmbSubject.

AddItem (datSubject.Recordset(“SubjectReferenceCode”))datSubject.Recordset.MoveNextWendcmbSubject.ListIndex = 0End SubPrivate Sub Form_Load()datSubject.

RefreshLoadSubjectsEnd SubPublic Sub DisplaySubjectName()Dim SubjectCode As StringIf (cmbSubject.ListIndex = 0) ThenlblSubjectName.Caption = cmbSubject.TextElseSubjectCode = cmbSubject.TextdatSubject.Recordset.

MoveFirstdatSubject.Recordset.Find (“SubjectReferenceCode = ” ; SubjectCode)lblSubjectName.Caption = datSubject.Recordset(“SubjectName”)End IfEnd SubReport 4:For my system, I will be printing out the relevant details for each subject separately. This is why I have included a drop down box, so that the user can select the specific subject they want the details for.

Below is the hardcopy of the centres entering candidates for SubjectReferenceCode: 64773 (Economic) and examiners marking papers from this subject.64773 EconomicsExaminer No. Forename Surname Centre No.137982 George Cloomey 10001137982 George Cloomey 34962137982 George Cloomey 12375137982 George Cloomey 46981357862 Natasha Foster 75548357862 Natasha Foster 46310357862 Natasha Foster 65456357862 Natasha Foster 79995842695 Liam Pitte 46795842695 Liam Pitte 37951842695 Liam Pitte 67135842695 Liam Pitte 25589422689 Christiano Ronaldo 34695422689 Christiano Ronaldo 25487422689 Christiano Ronaldo 58746422689 Christiano Ronaldo 78998Private Sub cmbSubject_Click()Dim SubjectCode As StringDisplaySubjectNameIf (cmbSubject.

ListIndex = 0) ThendatCentre.RecordSource = “Select Centre.ExaminerNumber, Centre.CentreNumber, Examiner.Forename, Examiner.

Surname From Centre, Examiner Where Centre.ExaminerNumber = Examiner.ExaminerNumber Order By Examiner.Surname ASC”ElseSubjectCode = cmbSubject.

TextdatCentre.RecordSource = “Select Centre.ExaminerNumber, Centre.CentreNumber, Examiner.Forename, Examiner.

Surname From Centre, Examiner Where Centre.ExaminerNumber = Examiner.ExaminerNumber AND Centre.SubjectReferenceCode = ” ; SubjectCode ; ” Order By Examiner.Surname ASC”End IfdatCentre.

CommandType = adCmdTextdatCentre.RefreshEnd SubPrivate Sub cmdClose_Click()Unload MeEnd SubPublic Sub LoadSubjects()cmbSubject.ClearcmbSubject.AddItem (“All Subjects”)datSubject.Recordset.MoveFirstWhile datSubject.Recordset.EOF = FalsecmbSubject.AddItem (datSubject.Recordset(“SubjectReferenceCode”))datSubject.Recordset.MoveNextWendcmbSubject.ListIndex = 0End SubPrivate Sub Form_Load()datSubject.RefreshLoadSubjectsPrivate Sub cmdPrint_Click()Printer.Print .Fields.Item(“Examiner Number”).Value ; vbTab ; .Fields.Item(“Examiner Forename”).Value ; vbTab ; .Fields.Item(“Examiner Surname”).Value ; vbTab ; Str$(.Fields.Item(“Centre No.”).Value) ; vbNewLineEnd SubPublic Sub DisplaySubjectName()Dim SubjectCode As StringIf (cmbSubject.ListIndex = 0) ThenlblSubjectName.Caption = cmbSubject.TextElseSubjectCode = cmbSubject.TextdatSubject.Recordset.MoveFirstdatSubject.Recordset.Find (“SubjectReferenceCode = ” ; SubjectCode)lblSubjectName.Caption = datSubject.Recordset(“SubjectName”)End IfEnd SubReport 5:This shows the number of candidates entered for each subject and the total number of candidates who are sitting UKAB exams. It also shows how much was paid to examiner for each subject and how much was paid to them in total.Subject Code Candidates Entered Payment Made20094 819 �3,851.1628181 825 �2,709.5364773 818 �6,076.73Total: 2462 �12,637.42Private Sub cmdClose_Click()Unload MeEnd SubPrivate Sub Form_Load()datExaminer.RefreshdatCentre.RefreshdatSubject.RefreshDim SubjectCode As LongDim ExaminerNo As LongDim TaxDeductedFlag As BooleanDim CanEnt As LongDim PayMade As DoubleDim EngCandidates As LongDim EngPaymentMade As DoubleDim CompCandidates As LongDim CompPaymentMade As DoubleDim EcoCandidates As LongDim EcoPaymentMade As DoubleDim TotalCandidates As LongDim TotalPaymentMade As DoubledatExaminer.Recordset.MoveFirstWhile (datExaminer.Recordset.EOF = False)SubjectCode = datExaminer.Recordset(“SubjectReferenceCode”)ExaminerNo = datExaminer.Recordset(“ExaminerNumber”)TaxDeductedFlag = datExaminer.Recordset(“TaxDeductionFlag”)Call CalcTotals(SubjectCode, ExaminerNo, TaxDeductedFlag, CanEnt, PayMade)If (SubjectCode = 20094) ThenEngCandidates = EngCandidates + CanEntEngPaymentMade = EngPaymentMade + PayMadeEnd IfIf (SubjectCode = 28181) ThenCompCandidates = CompCandidates + CanEntCompPaymentMade = CompPaymentMade + PayMadeEnd IfIf (SubjectCode = 64773) ThenEcoCandidates = EcoCandidates + CanEntEcoPaymentMade = EcoPaymentMade + PayMadeEnd IfdatExaminer.Recordset.MoveNextWendlblCanEng.Caption = EngCandidateslblPayEng.Caption = Format(EngPaymentMade, “�#,##0.00”)lblCanComp.Caption = CompCandidateslblPayComp.Caption = Format(CompPaymentMade, “�#,##0.00”)blCanEco.Caption = EcoCandidateslblPayEco.Caption = Format(EcoPaymentMade, “�#,##0.00”)lblTotalCan.Caption = EngCandidates + CompCandidates + EcoCandidateslblTotalPay.Caption = Format(EngPaymentMade + CompPaymentMade + EcoPaymentMade, “�#,##0.00”)End SubPublic Sub CalcTotals(ByVal SubjectCode As Long, ByVal ExaminerNo As Long, ByVal TaxDeductedFlag As Boolean, ByRef CandidatesEntered As Long, ByRef PaymentMade As Double)Dim ScriptRate As DoubleDim TaxDeducted As DoubledatCentre.RecordSource = “Select Sum(NumberOfCandidatesEntered) From Centre Where SubjectReferenceCode = ” ; SubjectCode ; ” AND ExaminerNumber = ” ; ExaminerNodatCentre.RefreshCandidatesEntered = datCentre.Recordset(0)PaymentMade = 0ScriptRate = GetScriptRate(SubjectCode)PaymentMade = (CandidatesEntered * ScriptRate) + 95TaxDeducted = 0If (TaxDeductedFlag = False) ThenTaxDeducted = (PaymentMade / 100) * 22End IfPaymentMade = PaymentMade – TaxDeductedEnd SubPublic Function GetScriptRate(SubjectCode As Long) As DoubledatSubject.RecordSource = “SELECT Payment FROM Subject WHERE SubjectReferenceCode = ” ; SubjectCodedatSubject.RefreshGetScriptRate = datSubject.Recordset(“Payment”)End FunctionJeeva Thavarajah 1163 Page 28

Get instant access to
all materials

Become a Member
unlock