I want there to be privileged use of the database. Therefore, some people will be able to read, edit and input into the database, whereas, others will only be able to read or edit or input, not perform all three.
I want the user to be able to create new letters whenever needed and perform a mail merge to selected clients.
I would like the presentation of the screenform to be attractive.
Investigation
The present system is a manual filing system, this consists of information written or typed onto paper or card and then filed away in cabinets. This information was collected over the phone or through application forms and then filed away in alphabetical order by company and then by surname.
These are the basics of a manual filing system, but each stores different data in different ways.
–E.g. in numerical order, or alphabetical order, etc.
To discover the specifics of the manual filing system dealing client information and sending out invoices, I will interview the owner of the company. The information will then be analysed, before a suitable database is found to be implemented especially for Fab Food Pix. This information can be collected through e-mail, fax, over the phone, through questionnaires or in person through interviews. To find the specifics of this particular company’s filling system I interviewed the owner.
To collect further information about the present system, questionnaires will be designed and handed out to the owner and employees in the work place. The questionnaires will be mainly open questions, as not enough specifics are known to produce closed questions. The questionnaire will be made up of such questions as; how long should a search take? What information would most likely to be searched for the most? What will the main uses of the database be? – Such as mail merge, invoicing etc. I am trying to find out how the present system is used, what its main uses are and how the database should be designed in order to make these procedures the easiest to perform.
Analysis
Computerisation will solve many of the problems found with the present system. Storing information on computer will save space, it will be easily accessible from any computer in the building, computerised information is much more secure, as passwords can be used to ‘lock’ the information so unauthorised personnel cannot access it. Print outs can be quickly made, so invoicing is much easier.
However, there are problems with computer systems, for instance, the system may crash when overloaded or a circuit failure. In order to prevent this from losing the business’s information, back ups must be made of everything (of importance to the company) these would preferably be done on hard disk, but could be done on floppy disk or paper.
Specification
The new system
- Must have a well presented data entry screen
- Must be able to perform searches in less than 30 seconds
- Will have a macro button in order to make invoicing quicker and easier.
- Will have passwords so there will be priviledged use of the database.
- Will have an attractive screenform. (according to the owner)
A data flow diagram of the proposed system is shown below:
Client details Client no. add.
Etc.
Client
details
Mail merge C Client details
Invoice I invoice ID
Client no. add. Etc. Invoice ID, date sent
Overall, the new system will allow the users to search for a company, surname, or any field in the client details previously entered, in less than 30 seconds. This information can then be used to mail merge an invoice to any of the clients in the database.
A computer database system is the best method for fulfilling these tasks because it is electronic and so, much quicker than using manual labour. Pre-entered information can be transferred between documents, whereas if information is only recorded on paper, information cannot be easily transferred from one document to the other. Overall the computerised database system is the basis of a set of computerised filing cabinets.
Implementation of Solution
There are numerous different types of hardware and software available to comperturise Fab Food Pix’s manual filing system, the hardware which will be used to design the database is a Power Mate I-Select XL2 P1700. This computer has Intel Pentium 4 Processor 1.7 GHz, 256 MB RDRAM, 60GB 7200RPM Hard Drive, 16X DVD-ROM and 12X CD-RAW, 19” NEC CRT Monitor (18” viewable) Quad Speakers and Headset and Optical Mouse and a 56 Kbps V90 Modem. The software, which could be used to design the database is; Microsoft Word 2000, Microsoft Desk Top Publishing 2000, Microsoft Excel 2000 or Microsoft Access 2000. The computer that will be used in the work place will be a Power Mate I-Select XL2 P1400. This computer has an Intel Pentium 4 Processor with 1.4 GHz, 128 MB RDRAM and a 40 GB Hard Drive. It has 17” NEC CRT Monitor (15.7” viewable) a 10/100 MB Network Card and a Software Package.
Although four programs have been mentioned, only one program can be used to create the database. So, I will now proceed to investigate the different types software mentioned to find which is the easiest and most effective to design, implement and use.
I predict that word will not be exceptionally beneficial when used to create a database as it was preliminarily created as a word processor. Any database that is created is in the form of a table, in which no searching or sorting can be done.
Microsoft Word 2000:
This database was complicated to create and it is not possible to type in for instance, T. Hill and for the computer to then search and display the fields connected to that particular field. So, therefore, my prediction was correct and Microsoft Word 2000 is not appropriate to use in order to create a database.
The same is true for both the desktop publishing (Microsoft Publisher) and the spreadsheet programs, on the desktop publishing and word processing programs little can be done in the way of sorting the data for both these programs. All four programs are capable of sorting to a certain extent, however none have as many features as Microsoft Access 2000 (database program). The main problems found with the first three packages is that is not possible to isolate and search a group of individual records, they can merely be sorted into the some order.
A database program has many useful abilities;
- The ability to sort by date, numeric or alphabetical options, and the ability to then create standard letters from the sorted data
- The ability to create a file by entering specific field definitions;
- To specify automatic validation checks for fields;
- To add new fields to records or delete fields that are no longer needed;
- To add, edit and delete records in a file;
- To perform simple searches and complex searches using more that one variable;
- To import data from other applications software;
- To export data in standard file formats to other applications software;
- To create customised report forms for output;
- To create customised data entry screens;
- To create customised menu screens and link them together.
These are some of the useful characteristics, which make using a database program much more efficient than such programs as desktop publisher or Microsoft excel. Databases are much more malleable than other afore-mentioned programs, they can either be a single file containing a large number of records or a collection of related files. Most modern databases are relational, this word describes the way in which the data is organised within the database. A relational database stores data in tables that are linked together using common fields. This factor is most useful for the database being designed for Fab Food Pix, as the amount of client information needed by the company covers such a wide variety of areas a relational database would most appropriate to accommodate these specifics.
Having investigated other possible programs that could be used to create a database, it has been decided that a program purposefully designed to make databases will be used. There are number of different database programs, such as Microsoft access, Paradox or Lotus Approach. However, Paradox and Lotus approach are not available, so Microsoft Access must be used. Microsoft Access is also available in the workplace so it is the perfect program to use.
Data Collection, Capture and Input
For a database to be of use, the information it is to consist of, must be collected, captured (on paper) and then input into the previously designed database. In order for these targets to be attained, a data capture form must be created. Information will be collected from the company’s present client information file this information will then be recorded onto the previously created data capture form any information not found here will be obtained through questionnaires.
The currently used manual database will be keyed into the computer. A data capture form will be designed and implemented to collect the details of new clients ringing up to book the photographer for a job. The same data capture form will be used to record the information of new clients who turn up at the studio, inquiring about the services provided. All the information will be stored, before the client is used, therefore only one data capture form will be needed.
The client database, which is to be updated to a computerised version will primarily be used for mail shots, cards and promotional information as well as sending out invoices. As these are substantially separate, a relational database will be used, so that when an invoice is being composed, the name, company, position and address of the client can be displayed after searching for the specific job and it’s costs. The first table in the database will contain information for producing mail shots. The fields included will be such fields as:-
- Client Name
- Company (e.g. Dragon, Radius, Selfridges etc.)
- Position (within the company such as, picture buyer, art director etc)
- Description of Past Jobs
- Previous Company (if moved)
- Company No.
- Private No./Direct Line (mobile/private line when in a large company)
- Company Address
The second table in the database will contain information for producing invoices. The fields included will be as follows:-
- Job Dates (date begun-date finished)
- Dates of Re-Shoots (if applicable) (date begun-date finished)
- Brief Description of Job (e.g. M&S Christmas hampers etc.)
- Raw Total (excluding VAT)
- VAT
- Total Invoiced (amount including)
- Date of Invoice (when sent)
- Date of Payment (date payment was received)
The second table will be updated after each shoot, whereas the first table is updated whenever a new client is used or when previously entered information is changed.
WHAT ARE THE ADVANTAGES OF THE DIFFERENT TYPES OF DATA COLLECTION/CAPTURE?
Data Capture Form – Client Details
Data Capture Form – Job Details
Data Verification and Validation
There are not many different forms of verification, proof reading, where once the document has been typed out, it is read by a proof reader who points out or highlights any mistakes. However, this is not very reliable as, there is always a possibility that the proof reader will not pick up every single mistake grammatical or spelling. The other form of verification is double entry. This is when two people write out the specified information on two different computers. The computers then match up the two sets of correct data. If not all the information is matched up, mistakes are present. It is unlikely that each person will make no mistakes, or make the same mistakes. It is likely that where one person has made a mistake, someone else will not have. The document (if found to be containing mistakes) must then be proof read to find the mistakes and the sources of the information must be used to then correct the document.
It is most beneficial to have a validation rule on the database, to check for any errors. Validation rules can be made for the majority or fields. Fields such as Raw Total, VAT and Total cannot have validation rules, as there is the possibility that every single entry in this field may be different. Due to the type of data entered into my relational database, validation rules are not often appropriate. For instance in such a field as Client Name and Company Name a validation rule could not be used as it is not possible to know all clients and company’s which were to be entered into the database as this is occasionally updated. However, a validation rule was made for fields such as Client ID, Job Dates, Dates of Re-Shoots, Date of Payment the validation rules of majority is ‘Date/Time’ the format must then be set to the specific type of date. The date can be ‘General Date’, 05/09/99 17:32:44, ‘Long Date’, 28 December 1987, ‘Medium Date’, 28-Dec-87, ‘Short Date’, 28/12/87, ‘Long Time’, such as; 17:32:44, ‘Medium Time’, 05:32, or ‘Sort Time’, 17:32. The validation rule for the Client ID field is ‘Auto Number’, this means the computer will automatically fill in this field with a unique number, there will only be one each number. This rule prevents a Client ID number from being entered more than once. Other fields have been set, so that unless information is entered, the user is not able to progress any further.
Relating the Database
In order to relate the two tables effectively, the primary key field ‘Client ID’, in the Client Details table was made an automatic number (i.e. all the numbers included in this field were unique.) this primary key field was used as a foreign key in the Job Details table, which was not an automatic number, as a client could have numerous jobs with the same photographer. Therefore, I created another key Job Dates, this key along with the Client ID key made each record unique.
Building a Macro Button
How do you do that sir?? Sir says ‘open access’
User Guide.
1. Opening Microsoft Access
It is possible to open Microsoft Access from the desktop by simply locating the icon on the desktop and double-clicking on it. However, it is also possible to open it through the shortcut in the Start menu:
2. Opening the Database
Once the right side of the mouse is clicked, the program will open.
This screen will then appear:
To open a previously created database, click OK after highlighting ‘Open an Existing Database’. Then clicking on client database and click ‘OK’
3. Entering into the Forms
This screen will then appear:
Depending on which database is needed, either ‘Client Details’ or ‘Job Details’ will then be double-clicked upon. This will subsequently open the selected database.
The screen will then proceed to the display below if ‘Client Details’ is selected:
Troubleshooting Guide
This guide will help with problems, which may be experienced while using this particular database:
Testing
To make sure the database works to it’s full potential