The data which is inputted to my database will consist in various forms which may need to be handled specifically and outputted in a user formatted way. Access reports allow you to specify exactly what data you want to be shown and in what form and layout. This means that the owner would be able to produce pages of information from the data on the database. Excel also has report capabilities although because of the way a spread sheet works, handling records and specific rows would be much more difficult.
Access also supports the switchboard feature where by an interface can be developed for users to browse and access the database itself. This switchboard supports the inputting of data of all kinds, which means that it can be defined for specific uses such as handling customers.
Access has the ability for users to develop queries to get the data they want from the database. A query is a method of filtering data you don’t require to leave you with the data you want. Advanced queries can be developed scanning various fields and records. These queries could also be made through a user-friendly interface. A description feature could also be implemented to allow items to be found or browsed.
What do I want my final product to look like?
I plan to develop my solution around Access’s switchboard feature to allow a simple user-friendly interface to be used to access and modify the database.
Switchboard map
The switchboard menu provides a user-friendly menu for the employees to access the database, ensuring it is done correctly and no fields are missed out. A switchboard interface will prevent parts of the databases data from becoming unreliable and invalid.
Standard switchboard menu pages specifications
The following information is a set standard I plan to implement on every page on the switchboard.
Template interface where a standard layout is established. The title should be on the top, links to the left etc… This is to make browsing the menu simple and less demanding and to prevent the user from looking for the same links on a different page.
- Title and sub-title where the sub-title represents the specific page (i.e. Main menu, add item)
- Back button link, linking back to the previous page, which should be the main switchboard menu.
If time permits I may consider adding graphics to the switchboard to make it appear more professional although this is not a requirement.
Method of data input
The database at the store will be updated through a easy to use user interface by the employees working. The data needs to be updated via a ‘form’ method to ensure all fields and data is collected.
All data will be inputted via a form layout on the switchboard and updated to the database automatically.
Step one: Creating the database
The database is to be designed using Microsoft Access due to the reasons listed further up in this document.
To create a database you simply start up Microsoft Access and press Ctrl+N. From the control panel select ‘create table by entering data’ and the database will load for you to begin editing.
The database we are using will use related tables which will work along side one another using data from each table.
There are three tables that are to be used including account, user and item.
Account
The account table will contain data relating to all sales and returns.
Field names will include account_id, account_item, account_paid, account_profit, account_quantitiy purchased and user_id.
User
The user table will contain data relating to the customers having purchased products from the store. Such information is important to the store as a means of recording vital data which may be important to the store and customer relating to the items in both’s pocessions which are often extremely rare and valuable. Ofcourse this data will only be used at the users discretion although it is often in there best interests to be used in matters relating to there items.
Field names will include user_id, user_name_first, user_number, user_address_street, user_name_last, user_address_city, user_address_town, user_address_postcode1, user_address_postcode2 and user_name last.
Item
The item table will contain data relating to the various items that come into the stores pocession. This data is important to the store as a method of tracking all items that the store has taken in over the years. However with items that may have come into the stores pocession on numerous ocassions, the item may have been inputted as a new separate item even though the same object is found elsewhere in the table. This shouldn’t be a problem becouse as items are sold, the quantity of that specific item falls and some items will have a quantitiy of “0” and may optionally be filtered when searched so these items don’t show.
There will also be a option for controllling the data on your tables so the store owner or worked may feel its appropriate to remove certain items from the tables. These options will be found on the switchboard.
The fields used in this table will be item_id, item_title, item_description, item_history, item_quantitiy, item_data_range, item_#_in_set, item_value, item_price obtained and item_condition_notes.
Column Renaming
Table 1: Item table
The first table I will be creating will be the ‘Item’ table. This will be a table of all items available for sale at the store at any given time.
We will begin creating the layout we want be formatting the database the way that we want it to be. Renaming the columns will make understading the database in design (and various other) view much easier. The following list is the columns we want to rename in order corresponding from left to right on the database as the list goes down.
Further information on the field formtating can be found be pressing F1 in table design view.
The switchboard
Navigation must be simple and allow the user to move around the switchboard at ease. Every additional page must have a “return” button to allow the user to return to the previous page.
Testing
Any mistakes I have made shouldn’t be too hard to track down and correct after testing. All database files and formulae will be accessible to be edited (even upon completion to the store owner) to update, add, remove or modify the database and what it is doing.
Because my coursework is to be created completely within Microsoft Access, the range of potential shouldn’t be too large and unmanageable.
Things that need testing
Queries
My queries will be tested as they are implemented, as well as in a short testing period after completing my solution. I will enter a series of
Forms
The data that is added/updated/removed to my database is always inserted via a ‘form’ based interface on the switchboard. This needs to be tested to ensure specific data is going into specific cells and the data generated is valid.
If I do need to find a error within my work I believe that it is easiest to be done by testing my formulae and work as I go along and adjusting my variables according to the results I receive and the results I want.
After finishing my database solution and switchboard, running it through a test version with ‘made up’ data will immediately track down any obvious mistakes or errors I may have overlooked. Using this method along side a pen, paper and calculator will allow me to compare and contrast the data displayed with the data I want/expect.
The website
Making pages for your system is not always a neccessity although it may help improve awareness of your store or product on the web and act as another form of contact. Features such as “searching” may be implemented on webpages for other users to access.
The design of a webpage is down to the users discretion and what visual product he hopes to achieve. Below I will detail methods of implementing a search system into your webpage to allow users to search fields and cells on your database to find the information they requre.
I plan to allow those on our page to search the item database for items they may be interested in purchasing. I don’t plan on allowing the item to be purchased from the store site due to the nature of how a antique store works but I will cover the basics on how you could implement such a system here.
- The first step involves implementing a basic page or site. This can be done in basic editors if you lack the coding knowledge to do this yourself. You should approach this step with a plan of what fields you will want the sure to search and the formulae you may use.
Example of a simple search form.
This form field would be placed at any position on your page and direct towards the database you want.
I plan to allow those on our page to search the item database for items they may be interested in purchasing. I don’t plan on allowing the item to be purchased from the store site due to the nature of how a antique store works but I will cover the basics on how you could implement such a system here.
Results page
A results page will be generated from the parametor’s set by the user of the search system displaying results appropriate to what they are searching.
