Address:
12 Kologhial Way
LONDON
SW12 7JK
Tel: 08007383773 Fax: 08009465787 E-mail:[email protected]
Monday, January 11, 2010
Mr Sean Okundaye
12 Kirtley House
Thessaly Road
London
SW8 4XX
Dear Mr Okundaye,
I have received the design plan for the database and was very impressed. However, I was not fully satisfied with the overall layout and presentation of the database. Therefore, I would be very grateful if you acknowledge and consider these changes that I have suggested:
- Instead of separating the macros (on the main switchboard) into customers, sales, suppliers and prescriptions, I would rather it is separated into the following categories: forms, queries, reports and letters. This way, for example, instead of trying to print out the reports by opening each macro, one can print out all the reports without the hassle of opening different menus.
- Like I mentioned before, I would like a macro which shows me the letter that is sent to customers whose prescription refill is overdue. This way I can view the letter without the hassle of opening the letter on Microsoft Word and searching for the appropriate query.
- I finally request that every query, form and report is displayed on the design even if it means showing the design over a multiple page spread. This way, one can see every single detail that there is to acknowledge.
I do hope you take heed of these suggestions and hope to hear from you soon
Yours Sincerely,
Olusola Gbayomi
Table Design
Customers
Medicine
Prescriptions
Suppliers
Query Design
Test Plan
Implementation Plan
-
Tables- Create four tables named:
- Customers
- Suppliers
- Medicine
- Prescriptions
-
Relationships- Create a relationship map which links all the primary keys and their records together.
-
Forms- Create five forms named:
- Customer Entry Form
- Medicine
- Overdue Prescriptions
- Supplier Entry Form
- Prescriptions
-
Queries- Create five queries named:
- Search for customers by telephone no
- Overdue Prescriptions
- Prescription in Stock
- Stock over £15 for a given supplier
- Supplier Details by enter of name
-
Reports- Create three reports named:
- Customer’s Details
- Overdue Prescriptions
- Supplier’s Contact Details
-
Mail Merge- Create a letter on Word that merges with the query ‘Overdue’
-
Switchboard- Create a menu using forms that can link to other forms, queries, reports and letters.
Implementation
Test 1: Customer Detail’s Report on Switchboard
Test 2: Enter the date 18/12/1990
Test 3: Run Overdue Prescription Report
Test 4: Enter price £1091.99 in the Transaction form
Test 5: Enter price £0 in the Medicine form
Test 6: Enter the number 3 in the prescription form under the section No in stock
Test 7: Report: Supplier’s Contact Details
Test 8: Enter the number ‘01046291176’ in the query ‘Search for customers by post code’
Test 9: Enter the name ‘Trolovol’ in the ‘Prescription in Stock’ Query
Test 10: Enter the supplier name Equinox Ltd in the ‘Stock over £15’ Query
Test 11: Open the report: Customer’s Details
Test 12: Open up the form ‘Switchboard’
Test 13: Macro: ‘Forms’ on the switchboard
Test 14: Macro: ‘Queries’ on the switchboard
Test 15: Macro: ‘Reports’ on the switchboard
Test 16: Macro: ‘Letters’ on the switchboard
Test 17: Macro: ‘Close Menu’ on the switchboard
Test 18: Macro: ‘Exit System’ on the switchboard
Analyse
Hardware & Software
Hardware- I am going to use the following hardware:
-
A PC (in terms of a computer, mouse, keyboard, monitor) – I am using this because as one is creating a database; one needs to use a computer if one wishes to create a sufficient database. A simple, hand drawn database would be inadequate and time consuming. The data, via the keyboard will be inputted using this system.
-
A colour laser printer- I am using a toner-based colour printer rather than an inkjet colour printer because inkjet printers are far slower than laser printers and have the disadvantage that pages must be allowed to dry before being aggressively handled; premature handling can cause inks (which are adhered to the page in the liquid form) run.
-
A black laser printer- Again I am using a toner-based printer rather than an inkjet printer because inkjet printers are far slower than laser printers and have the disadvantage that pages must be allowed to dry before being handled. Furthermore I am only using a black and white printer where colour is not mandatory.
Software- I plan to use the following software:
-
Microsoft Access – Relational databases in access are far more efficient and are at a higher level of class than flat file databases in Excel as they allow you to store, edit, add, remove, search and sort data. It can perform the calculations that are required and can display a report of a particular person, in this case, the customer. With relational databases you can also share information between tables and queries, set a primary key in certain fields to identify records and one can specify the different data types. Although Excel is quite efficient it has severe limitations whereas with Access one can do complex searches (“Queries”).
-
Microsoft Word- I am using Microsoft for the letter as it is basically the only efficient way to do most of the designs and the theoretical part of the course. It is much more superior compared one’s handwriting.
Input and Data Collection
The data used will be information on:
-
A list of all the customers of Mr Olusola- I am going to receive information from an address book containing vital information which will be given by Mr Olusola. This information will be inputted via a keyboard into a table under appropriate field names e.g. the name ‘Linda’ will be under the field ‘First Names’. Each field will have an appropriate character limit and will the field ‘Customer No’ will be made primary key. An age limit will be put under the field ‘Date of Birth’ meaning that anyone under the age of 18 cannot use the Gbayomi Pharmacy.
So the following data items shall be inputted:
- Customer No
- First Name
- Last Name
- Title
- Date of Birth
- Prescription
- Date of Renewal
- Recommended Daily Dosage
- Address 1
- Address 2
- Address 3
- Postal Code
- Contact No
-
A list of all the medicines available- This information will be inputted using information from Mr Olusola’s book records and archives with information about the amount in each container, the no of stock left, plus having its own primary key, ‘Prescription No’. Validations will be made meaning that Prices of the prescription cannot be £0, supplier umbers will not be allowed to 100 or over (this is because the first supplier’s number is 100 and none of them are lower so therefore, this validation will stop mistakes being made) and so that stock that has been reduced to five will be made clear rather than Mr Olusola having to make a mental note. All the pictures of the prescription however will be given to me via a Universal Serial Bus and saved onto my network drive.
So the following data items shall be inputted:
- Prescription No
- Name
- Prescription Type
- Price
- Amount
- Supplier No
- No in Stock
- Recommended Daily Dosage
- Picture
-
A list of all the suppliers and their specialities- Mr Olusola will give me an information booklet about all the suppliers and what they supply. This will then be keyed into the database using a table and a primary key will be made with the supplier no.
So the following data items shall be inputted:
- Prescription No
- Customer No
- Price
- Order Date
- Collection Date
-
A list of all the prescriptions- Mr Olusola will give me a booklet with their bar codes, the customer using them and the supplier. This will be keyed into the database via a keyboard into a table. There will be no need for a primary key. There will however be a price validation which will make sure that no price is higher than £999.
So the following data items shall be inputted:
- Supplier No
- Name of Supplier
- Address 1
- Address 2
- Address 3
- Postal Code
- Contact No
Output
The database will include two different types of output. It will have both Screen Based and Printer Based Outputs. All of the forms and queries will have a screen base output. However, most of the queries will be turned into reports and all of the reports will have a paper base output. The queries that will not be made into reports are the parameter queries. Although these could be made into reports, they will not be as they can only show a few records at a time and are few searching and so would be useless as a printout. All reports will be printed portrait on an A4 page.
All the forms that will be created will only be seen on screen as they are data entry forms and so will constantly be edited and so are not needed as printouts. The mail merge letter will be printed out portrait on an A4 sheet of paper.
Processing Methods
The next part of the database will be to create queries and reports. In total there will be five queries and three of these will also be made into reports. The other two queries will not be made into a report as they are the parameter queries. These are firstly, Overdue Prescriptions query the user is not prompted but instead an entire list of people with overdue prescriptions is shown. Secondly, Prescriptions in Stock: when this query is opened the user is prompted to give the name of the medicine and a medicine along with its details will be shown.
The final three queries are; Search for customers by telephone no, Stock over £15 for a given supplier and Supplier Details by enter of name. Search for customers by telephone no will show all the customers with their details, and will include fields from Customer table. Stock over £15 for a given supplier will show all prescriptions that are £15 or more and will include fields from the Prescription and Suppliers table. Finally, Supplier Details by enter of name will include the supplier table and will show the contact details and other information that correspond with the supplier. These queries will make up all of the reports.
The next stage of the production will be creating the mail merge letter – Overdue Reminder. To begin the letter, the core of the letter will be typed up from a handwritten template made by the user –, Mr Olusola. Once this core template has been made, I will browse for the correct data source – the database and the Overdue Prescriptions query. When the correct source has been chosen, I will insert merge fields like the customer details and the date prescription in question. Once the letters have been made and previewed, the letters can be checked manually and then printed.
Data Flow Diagrams
Tables
Reports
Backup & Security Strategies
The database will be backed up by at least 2 computers, laptops or USB. The file itself will be roughly 20MB and take 15 seconds to upload on to a USB and under 5 seconds to take from USB to PC. There will be two computers within the company’s premises with access to the file. These computers are that of the reception and the owner - Mr. Olusola. The file will be kept on a network between the two so if changes are made to one, they will occur on the other PC to. Mr. Olusola will keep a USB with him at all times. Each day he will copy the file from the network onto his USB stick and take it home with him when he leaves. This copy will then be copied onto his laptop at home which means that a daily copy of the database will be kept on the network, Mr. Olusola’s USB and his personal laptop as well. Finally, a monthly copy of the database will be kept on an external hard drive in Mr. Olusola’s safe at home. This means, in total, there would be three copies of the database. Other options that we considered were e-mailing the copy and floppy disks/CD.
To ensure tight security, I am going to enforce the following measures:
- Each copy will be protected by different passwords. This is to ensure that if a USB gets stolen or is lost or a computer is stolen, no one will be able to hack into the database and even if they find out the password of one database, they will not know the password of the other copies.
- There will be a virus checker/debugger to stop viruses from deleting any vital files or distributing information such as customer information.
- The database will be read only unless a password is inputted to ensure that no data is sabotaged.
EVALUATION
EVALUATION OF OBJECTIVES:
- When the database is first opened a main menu appears automatically. This has links to five further menus, which enable the user to add/edit information, print out reports and letters, or check queries.
- Inputting information is without its problems with the help of data input forms. It is very time efficient when one wants to add/edit a record; the information is separated into customer and supplier details, medicines and their statistics and customers prescriptions. This should apply to any information Mr. Olusola is intending to input.
- There is a menu button of which links to a WinWord document with a mail merge reminder letter for overdue prescriptions, which is constantly updating as it is linked to the “Overdue Prescriptions Query”. One needs only to press print and letters will be printed to customers with overdue prescriptions. Furthermore the template of this letter can be replicated for Mr Olusola to write letters to the customers and associates.
- When adding customer and supplier information a few precautions have been taken to increase the accuracy of data inputted:
- Combo boxes to avoid errors, especially with names that are complicated and as Mr. Olusola’s customers are of various ethnic backgrounds it would be highly embarrassing if mistakes were made.
- Identification numbers and relationships in a relational database so one does not duplicate data for example if a customer or supplier changes their phone number or address.
- Validations within the database to make sure people using the database who do not have an outstanding knowledge of computers can update information onto the database easily with error messages to notify the user of any mistakes they are making.
- There are three reports to show the customer’s address and contact details, supplier’s contact details and the prescriptions which are overdue.
- There are five queries to search for customers by telephone no, to show overdue prescriptions, prescription in stock, stock that is over £15 (with a given supplier) and supplier’s details (by entering their name)
- The system could be developed to initiate a monthly warning system of which triggers the automatic updating of data to begin the month. The system would sort out customer and supplier details in case of changes in certain details which have not been currently recognized using another database as a source. There would also be a button to add and sort the details of new suppliers and customers who came during the month.
- In the customer, prescription and supplier form the system would automatically reorder the records in numerical order with the details every time a new record is added. This would be unproblematic.
Address:
12 Kologhial Way
LONDON
SW12 7JK
Tel: 08007383773 Fax: 08009465787 E-mail:[email protected]
Monday, January 11, 2010
Mr Sean Okundaye
12 Kirtley House
Thessaly Road
London
SW8 4XX
Dear Mr Okundaye,
Thank very much for your database. Because of you we at Gbayomi pharmacy can sort out our records with ease. However there are is a suggestion that I would like to give to you if that is ok:
- The system could be developed to initiate a monthly warning system of which triggers the automatic updating of data to begin the month.
The system could maybe sort out customer and supplier details in case of changes in certain details which have not been currently recognized using another database as a source. There could also possibly be a button to add and sort the details of new suppliers and customers who came during the month.
I do hope you take this suggestion into account. Do not hesitate to contact me.
Yours sincerely,
Olusola Gbayomi