- Works does not provide as many advanced tools as the intermediate user may require. This goes as far as customisation of functions and merge facilities, which although are possible it would be nice to adapt Works to suit SQL databases.
- In Publisher there are no functions for using search criteria. This mainly due to Publisher not being a program designed for multiple sheets, or database for that matter. It is a design and presentation desktop publishing tool and boasts no more than that.
- Another major thing that Publisher does not have is the ability to edit and adapt presentation effects, for an easy example, WordArt. Now this is a major commonly used graphic program, but I do not feel that there are enough functions and options to manipulate WordArt. This is also the case for many other parts of Publisher. It just could be improved a lot more.
- Publisher does not allow different data entries either. As explained above this is due to the lack of database functions it has. However, Publisher does allow different columns in a document to be linked (cross-linking) which may be to keep the same editing and format. It’s somewhat basic, but it’s something.
Microsoft Access:
- Access is a brilliant program for creating databases and forms. It has many carefully selected components and functions, and as time has progressed many more features have been included. Some of which have been used in the following document.
- Because it is a database tool, it can manipulate data entries and use them as part of a search facility, in built in databases, which can easily find and locate certain data strings within a database.
- The good thing about Access is that although it is somewhat more complicated that some of the other software tools that have been mentioned above, it only takes a very little time to get used to, which means it can easily be used for a business or alike to manage and control many aspects of its workplace. This is exactly why I have chosen Access as the program for my problem solution.
- You can also incorporate Macros into the document, which link one piece of data to another, but this can be done by other means as well. Macros are very helpful in creating buttons and navigation around a database, such that queries and forms can be used and viewed in stylish and easy to find ways.
- Another good part about Access is its ability to show and explain errors, which come up in editing databases. It will let you know if you have missed a field, or forgotten to finish a macro. This is very helpful, especially in bigger documents whereas a small mistake can cause major problems in the database, maybe stopping a link, which will ultimately edit the way a query comes out, which is linked to forms. So error-control is extremely important in Access.
- Although Access is a great tool for what it does, and as well as including some form of presentation ideas, it does seriously lack ease of use in the presentation part of the program. I had many struggles when editing work, and it seems that text is even harder to control if you haven’t used Access for presentation before.
- Also, I suppose you could say that Access was difficult to use in parts, it fits both sides because on the one hand if you’ve used any Microsoft products before (especially the Office suites) then you will have a basic knowledge of the way their program work, and therefore things are very easy to use. But for a complete beginner, I think that Access is a very hard program to use, there are just so many functions that it has that many people won’t even understand without some basic guidance to what it does.
- Access, unlike many products on the market today, lacks many templates that can help to give a basis on a database. Being a professional piece of software, it should come with a host of templates but instead doesn’t appear to. However, you can get some templates off the Internet, as with all office software.
Overview:
From the above you can see why some software tools have been rejected and others used. I will choose to use Access for my database software, due to its extensive functionality for databases. It has enough functions for the Snowboarding Company, and suits all requirements. Excel was another strong candidate for the Snowboarding Company, but it just didn’t boast an efficient set of functions. Access it is.
Hardware Constraints:
Firstly, this system needs to be run in an environment suited to a computer, this would probably be somewhere in the operating temperature of around 10-30ºC and needs a clean, water-free environment. Next the features needed for a system to operate the database. These are as follows:
Minimum Requirements:
- CPU – A Pentium Processor of minimum operating speed 200mhz.
- Memory – This needs to be of minimum 16mb to allow a system to run software at a reasonable speed and accountable for a large file to be run and complicated equations.
- Hard Drive Space – This must be firstly enough to run the software, which may need anything up to 100mb of free space, and also to accommodate a database file which can increase in size as data builds up. 200mb of free space is needed.
- Mouse – The database CAN be run without a mouse, but it is somewhat difficult to master and can save a lot of time with a mouse.
- Monitor – A monitor is required to display data to the user. It must be of minimum colour depth of 256 colours.
- Keyboard – A keyboard is required to enter data into the database.
Optimum Requirements:
- CPU – Pentium III 500mhz will allow much increased performance.
- Memory – 128mb will allow fast memory and software data transfer.
- Hard Drive – 4gb will store excess files and reports if needed, and can accommodate upgrading of software, and database packages if needed.
- Monitor – 1024x768 resolution with a 32mb colour depth.
- Sound – A Sound card will allow alerts and user interaction with the database if errors occur.
- Keyboard
- Mouse
End User Requirements:
Snowshock beanies have a number of end-user requirements that they felt need to be met in order to produce a database which meets their design standards. These are as follows:
- Snowshock want a system which is easy to use and enter data into, even for the less computer-literate users.
- The user should be able to access the database in a secure manor. This will mean that backups of the database need to be kept.
- There will be a large amount of records, so there needs to be some sort of checking system that allows for visible errors (so that it can be edited easily).
- Easy navigation is the main point for the user, if he can find what he is looking for then it will save time, effort and make adding and editing records easier as well. Forms will be the key in making navigation easy, and a switchboard may also be added for a nice opening splash screen and options menu.
Who will use this database?
The database will be used by staff within the shop that have authorised access and knowledge to use the system. The database or system will be password protected to prevent any unwanted access. Due to the large size of the database, as time goes on, there will be several copies of it kept; this stops the accidental deletion or mistakes from happening.
Analysis:
Problem Solving:
An answer to the Snowboard Company is to run a database, which will keep track of all sales, being linked with the customer’s information. This would be used to send the customer relevant information and sales, whether it is through e-mail, mail or word-of-mouth. It would also be used to see what products are most popular with a visible count of sales for a particular product, and this would also be available to see which customer buys most products. This is a great advantage over using standard filing because not only can it be accessed in a matter of seconds; it also eliminates the need for new records upon editing needs.
Collecting Information:
Because the database will be subject to a vast amount of records, it will need to have adequate information about the customer, so that record entries can quickly be accessed and edited as necessary. This information will be simply collected from a customer and entered. This will take a long time. New records can be created for new customers and existing customers can have information added to the database. With more advanced databases it could be linked automatically so that sales, when generated, are updated instantly into the database.
Data Requirements: Tables
Due to a database being put into different sections, there will need to be raw data to start with that will contain the main information about customers. Later on in the database, more specialist information can be ‘pulled up’ which will be used in different ways depending on what the user is looking for. Many of these will decide which customers are best buyers and which deserve special discount and such. Going back to the raw data however, here is the information that will need to be gathered:
Tables:
- Customers
- Sales
- Switchboard Items (optional)
Queries:
- Full (all records)
- Search
Forms:
- Customers
- Sales
- Full (all records)
- Search
- Switchboard (optional)
Reports:
- Full (all records)
- Search
Data Requirements: Source
Up until now, the system of keeping records of clients and sales has been an old-fashioned filing system, whereas each individual client has information written on a sheet of paper and filed away in a cabinet. It is a slow and inefficient system, especially due to the fact that when a change needs to be made to a record, instead of editing the text, an entirely new record has to be re-written in the correct format.
Data Input:
Data will be put into the database via keyboard. In some cases the data will have been copied from handwritten records, or receipts that contain necessary data.
Queries:
Queries are basically the next stage to creating a form, it is where the data from a table or such is sorted into a legible table. The database will require queries to take fields from certain tables and use them to link together data and create a more specified output of data. This will be shown when a full query is made. This will be to allow all data that has been entered into the various tables to be shown in one query, this will be done because at some point or another a client will need the full database to be used to compare products that are selling well, to allow for a more efficient stock for the coming season, e.g. to see if say bn-02 is selling well, and if not whether it is worth removing from stock for a better product that has been marketed and proven to sell better.
Also a search query is needed, this will be so that users can customise the data that needs to be shown, e.g. the user may simply want to see how often products are sold, so takes the fields of time, date, product and price and then can see easily how often they are sold, and perhaps extending on that, the user may then be able to have criteria showing, for instance, bn-02, and then will be able to see how often that product is sold. Also, relationships will need to be set up to allow data to be cross-referenced, making sure data in one table matches data shown in the other and visa-versa.
Forms:
After the queries have been created, forms are made to produce a clean and presentable set of data. After a form has been created, the data can be accessed in a more presentable way that that of the raw data in tables. For example, when a form has been created, you can then enter a new record into it in a much more efficient way and the same goes for when editing the whole form, because the data is set out into a format that is easier to understand than in tables. Also the forms can be more personalised to Snowshock because design view boasts basic presentation and design functions. One major piece of the database, the switchboard and splash screens are both created as forms, because of the high presentation capabilities of it, this makes the project easy to navigate, and therefore meets the end user requirements, as stated earlier in the project.
Reports:
Reports are used in the database to simply take presented data from one place and to show it in a fully presented, printable format. Most reports show the page number and current date, which makes the look of the report more professional. A standard set-up from a report also occupies the entire space of the page, maximising its use, so that a full database report can be filed and kept away for review at a later date. Most reports are based upon a query though, because a query can easily be edited to change data view, but a form may take more time, and also a table is simply raw data, and instead of editing a query to produce a customised report, several reports would need to be created if it were based around data from a table.
Macros:
Macros will be the more advanced or customised parts of the database which are not in standard menus. In the database, there will be several macros that need to be created. Most of which will be used for functions to navigate and control the database through a switchboard system, which although is optional, creates an easy to use and manageable database allowing a less experiences user to access to be able to use the database. One of these macros is the exit button from the database, which simply closes down the database, whilst saving it at its last known state. This will most likely be the last item on the switchboard. A more complex macro will be that which allows the query to be customized (and used a search tool), but from the switchboard. This would open up a query in design view. And of course, for the more experienced user, there will be a macro (again, from the switchboard) which allows the user to edit any piece of data within the database, based from a full query (of all sales and customers). This would be used to update the customer info to transactions that do not have all their fields entered.
Data Flow Diagram:
Design:
Starting the Database:
To start with we need to begin by making a new database. This is done in Access by going to File (Menu), then New… and select Blank Database. This may differentiate depending on what version of Access is used, but generally that is the rule.
Tables & Table Structure:
Next, we need to create the separate elements of our database. This changes again depending on the version used. However, as this database is created using the latest version, this document uses references from Access 2002 (XP Version).
To start with, a simple table needs to be produced, this can be done in many ways, but for someone to learn, as with all aspects of Access, it needs to be done in design view. Once in design view, the fields and field types need to be set up, this can be done as follows:
As seem above, at times the data type is set to text when in fact, the field will contain numbers (primarily in ‘phone number’), this is because sometimes people write their phone number as #####-###### or ##### ######. This is to allow the gap or hyphen which maybe put it, making the number easier to memorize and view. Text is also used as the data type when other types of text is used such as ‘@, £, $, %, #, ~, -‘.
However, after this data has been entered, there are a few validation rules that need to be edited. This is done by selecting the field which needs to be edited, then below where it says ‘Field Properties’, you will see (under the General tab), a field called ‘Required’. Set this to yes, and then if when a user is entering the date into the table and does not enter anything into the field which has the required set to yes, an alert will popup. There may be some other fields that need to be adapted in the Field Properties for each individual field. So below is a short outline of the fields and their property settings (the ones that are relevant).
CUSTOMERS:
Name:
Field Size: 50
Required: Yes
Allow Zero Length: Yes
Indexed: No
Unicode Compression: Yes
This field is not indexed because there may be more than one entry for the particular name, e.g. Joe Bloggs may have made more than one transaction. Also zero-length is set to yes because there may not be an update for the customer info, but only a transaction, so until the transaction has been linked with the customer then it may not have an entry for the name.
Address:
Field Size: 50
Required: Yes
Allow Zero Length: Yes
Indexed: No
Unicode Compression: Yes
This field is not indexed because there may be more than one entry for the particular address, e.g. Joe Bloggs lives at 20 North Road, and he may have made more than one transaction. Also zero-length is set to yes because there may not be an update for the customer info, but only a transaction, so until the transaction has been linked with the customer then it may not have an entry for the address.
Phone Number:
Field Size: 50
Required: Yes
Allow Zero-Length: No
Indexed: No
Unicode Compression: No
E-mail:
Field Size: 50
Required: No
Allow Zero-Length: Yes
Indexed: No
Unicode Compression: Yes
This is one of the few fields that do not have required set to yes, this is because some people do not have the internet and therefore may not have an e-mail address.
Reference Number:
Field Size: 50
Format: “(red)”
Required: Yes
Allow Zero-Length: Yes
Indexed: Yes (No duplicates)
Unicode Compression: No
The reference number does have indexing on, this is so that there are no duplicate reference numbers in the database, if this was so, records would get mixed up with customers, so this in place to make sure that 2 reference numbers cannot be present at the same time.
This table now needs to be saved as ‘Customers’ then this process needs to be repeated for a table which needs to be saved as ‘Sales’. This will look as follows (in design view):
With the following set of field properties:
SALES:
Date
Format: Short Date
Required: Yes
Indexed: Yes (Duplicates OK)
Duplicates are allowed in this field because there may be more than one purchase on a given date.
Time:
Format: Medium Time
Required: Yes
Indexed: No
Product:
Field Size: 50
Required: Yes
Allow Zero-Length: No
Indexed: No
Price
Format: Currency
Decimal Places: Auto
Default Value: 0
Required: No
Indexed: No
Reference Number:
Field Size: 50
Required: Yes
Allow Zero-Length: No
Indexed: Yes (No Duplicates)
Unicode Compression: Yes
Like in the sales customers table, the indexing of the reference number stops duplicate reference numbers from appearing, thus reducing client error when entering a reference number, especially being that some may be very alike.
Queries & Query Structure:
Queries are somewhat different in their set-up as opposed to tables, because they generally rely on a table or query to have been created beforehand. As we need to create a query in design view, as with most of the database elements, design view asks for what table/query to be used in reference immediately when opened (if by some chance design view does not ask which tables to show at the beginning, click ‘query’ and select ‘show table’).
For this query, which will be a full query, both sales and customers tables need to be involved, linked, and organised to create a full query of all records in the database.
So to start with, customers and sales need to be brought up on the screen. This is done by selecting the customer, and sales table and clicking add. (from show table dialogue). Then select the fields that need to be displayed. Double click them and they should come up in the box below. Those that need to be displayed are as follows:
CUSTOMERS:
Name: SHOW
Address: SHOW
Phone Number: SHOW
E-Mail: SHOW
Reference Number: DO NOT SHOW
SALES:
Date: SHOW
Time: SHOW
Product: SHOW
Price: SHOW
Reference Number: SHOW
The order in which these need to be displayed are as follows:
- Date
- Time
- Product
- Price
- Name
- Address
- Phone Number
- E-mail
- Reference Number
Then the sorting needs to be set-up so that certain fields are displayed in certain arrangements. In this query it is only necessary to show the date and time in order.
To do this, select the field that needs to be sorted and next to the sort tab, select the required type of sorting. The following fields need to be edited in this way:
- Date (Sales): Sort Ascending
- Time (Sales): Sort Ascending
Relationships:
Relationships need to be set-up to make a link between sets of data so that one can be cross-referenced to the other. In a way it checks to see if that particular piece of data matches that of the linked one. This is particularly useful in this database because there are two tables, both with reference numbers that should read the same. Therefore a relationship needs to be set up with the two reference numbers. This can be done in the query design view by selecting ‘Tools’ then ‘Relationships’ from the top menu bar. Then by selecting the Reference Number from Sales and dragging it onto Reference Number in Customers. This should bring up a ‘Join Properties’ Dialogue. The most important thing in this is to select option 3 which is:
Include all records from ‘Sales’ and only those records from ‘Customers’ where the joined fields are equal.
If this is set then it will do what is required, which is to display all transactions, and the reference numbers of the transactions that match to those displayed in the customers section. So in a full query, all sales are shown, and in some places where the customers haven’t been matched to the sales, there will be gaps, which will need to be filled in manually. This is why an switchboard may be set up to allow for easy access to editing functions throughout the database, as inevitably and some point or another clients will need to edit the database.
Forms:
We now need to create forms from these elements we have created. To do this, as usual, select forms and click create from design view. You will notice a different format come up from that of before because there is now presentation and layout incorporated into the database. But at this stage it is not necessary. Making the forms is somewhat more complex than that of the queries and tables, because the data is not already in front of you. So to start with, we need to have a list of available fields from a query or table. This is the difficult part because there is a small button located on the toolbar that is designed specifically for adding date sets to forms (New Object). It looks like tiny form with a star at the top left (yellow star). Some versions of Access have a drop down menu to the side of it (XP Version does) which has a variety of different options available. Once the box has been located, select Form from the dropdown box (or simply click the button), and a ‘New Form’ dialogue will come up. Making sure that Design View is selected, the object’s data source needs to be selected.
So far, we have created 2 queries and 2 tables (excluding Switchboard Items, which is automatic). However, 4 forms need to be created. These will be as follows:
- Customers
- Sales
- Full
- Search
Now, for customers, when the New Form dialogue comes up, the object’s data source needs to be set to ‘Customers’, so that the fields and data from the table ‘Customers’ is displayed. The reason that it is obvious that it is the table ‘Customers’ and not the query, is that the query was never made, as it was not required. Once the data source and design view are selected, click OK and you will notice a new form comes up, but with a dialogue that contains the fields from the ‘Customers’ table. For this Form all fields from it are required, so to get them onto the form view, drag the field and place it onto the form area. If the form needs to be larger, then when the mouse pointer goes over the edge of the form area, a different cursor will come up (resize), and then just drag the form area to the desired size. Once all fields from Customers are on the form, they can be reposition to suit the requirements of the layout. Access 2002 snaps the Field and Field name together, so if you move the Field, the Field Name comes with it, this can be separated by clicking the larger square (top left corner), of the move feature, and then repositioning the Field, or Field name. Once you have clicked off the larger square, Access 2002 automatically snaps the two back together again. After the fields have been positioned correctly, presentation can begin. To start with, depending on the layout of the form, some sort of label or logo needs to be put in the header of the form to show what kind of form is it. This is done by firstly getting the headers and footers displayed, which is done by going to ‘View’ in the main menu bar and selecting ‘Page Header/Footer’. Once you have selected this, you will see the header and footer bars on the form area. Now the title and logo (if available) can be put into the header. This is done by selecting the button that has ‘|Aa’ on it. Then enter the label, which for this form is ‘Customers’, and then format it to the desired font and size (Facelift, 22) and you're done.
To complete the set of forms, only slight moderations are required. These are as follows:
FORM: Sales
- Object data source needs to be set to Sales (table).
- Header label needs to be ‘Sales’.
- Follow instructions from Customer Form as above for other info.
FORM: Full
- Object data source needs to be set to FullQuery (Query).
- Header label needs to be ‘Full Client List’.
- Follow instructions from Customer Form as above for other info.
FORM: Search
- Object data source needs to be set to SearchQuery (Query).
- Header label needs to be ‘Search Results’.
- Follow instructions from Customer Form as above for other info.
This should complete the main forms. The only other form the needs to be created is the Switchboard, which is done by selecting ‘Tools’ from the main menu bar of the database, then selecting ‘Database Utilities’ and then ‘Switchboard Manager’ where a Switchboard can be set up, allowing for a linkage between all functions of the database. Once set-up, the switchboard can be customised for Snowshock by simply editing the form Switchboard in design view.
Reports:
Very much like Forms, Reports are made in Design view by selecting the New Object button, and selecting Report, and choosing the object’s data source. The fields are then added, laid out respectively, and the report is formatted to a customised look for Snowshock. The main advantage over using a report than a form is that they are able to print out the report, whereas a form does not have the kind of layout suitable for a normal paper printout (A4, A3). So to start with, like a form, we need to get the data fields up. This is done by clicking ‘New Object’ and selecting Report. Then choose to open the new report in design view, and then (being that this report we will create is going to be the Full Report (based upon FullQuery)) select FullQuery as the object’s data source. Once the new form comes up, drag and drop all fields from the ‘FullQuery’ field dialogue (that will have come up), and position them appropriately onto the report ‘Detail’ area. Once all the fields have been added, the field names have to be positioned in the page header. This is so that the records are displayed columnar instead of each record being shown in one set. To do this, firstly click on ‘View’ in the main toolbar (File, Edit, View etc) then select Page Header/Footer. Once the page header and footer is displayed, the field name is selected and on the larger square (top left corner), right-click and select copy. Then, go to the page header and right-click and paste the field name into it, then go back to the field name that was copied (in ‘Detail’), select it, and press delete (key). There should now be a field name in the page header and the field should be in the ‘Detail’ area of the report. The fields and field names should now be position so that there is ample room for each record to be displayed, and that the field name, and field is aligned (so the field is correctly labelled in datasheet view). Next, in the report header a label needs to be added (‘|Aa’), and then enter the title as ‘Full Report’. Format the title so it is in font ‘Facelift’, and size ‘22’. Then a small caption box, again a title (but smaller), needs to be added. This is done by the same as above (‘|Aa’, then enter the title, and format to desired font). It needs to be labelled ‘(based on data from FullQuery)’, and formatted to font ‘Tahoma’ size ‘10’. After this, select all boxes from the page header (by dragging on open space, creating a box shape, and then going over all the objects in page header), and format them to ‘Verdana’ size ‘10’ in bold. Then select all fields in Detail, and format them to ‘Tahoma’ size ‘8’. Then add a line just underneath the field names. This is done by selecting the line tool from the ‘Toolbox’ menu, and clicking the line bar where you want it to begin, and dragging it to the desired size. Once this is done, make sure it is in line with the field names, and then right click on the line, and select Properties. Then select 'All', and go down until you see a border width tab, make sure it is set to 2pt. Then above, go to the border colour tab, and select the colour to ‘12632256’ (light grey). Next, under the page footer area, two text boxes need to be created. This is done by selecting the box labelled ‘ab|’. The first box on the right side of the page footer needs to contain the following code:
="Page " & [Page] & " of " & [Pages]
This will create a small code that will show which page of the total pages is being shown, e.g. in the format ‘Page 1 of 9’. The next box, the one on the left, needs to have to following code put into it:
=Now()
This will show a timestamp of the current time, which is updated as time progresses (real time). However, this code simply displays the data and time in short and basic format. This is changed by right clicking on the text box and select properties. Then go to the ‘All’ tab, and under the ‘Format’ tab select ‘Long Date’. This should now display the date as 28 July 2002. One small problem is that after adding two new text boxes, there are also two text box name boxes. As these are not needed, they can be removed by selecting the text box name, right clicking on the larger square and selecting cut. This should just leave the text box containing the code. Repeat for the other text box. Once this is done, align the text boxed to either side of the page, making sure that there is ample room for the date and page number to be displayed, and then format to ‘Arial’ size ‘9’ in bold.
The Search report is not much different from the above except for the following changes:
- Object data source is ‘SearchQuery’.
- Report title is ‘Search Report’.
- Report caption is ‘based on data from SearchQuery’.
Macros:
Most of the database relies solely upon functions that are based around design view and editing properties, therefore there are only a few macros required within the database. These are for pretty simple functions aswell.
The first of which is a macro called ‘EditClientList’ and is simply used to open up a Query in datasheet mode, which allows it to be edited quickly. This macro will be used in the switchboard and will be assigned to a button. In design view, this macro has only one Action, which is ‘OpenQuery’. However, it does have a few set Action Arguments which are as follows:
ACTION ARGUMENTS FOR ‘EditClientList’ :
Query Name: FullQuery
View: Datasheet
Data Mode: Edit
This allows ‘FullQuery’ – the query which contains all database information, to be opened up in datasheet view (so that all records are displayed), and is freely editable to the user.
The second macro is called ‘EditSearchQuery’ and is used to open up a Query, but this time in design view, which allows its actual structure to be edited. Again, this macro is used in the switchboard and will be assigned to a button. Like ‘EditClientList’, it only has one Action, which is ‘OpenQuery’. And like ‘EditClientList’, it has a few set Action Arguments which are as follows:
ACTION ARGUMENTS FOR ‘EditSearchQuery’ :
Query Name: SearchQuery
View: Design
Data Mode: Edit
This allows ‘SearchQuery’ – the query that contains the customisable fields from both tables, to be opened up in design view (so its structure is displayed), and is freely editable to the user.
Test Plan
For the test plan, all sections of the database need to be tested. So they will be separated and tested independently and linked as more testing continues. A series of questions must be asked from each section to test the sections for accuracy as follows:
Tables:
Firstly, the tables needed to have the correct set of data fields. This is shown in the table structures. Then they must be checked for data checking, as in do they pop-up an alert if required fields are not entered? Do they auto-format things like date, time, and currency? This can be checked by entering a new record, and seeing the way the data is auto-formatted. Are the required fields set-up correctly, and do they have any mismatches, e.g. are any of the fields set to required yet allow for a null-value to be entered? This is done by checking which fields have required set to yes, and then entering a null-value into the fields on a test record. If the table allows a null-value to be entered and it states that it is a required field, then the allow null-value option needs to be set to no.
Queries:
The queries need to be checked for the correct fields. Do the fields shown match what needs to be displayed. This can be checked by looking at the Query Structures section of this documentation. Do the fields show the correct sorting (ascending and descending). Again, this is shown in the Query Structure section Are the correct fields sorted? Are the fields in the correct order (are they laid out how you want them to be)? Are all required fields shown? Are the relationships set-up, and in the correct way?
Forms:
Are the forms laid out correctly, does it match the criteria selected. This needs to be checked with the data source. Does the layout of the forms make it easy to see and use the forms within the database? Are they sensible? Is the title of the form correct? Can data be entered into the forms correctly? Do the fields and field names match, and are they aligned correctly? Do they match the requirements of the end user?
Macros:
Macros simply need to be tested to see if they do what their structure stated and what needs to be done. Do they link to what they were set to do?
Reports:
Do the reports fit neatly onto A4 paper? Do all the fields fit onto paper? Like the forms, do the fields and field names match are aligned efficiently? Does the formatting match that of the stated design, are the fonts correct and to the right size? Are the report labels and titles correct? Do the codes placed at the bottom of the page (page footer) show what they are supposed to and in the correct format? Are the data sources for the reports the correct ones, and do they show all fields?
Implementation:
Tables:
Design view of the tables, as seen below shows how the data has been laid out in the datasheet view, with each field having different individual properties.
As you can see from the above screenshot, the structures mentioned in the design section match that shown above. Not all Field Properties can be shown for each field, but as seen above the name field is correct.
Now for the datasheet view…
This is the datasheet mode of the Customers table showing all the records, as seen, there are duplicate names, addresses, phone numbers and e-mail address entries, as allowed in the field properties, but no duplicate reference numbers. This will be explained in more detail in the test plan.
Queries:
This is that design view of the FullQuery query, it shows the data source tables at the top, with the relationships tab linked and the selected fields in order below.
As seen in the design section of this document, the fields supposedly displayed in the FullQuery are shown, with the correct sorting shown also. The Join Properties dialogue is not usually seen in the design view, unless the joining link between the related fields (ReferenceNumber.Sales & ReferenceNumber.Customers) is double clicked, but it is included in this screenshot to show just how the link is customised, with the 3rd option being selected (as stated in the query structures). This again is explained a little more in the test plan where the relationships are tested more accurately. All other information shown in the design section can be seen here, including the shown fields and the arrangement of the Field’s (the order) which is also in design and analysis section.
Next is the datasheet mode of the Query, similar to that of the Table datasheet mode but more specific due to the customisation shown for the queries.
Here you can see that the shown fields in the design view have been displayed, with all records displayed.
Forms:
Main Forms:
Forms are more complicated than the queries of course due to the high demands for presentation and design structure. To start with, here is the design view for the Full form, which displays all records within the database.
Above is the design view of the form ‘Full’. The FullQuery data source is shows to the bottom right which is what fields went into the form, and all went in as seen. As shown, the ‘New Object’ button is how the data source was applied, after Form was selected. Formatting is shown, with the main title as Facelift, 22 and the fields and field names as MS Sans, 8.
Next is the datasheet view, which is very similar to the above, except it shows all the records based upon the data sources selected.
Most of the explanation to this has already been done, except for the slight shortening of the field names. This is done to allow for a neater presentation to the forms. All records here are based upon data found in FullQuery.
Switchboard:
The switchboard is a lot more complicated than the other forms in that it is automatically created, and therefore has little design to do, except for the custom layout that Snowshock specified that make it presentable and stylish, but offers a host of possibilities that conventional forms take a long time to do. As seen below is the design view of the form, but none of the options are shown, so it is simply a mask of the presentation and design for Snowshock.
As seen above, a small logo has been added (Insert, then Picture) and resized to fit neatly onto the report layout. Then a title (again, Facelift, 22) and more simple resizing changes (such as the report area has been shortened to make it more central aligned). Also the text boxes have been formatted to lime green for a cool and easy to read design. The switchboard items dialogue should be discarded and left alone as changes in this may cause errors.
Here is the datasheet view of the switchboard:
As you can see, the switchboard items have been placed onto the switchboard, these are data objects that have been based upon the table ‘Switchboard Items’. The formatting from design view leaves the switchboard looking simple and presentable, with easy to read and understandable options. This is a main requirement that has been completed from the end-user requirements at the beginning of this documentation. Also the buttons are large enough to be easily clicked, minimising accessibility problems.
Switchboard Options:
The switchboard itself is created as a new form by using the ‘Switchboard Manager’ option (Tools, then Database Utilities). It is from here that the setup and controls of the switchboard can be accessed, allowing for new options to be placed onto the switchboard. The switchboard already hosts a satisfactory amount of options available to the user, and if the user requires more complex options on the switchboard, then macros can be created to do user-specific requirements. Below is a screenshot of an option on the switchboard.
As seen above, the Switchboard Manager has been opened, and the switchboard named ‘GCSE Beanie Project’ has been opened in edit, which opens up the ‘Edit Switchboard Page’. As seen on the switchboard, there is already a fair amount of items which have been customised for their particular uses, but ‘Add record: CUSTOMERS’ has been opened in edit to show what options the switchboard has to offer. Now the ‘Add record: CUSTOMERS’ option is set to ‘open form in design view’, but as seen there are many other options available to the user.
Macros:
Now although the switchboard has offered most of the options for the database, there are a few that needed to be added, that although simple, incorporated editing into the switchboard instead of simple navigation. The macros were shown mainly in the design section (of this documentation), but below are the screenshots from the design view of the macros (as there are no other views, apart from the actual view of the code/script of the macro):
As you can see, there are a host of options available to the user when creating a macro for a database. Of course, if the user requires more than that available, then the importation of a custom, code-built macro is possible, but writing code for database is for the advanced user. The macro screenshot shown above is that of the ‘EditClientList’ macro and is used to open up ‘FullQuery’ in design view, which is setup in Action Arguments (the lower section of the screenshot). This screenshot also matches the design structure stated earlier.
Reports:
Reports are similar to that of forms, except as seen in the screenshot below, they have a lot more printer-friendly options and presentation that makes it more easier to keep track of data when filing databases (outside of a computer system). Shown below is the design view of the ‘Full’ report, and how it is laid out according to the design of the reports.
Firstly, the title of the report has been correctly formatted to ‘Facelift, 22’, as has the caption underneath to ‘Tahoma, 10’. The fields and field names have been aligned correctly and the correct fields are displayed, as the data sources are shown in the ‘FullQuery’ dialogue (which is showing all fields from ‘FullQuery’). Next, the field names have been placed correctly in order into the page header. This now allows the fields to show the data in a columnar way, so that as many as possible records can be displayed on paper. The fields and field names have been correctly formatted (‘Tahoma, 8’ and ‘Verdana, 10’ respectively), and are correctly aligned. Also, the page footer shows two text boxes that contain the correct data stated earlier to have the date and the page number shown. The text box properties for the left text box are shown in the screenshot to show that the long date format has been selected.
Finally here is the print preview of the report, presented neatly and ready to print:
As you can see, the report is nicely aligned and ready to print in a clear and simple layout.
Test Plan Results:
After having completed the test plan, there were a few simple errors that needed to be addressed not only in accordance to the end-user requirements but also a few minor adjustments needed to be made.
Tables:
There was one design problem:
- The field ‘E-mail’ was set to ‘Allow Zero Length: No’ when it should have been set to ‘Yes’, as although it did not have ‘Required’ set to ‘Yes’, it still needed to be allowed a zero-length entry. This was resolved by setting to ‘Allow Zero Length: Yes’.
Other than that, all tables were up to the specifications as stated in the test plan.
Queries:
There were two design problems:
- The query ‘SearchQuery’ had the field Reference Number linking to the table ‘Customers’ instead of the table ‘Sales’. This was changed and the problem resolved, however it caused a major data change, and affected both the forms and reports.
- The fields ‘Product’ and ‘Price’ were in the wrong order, and needed switching around. This required the field and table to be switched, which was done, but again affected both the forms and reports.
Then, the relationships and validation settings were tested to see if they actually work. Here are the results:
- When data was entered into the customers form, and a new reference number was entered, which was a duplicate of a record within that table, an error message came up, stating that duplicates were not allowed, and the field data needed changing.
- When a new reference number was created for a new customer, and the same customer made a transaction (logged), but a different reference number was entered, the customer information was discarded and not shown on the full list, but the transaction was shown as a separate entry, but without customer information.
This completes the test plan results for the queries.
Forms:
There were two design problems:
- Both the ‘Customers’ and ‘Sales’ forms did not have a label put onto the page header of the forms. The correct labelling ‘Customer Details’ and ‘Sales’, respectively, were added and formatted to ‘Facelift, 22’.
After this problem being addressed, there were no more design problems for the forms and all requirements were matched from the test plan.
Macros:
There were no problems with the macros. Both macros did what was required of them without error or undesired effects.
Reports:
There was one design problem with the reports:
- The phone and e-mail field names did not have big enough text boxes to accommodate all characters within the text box, so they had to be enlarged. This was a minor problem that did not affect any other sections of the database and was corrected easily.
This completes the test plan results for the reports.
Evaluation:
System Evaluation/Requirements Evaluation:
Hardware:
The database was saved, taken off its prototype system (where it was built and designed), and tested on a two different systems. Both of which performed equally efficiently, the slower machine (which matched that of minimum requirements) ran just as well as the faster machine, except for slight pauses when a complicated design process was initiated (like that of the reports when it is opened in design view). Other than that the hardware requirements were matched.
Software/Database:
The database was then opened on the optimum machine for efficiency and database testing (which would show errors faster), and tested for its navigation and ease. Firstly a random data record was taken from the Snowshock filing cabinet, the details of both the customer and sales were on there. The database was opened, then the switchboard opened. The user clicked on ‘Add Record: CUSTOMERS’ and entered the details of the customer, closed it and went back to the switchboard menu where the user entered data into the ‘Add Record: SALES’. This was completed and the user went back to check to see if the data entered had appeared in the full list, which it had. So data entry proved successful.
Next, the search was tested, and from clicking the ‘Setup Search’ menu, the SearchQuery query was opened in design view. The user entered into the ‘Date’ field, the criteria to search for as ‘*2000*’. This was automatically changed to ‘Like “*2000*”’. The user then clicked close on the SearchQuery, where the user was asked to save the Query, of which the user did. Next, the user went back to the switchboard menu and clicked on ‘View Search Criteria’, which opened and displayed all records with the date set as 2000. This proved successful and the user was happy with the database setup.
End User Comments:
Snowshock Beanies after vigorous testing made the following comments:
- They found that the database was easy to use and found it simple to enter information into the database, and even if errors occurred, could easily be corrected.
- They found the layout and design was simple and readable, but professional enough to show that the database was customised for their purposes.
- They found that the user need not save the database before quitting, although will remind all users to do so anyway, but this limits users from losing data from the database.
- It matches the requirements of Snowshock Beanies.
Possible Improvements:
There were a few things found at the end-user stages of the design which could easily be changed, but were not sufficient enough to adjust the database in a way which would ruin the ease of use for the user. These were as follows:
- The switchboard, for ease, would be better if it could be loaded upon start-up of the database. This was done in a simple and effective way, as follows:
‘Tools’ was selected, then ‘Startup…’, next a new dialogue came up as follows:
The ‘Display Form/Page’ was then set to ‘Switchboard’.
The database was then closed and re-opened, where the switchboard opened immediately. This made it even easier for the less computer-literate user to use.
And finally for security of the database, a password would make the database secure if it was stolen over the internet or e-mail. This is set-up in the following way:
Firstly, the database needs to be closed, and opened (‘File’, ‘Open’), but as it is being selected (from the ‘Open File..’ dialogue) it is selected and next to the open button, there is a small down arrow, selecting ‘Open Exclusive’ the database can be opened.
Then select ‘Tools’, and then ‘Security..’ and then ‘Set Database Password…’.
The database password can now be set and once the database has been closed and re-opened, before the database can even be viewed, the password has to be entered, as seen below: