It has to have the ability to store details of customers and parcels. Each customer has their own Identification and they have many consignments, which contain more than one parcel. Customers are charged per parcel by weight it has to check the price of each parcel consignment, to produce an itemised consignment note for each customer. The details actually stored need only include information for 1day.
Customer details include:
* Middle Name/s
* Customer ID
Consignment details include:
* Consignment number
* Customer ID
* Total weight
* Total cost
* Deliver to
Parcel Details include:
* Parcel number
* Consignment number
* Total dimensions
* Cost according to weight
Cost details include:
ENTITY RELATIONSHIP DIAGRAM
HAS WHICH CONTAIN
A customer will have more than one consignment, as they may need to send different batches of parcels to different places. These consignments therefore have one or more parcels within the individual consignments. Therefore this system has to cater for a 1 to many relationships.
DATA FLOW DIAGRAM;
USER INTERFACE DESIGN:
Customer form – This Form requires the entry of each customer’s details this is where customer Identifications is assigned.
Consignment form – This form requires data from each parcel within the consignment E.G Total weight of parcels. As well as customer ID, deliver to address and a consignment number will automatically be assigned.
Parcel form – This form will require the details of each parcel to be entered E.G weight as well as the consignment number the parcel is apart of. It will automatically calculate the total dimensions and round off the weight to the nearest whole number to make it easier for the user to find the according price.
Note: A combo box that links to the Cost table can look up the cost according to weight and then entered in the cost record.
Cost table – This Table contains the various prices according to weight, the user can update this table if the prices are changed.
METHOD OF DATA ENTRY:
There will be separate forms, as shown above to fill in by the user. Customer details, Consignment details, Parcel details. These will all need filling in, to make it more user friendly some of the records will fill themselves in using data already entered in other fields. E.G calculating the sum of length + breadth + height. This saves the users time from doing the calculations and helps to minimise mistakes. Also the calculation of all the parcels weights/costs in one consignment. Also validation rules will be entered, this is to minimise mistakes that can occur by the user.
? Minimum weight 1 kg
? Maximum weight 30 kg
? Maximum Length 1.5 m
? Maximum total dimensions (length + breadth + height) 3 m
? Maximum weight of any one consignment 200 kg
? Maximum cost of a single parcel ï¿½18.45
However if the validation of the data in a field is incorrect then a short message should appear in the form of a message box, to tell the user to correct the data entered.
? Parcels have to be heavier than 1 kg and lighter than 30 kg
? Parcel length can’t be larger than 150 cm
? Consignments can’t be heavier than 200 kg
RECORD STRUCTURE, FILE ORGANISATION AND PROCESSING
The information of each customer, consignment and parcel are entered and recorded in the Customer details, Consignments and the Parcels forms as shown in the user interface design section. They are linked together as follows:
A Access query is used in combination with the consignment form to create the itemised consignment note.
SECURITY AND INTEGRITY OF DATA:
Most places where data has to be entered by the user have set data types; this helps to keep the integrity of the data by minimizing mistakes. E.g. if the data type of the weight of the parcel is set as a number then only numbers can be entered in this field. Also see validation rules. Data, which is calculated by the computer, can’t be changed unless the data in the fields within the formulae are changed.
The database will have various security passwords, which only allows employees access to the customer details. Further more the passwords should be changed regularly to decrease the chances of the data being used unauthorised and illegally. Also the data held may need to be registered to the according officials, and shown to the customer on demanded.
Also backups of the database should be regularly made and stored in a safe place on external media E.G. floppy disk.
“INPUT: Customer details, parcel details, consignment details, Updated prices.
“PROCESSES: Validate passwords, Validate inputted data, calculate Total dimensions, calculate total weight/cost, open forms from main menu, print forms from main menu.
“OUTPUT: Total dimensions, Total weight/cost, Print updated prices, Print consignment details with list of parcels within consignment.
“FILES: Customer, Consignment, Parcels, Cost
DOCUMENTATION OF FORMS AND CODES:
Buttons were built into the main menu so that when pressed the appropriate action occurred. E.G Enter a new parcel.
As you can see above the drop down menu [combo box] is shown, this is how the employee can look up the price of the parcel. With cost on one side and weight in kg on the other. Also below is the design view of the parcel form, where you can see the formulae for the total dimensions.
Total dimensions =[length]+[breadth]+[height]
If the manager wished to change some of the prices, he can do so by pressing the button to update prices this will take him directly to the Cost file to be edited.
Clicking the print prices button on the main menu can print off this new price list. See attached sheet.
I had to create an event that happened in the Consignment file, this event had to add all of the weights of all the parcels within that consignment and display the sum in the Total weight box. This was also done for the Total cost of the consignment.
Total cost =
Private Sub Total_Cost_Enter()
Dim cost As Variant
Forms![Consignment]![Total cost] = DSum(“[Cost]”, “parcel”, “[consignment number]=Forms![Consignment]![Consignment Number]”)
cost = Forms![Consignment]![Total Weight]
Total weight =
Private Sub Total_Weight_Enter()
Dim tot As Variant
Forms![Consignment]![Total Weight] = DSum(“[weight]”, “parcel”, “[consignment number]=Forms![Consignment]![Consignment Number]”)
tot = Forms![Consignment]![Total Weight]
If tot > 200 Then MsgBox “Tot weight must be <=200″
Note: I built in a validation rule for the Total weight (total weight must be <=200”
Below is the query that finds all the parcels in a certain consignment and displays this date this table can then be merged with the consignment form and this is then the itemised consignment note.
I will test the all of the validation rules and some other places were data entry occurs to see if any other validations or data types can be applied to fields to make it harder to enter incorrect data. But mistakes will happen when data is entered but it is virtually impossible to create a database where incorrect data cannot be entered but it is possible to minimise this risk.
Test with parcel weight with a decimal place.
The field automatically rounded the decimal place to the nearest hole number. Which was from 1.5 to 2.
Test with weight larger than 30
Test with length larger than 150
Test with invalid data type entered
Test with weight less than 1