Queries: - Queries can be used in Access to extract useful information from the database. There are various types of queries that can be used. Select queries extract data from tables based on specified values, find duplicate queries display records with duplicate values for one or more of the specified fields, and find unmatched queries display records from one table that do not have corresponding values in a second table.
Reports: - Reports are a useful way of presenting useful information extracted by queries. They provide a way of organising the results produced by a query and sending it to a printer to produce a hard copy of the information.
Menu Creation: - The creation of menus in Access gives the user an intuitive and user-friendly method for navigating and interacting with the system. A main menu can be produced, in which sub-menus can be accessed such as a Tables menu and queries menu. From these sub-menus, there can be buttons representing the individual tables and queries themselves respectfully. Thus, everything within the database can be accessed via the main menu, or the switchboard of the database.
Integration of VBA (Visual Basic for Applications) code: - Visual Basic is an event driven programming language and integrated development environment from the Microsoft Corporation developed for its COM (Component Object Model) programming model. Pieces of code can be designed that increase the functionality and flexibility of the standard user interface, customised appropriately to the system’s unique requirements. For example, pieces of code can be designed that automatically capitalise the first letter of the character string entered into a field. This would be useful for fields such as “Forename” and “Surname”, where the conventions and grammatical rules of the English language require such capitalisation. Code can also be created to compliment reports.
Advantages of Microsoft Access
I shall now discuss the advantages of Access in comparison to the other packages considered. Obviously, all of the aforementioned features of Access are all advantages, so I will be discussing a few general advantages. A major advantage for its suitability for this project is its availability. This package is on both the school computers and my home computer so it is a convenient choice for me. One other advantage of Access is that it avoids data redundancy due to the relationships between the entities. Data in the system does not have to be needlessly repeated, and this has the knock-on effect of improving data consistency as different versions of the same data are not held elsewhere in the database. Another advantage is that because data is held centrally, it can be readily used by all departments within an organisation as opposed to belonging to just one section of the company. The system will also be more secure, due to the centralised access to data. If data is spread around the system, there will be more points of access to such data and thus more potential ‘leaks’.
Data can also be stored independently to any application that may use it. Thus structural modification to the database will not affect any programs that access the data. One benefit of Access from a programmer’s perspective is its compatibility with SQL (Structured Query Language). Queries can be viewed and edited as SQL statements, and SQL statements can be used directly in Macros and VBA Modules to manipulate Access tables. Users may mix and using both VBA and "Macros" for programming forms and logic and offers object-oriented possibilities.
Disadvantages of Microsoft Access
Because the database can be locally copied, hackers can discover any passwords used to protect the system. Scalability can also be a problem with Microsoft Access. Whilst it is perfectly suitable for a small business, when the businesses grows in size and accumulates more and more masses of data, Access’s performance can decrease. It is not ideal for handling very large volumes of data. If Fred’s Car Rentals were to scale out and open new branches of their shop, Microsoft Access would not be up to the job of handling the large amounts of data that need processing. In this case, it would be probably best if the organisation hired a team to develop a tailor-made bespoke package for the purpose.
Hardware and Software Specification
The specification of the school computers are:
- 1Ghz CPU
- 256Mb of RAM
- 1GB of allocated hard disk space
- CD-ROM drive
- Keyboard and mouse
- Windows XP Operating System
The specification of my home computer is:
- 3.06Ghz CPU
- 504 Mb of RAM
- 20GB of free hard disk space
- CD-ROM drive
- Keyboard and mouse
- Windows XP Operating System
I will use a removable storage device with a capacity of 512Mb to transfer my work between both computers, and will store a copy of the most recent version of the database on the school computers, home computers and memory stick at all times.
The system requirements of Microsoft Access are:
-
Computer with a 133 megahertz (MHz) or higher processor; Pentium III recommended.
- 64MB of RAM (128MB recommended) for the operating system, plus an additional 8MB of RAM for each program running simultaneously.
- 245MB of available hard-disk space with 115MB on the hard disk where the operating system is installed (hard-disk usage varies depending on configuration; custom installation choices may require more or less hard-disk space).
- CD-ROM drive
- Super VGA (800*600) or a higher-resolution monitor with 256 colours.
- Microsoft Windows 2000 with Service Pack 3 (SP3), Microsoft Windows XP, or a later operating system.
- Microsoft mouse, Microsoft IntelliMouse, or a compatible pointing device.
Normalisation
Normalisation refers to the process by which invalid data models are converted into valid data models, ensuring that the data within the model is consistent and of integrity. This process breaks down entities to an atomic structure, in which they cannot be broken down any more, and gets rid of repeating attributes within the data model.
Un-normalised form
Customer: Customer ID, Forename, Surname, Street Address, Town, County, Nationality, Sex, Postcode, Telephone Number, Mobile Number, E-mail Address, Date of Birth, Date of Membership, Staff ID, Forename, Surname, Street Address, Town, County, Postcode, Telephone Number, Mobile Number, E-mail Address, Date of Birth, Working Hours, Job title, Job description, Employment began, Employment terminated, Vehicle ID, Model, Manufacturers Code, Registration Number, Current Mileage, Daily Rental Price, Class, Colour, Availability, Manufacturer Code, Manufacturer Name, Name of Contact, Contact Telephone Number, Contact Mobile Number, Contact E-mail Address, Manufacturer Description, Rental ID, Customer ID, Vehicle ID, Model, Daily Rental Price, Date Rented Out, Number of Days, Rental ID, Customer ID, Vehicle ID, Vehicle Name, Return ID, Date Returned, Daily Rental Rate, Overdue Fee Charged.
1NF
For data to change from being un-normalised to being in first normal form, I must ensure that there are no repeating data item groups. From viewing the above attributes in their un-normalised form, I can see that a customer can make many rentals and there are many customers that can rent. This means that these are repeated groups and need to be moved to their own entity. I can also see that a vehicle can be rented many times, and there are many vehicles available. This means that Rental ID and Vehicle ID are also repeating groups, and again must be moved to their own entity. Also, a rental can be recorded many times by a member of staff and there are many members of staff in the shop. Therefore, Rental ID and Staff ID are also repeating groups and should be moved to their own entity.
Customer: Customer ID, Forename, Surname, Street Address, Town, County, Nationality, Sex, Postcode, Telephone Number, Mobile Number, E-mail Address, Date of Birth, Age, Date of Membership.
Customer_Rental: Customer ID, Rental ID, Vehicle ID, Date Rented Out, Number of Days, Return ID, Date Returned, Staff ID, Forename, Surname, Street Address, Town, County, Postcode, Telephone Number, Mobile Number, E-mail Address, Date of Birth, Working Hours, Job title, Job description, Employment began, Employment terminated.
Rental_Vehicle: Rental ID, Vehicle ID, Model, Registration Number, Current Mileage, Class, Colour, Availability, Manufacturer Code, Manufacturer Name, Name of Contact, Contact Telephone Number, Contact Mobile Number, Contact E-mail Address, Manufacturer Description, Daily Rental Price.
Rental_Staff: Rental ID, Staff ID, Forename, Surname, Street Address, Town, County, Postcode, Telephone Number, Mobile Number, E-mail Address, Date of Birth, Working Hours, Job title, Job description, Employment began, Employment terminated.
Rental_Return: Rental ID, Return ID, Date Returned.
2NF
In second normal form, entities containing a composite key are examined to see if the attributes in the entity depend on both the keys. If an attribute depends on just one of the keys, then it should be removed with that key into a new entity. All of the non-composite keys in Customer_Rental are dependant on the Rental ID but not Customer ID. Therefore I created a rental entity with these attributes. All of the non-composite key attributes in the Rental_Vehicle entity are dependant on Vehicle ID but not Rental ID. Therefore, a vehicle entity is created with these attributes and that key. All of the non-composite key attributes in the Rental_Staff entity are dependant on Staff ID but not Rental ID. Therefore, a staff entity is created with these attributes and that key. The non-composite key attribute in the Rental_Return entity is dependant on Return ID but not Rental ID, so a Rental entity is created.
Customer: Customer ID, Forename, Surname, Street Address, Town, County, Nationality, Sex, Postcode, Telephone Number, Mobile Number, E-mail Address, Date of Birth, Age, Date of Membership.
Rental: Rental ID, Customer ID Vehicle ID, Staff ID, Date Rented Out, Number of Days.
Vehicle: Vehicle ID, Model, Manufacturer’s Code, Registration Number, Current Mileage, Class, Colour, Availability, Manufacturer Code, Manufacturer Name, Name of Contact, Contact Telephone Number, Contact Mobile Number, Contact E-mail Address, Manufacturer Description, Daily Rental Price.
Staff: Staff ID, Forename, Surname, Street Address, Town, County, Postcode, Telephone Number, Mobile Number, E-mail Address, Date of Birth, Working Hours, Job title, Job description, Employment began, Employment terminated.
Return: Return ID, Rental ID, Date Returned.
3NF
Now the attributes in the entities much be checked to see whether any of the attributes are mutually dependant, and if they are, they must be moved to another entity. Manufacturer Name, Name of Contact, Contact Telephone Number, Contact Mobile Number, Contact E-mail Address and Manufacturer description are all dependant on Manufacturer code, so a manufacturer entity is created.
Customer: Customer ID, Forename, Surname, Street Address, Town, County, Nationality, Sex, Postcode, Telephone Number, Mobile Number, E-mail Address, Date of Birth, Date of Membership.
Rental: Rental ID, Vehicle ID, Staff ID, Customer ID, Date Rented Out, Number of Days.
Vehicle: Vehicle ID, Model, Manufacturer’s Code, Registration Number, Current Mileage, Class, Colour, Availability.
Manufacturer: Manufacturer Code, Manufacturer Name, Name of Contact, Contact Telephone Number, Contact Mobile Number, Contact E-mail Address, Manufacturer Description.
Staff: Staff ID, Forename, Surname, Street Address, Town, County, Postcode, Telephone Number, Mobile Number, E-mail Address, Date of Birth, Working Hours, Job title, Job description, Employment began, Employment terminated.
Return: Return ID, Rental ID, Date Returned.
Entity Design
Customer Table
The customer table’s purpose is to store details on all of the shop’s customers. This enables customer details to be viewed when required; for instance, in the event that a customer with an overdue item needs to be informed of its lateness, the user can consult the database, locate that record and note the customer’s telephone number to phone them up and inform them. The data contained on customer’s records can also be further manipulated automatically to produce other values that are needed by the business. For instance, the age of a member can be calculated by feeding the “Date of Birth” value held in the Customer table into a formula (which is outlined later in the design). Note that a duplicate table called tblArchiveCustomer will also be created, in order to archive old customer records to
Staff Table
The staff table’s purpose is to store details on all of the shop’s staff. This enables staff details to be viewed when required; for instance, in the event that a staff member has been absent for a period of time and the business has not been informed why, the user can consult the database, locate that record and note the staff member’s telephone number to phone them up and inform them. The data contained on a staff member’s records can also be further manipulated automatically to produce other values that are needed by the business. For instance, the age of a staff member can be calculated by feeding the “Date of Birth” value held in the Staff table into a formula (which is outlined later in the design).
Note that a duplicate table called tblArchiveStaff will also be created, in order to archive old staff records to it.
Vehicle Details
The Vehicle table’s purpose is to store details on all of the vehicles owned by the business for rental. This enables vehicle details to be viewed when required; for instance, in the event that a vehicle has not been returned and all attempts to contact the customer have failed, the police will need to be informed of the vehicle’s details so they can track it down. The user can consult the database, locate that record, record the registration number, colour and other relevant details of the vehicle in question and feed this information to the police.
Manufacturer Details
The Manufacturer table’s purpose is to store details on all of the manufacturers of cars that the business have bought in the past or intend on buying from in the future. This enables manufacturer details to be viewed when required; for instance, in the event that a vehicle has broken down and needs a new part directly from the manufacturer, the user can consult the database, locate that manufacturer, record the contact details and arrange for that part to be delivered.
Rental Table
The Rental table’s purpose is to store details of all the current rentals. The data contained in these records can then be further manipulated automatically to produce other useful values that are needed by the business. For instance, the date that a vehicle is due back can be calculated by adding the number of days the item is to be rented to the date the item is rented out.
Note that a duplicate table called tblArchiveRentals will also be created, in order to archive old rental records to it.
Returns Table
The Rental table’s purpose is to record all the details of a vehicles return and calculate and update some crucial values in other tables on the basis of these details. For instance, from recording the vehicles mileage on return, the total number of miles travelled during the vehicles last rental can be calculated by subtracting this value by the mileage logged on the vehicle before its last rental. This table also enables the calculation of the total rental price that needs to be paid by the customer returning the vehicle on the basis of the amount of days overdue the vehicle was, the daily rental price of the rented vehicle, the number of days the vehicle was validly rented for and the number of miles travelled by the customer. This calculation will be explained in more details in the Form Design section.
Note that a duplicate table called tblArchiveReturns will also be created, in order to archive old returns records to it.
Relationship Design
Legend:
I shall now discuss why the relationships are designed in this way and what this means, starting with the Rental entity:
- One customer can only make one rental at time since there is only one of each vehicle and the same person will not be able to rent more than one car for themselves, for security reasons. This is a one-to-one relationship; one customer makes one rental at a time, and one rental record is associated with that one user during this time.
- Each rental can be returned once, and then it is archived and deleted from the current rental file.
- One vehicle can be rented many times, although each rental record consists of one vehicle, as only one vehicle is recorded in one rental.
- One staff member can record many rentals, but each rental recorded is made by one particular staff member.
- One manufacturer supplies many vehicles, but each vehicle is supplied by one manufacturer.
Design of Data Input Forms
Form Design Principles (For all forms):
An image will be inserted into the top-right hand corner of each form to enhance its aesthetic appeal, the background will be a light shade of blue and the font used will be plain old Times New Roman with a font size of 12. A light shade of blue will be used as it is reputed to have a calming, relaxing effect, and amazingly, if we are to delve into the field of colour psychology for a moment, blue is actually often used to decorate offices because there exists a significant body of research showing that people are more productive in blue rooms. If one were to extend this logic to accommodate form design, then perhaps one could aspire that blue form could potentially increase the work rates of employees behind the screens, but failing that, I’m sure it would certainly not harm it!
Size 12 Times New Roman is the font that will be used because it is the font that the majority of users are used to seeing displayed on computer screens. This familiarity should lessen the difficulty with which the user adapts and/or apprehension that the user may initially experience over the implementation of a new system. I believe that exotic fonts and artful backgrounds are superfluous at best and just downright gaudy and distracting at worst. The layout of all of the forms will be consistent throughout, with all similar functions located in the same position on each form. This consistency and uniformity in form design will speed up the length of time it takes for the users to learn how to use and navigate the system effectively and efficiently.
Each form has pieces of code attached to them, although that aspect shall be detailed in the Code Design section.
I shall now present the forms that I intend to design, along with the details and functionality I wish to incorporate into them.
The customer form will be used every time a member of staff wishes to enter a new customer record into the system when new customer is in the process of joining. A Date and Time formula will be displayed on the top-left hand corner of the form for the convenience of the user.
A combo box will be used to limit the user’s responses to “Male” or “Female” in the Sex field. Taking away the freedom of the user to enter what they will, by restricting the response to the only two valid ones, means less data entry errors will be made. It also speeds up the data entry process. A formula (outlined above) will be used to calculate the customer’s age on the form. There will also be combo box from which, upon being clicked, a list of the customer Surnames are displayed. The user can select one of these Surnames to display that particular customer’s record onscreen. There will also be a “Find Next” button beside the combo box that will be used in the event of customers with the same surnames.
Next record, Previous Record, Add record, delete record, save record, archive record and Close buttons will be placed on the form, all with appropriate macros attached to perform these particular commands. A sub-form is included in this form that displays the current rental of that particular customer. This enables the user to check at a glance the details of this said rental, without having to take the time to exit the form and go to the rental form.
Code will be incorporated into particular fields in this form, but I shall outline these further on in the code design section of my Design.
The customer form will be used every time the manager employs a new member of staff, and the required details must be entered into the system. A Date and Time formula will be displayed on the top-left hand corner of the form for the convenience of the user.
A combo box will be used to limit the user’s responses to “Male” or “Female” in the Sex field. A combo box will also be used to limit the user’s choices in the Working Hours field to “Part-Time” and “Full-Time”. Taking away the freedom of the user to enter what they will, by restricting the response to the only two valid ones, means less data entry errors will be made. It also speeds up the data entry process. There will also be combo box from which, upon being clicked, a list of the staff Surnames are displayed. The user can select one of these Surnames to display that particular staff member’s record onscreen. There will also be a “Find Next” button beside the combo box that will be used in the event of staff members having the same surnames.
Next record, Previous Record, Add record, delete record, save record, archive record and Close buttons will be placed on the form, all with appropriate macros attached to perform these particular commands.
Code will be incorporated into particular fields in this form, but I shall outline these further on in the code design section of my Design.
The vehicle form will be used every time the vehicle manager purchases a new vehicle for the store, so that the vehicle’s details may be entered into the system. A Date and Time formula will be displayed on the top-left hand corner of the form for the convenience of the user. An option box will be incorporated into the form to display whether or not a given vehicle is currently available or not. It will be linked to the Rental and Return tables, in such a way that when the particular vehicle ID is detected in the Rentals table, “No” is selected on the option box, but when that same vehicle is returned, the presence of its vehicle ID will be detected in the returns table, causing the option box to display “Yes”.
Next record, Previous Record, Add record, delete record, save record, archive record and Close buttons will be placed on the form, all with appropriate macros attached to perform these particular commands.
Code will be incorporated into particular fields in this form, but I shall outline these further on in the code design section of my Design.
The manufacturer form will be used every time the vehicle manager wants to but a vehicle from a new manufacturer that is not on the database, so that the manufacturer’s details may be entered into the system. A Date and Time formula will be displayed on the top-left hand corner of the form for the convenience of the user.
Next record, Previous Record, Add record, delete record, save record, archive record and Close buttons will be placed on the form, all with appropriate macros attached to perform these particular commands. A sub-form will be incorporated into the form, listing all of the vehicles owned by the store that are used to rent out to clients.
Code will be incorporated into particular fields in this form, but I shall outline these further on in the code design section of my Design.
The rental form will be used every time a customer makes a rental, so that the staff can enter and record the details of the rental on file. A Date and Time formula will be displayed on the top-left hand corner of the form for the convenience of the user.
Next record, Previous Record, Add record, delete record, save record, archive record and Close buttons will be placed on the form, all with appropriate macros attached to perform these particular commands. There will be two formulae used in this form. One formula is attached to the Date Due Back field and it calculates the date the rental needs to be returned from the Date of Rental and Number of Days fields.
Code will be incorporated into particular fields in this form, but I shall outline these further on in the code design section of my Design.
The returns form will be used every time a customer returns their rented vehicle, so that the staff can enter and record the details of the return on file. A Date and Time formula will be displayed on the top-left hand corner of the form for the convenience of the user.
Next record, Previous Record, Add record, delete record, save record, archive record and Close buttons will be placed on the form, all with appropriate macros attached to perform these particular commands. There will be three formulae used in this form. One formula is attached to the Days Overdue field and it calculates the number of days the vehicle is overdue from the Date Returned and Date Due Back fields. Another formula is used for the “Miles traveled” field to calculate the miles the vehicle has traveled the last time it was rented out using the “Mileage Upon Return” and Mileage fields. This field is linked to the Mileage field in table vehicles, and will be used to update the mileage field when a vehicle is returned by adding this value to the appropriate vehicle’s mileage.
The formula used to calculate the Total price of the rental is a more complex formula than the others so I shall discuss it briefly to explain the values behind the equation. The value calculated is dependant on four factors – the number of days overdue the vehicle is (if any), the daily rental price of the rented vehicle, the number of days the vehicle is validly rented for and the number of miles traveled by the vehicle. This formula is (([DaysOverdue] ^ 1.05) * [Daily_Rental_Rate]) + ([Number_Of_Days] * [Daily_Rental_Rate]) + ([Miles_Travelled] * 0.25). The reason why “DaysOverdue” is held to the power of 1.05 before it is multiplied by the daily rental rate is so that the overdue fine is exponentially heftier the longer overdue the item is, but not so hefty as to be unreasonable, as I’ve deduced by feeding realistic values into the formula. This is to deter customers from holding onto vehicles after their due back date, as this will damage the business. So the first section of the formula can be thought of as the exponential overdue fine calculation. This value is then added to the standard rental charge, ([Number_Of_Days] * [Daily_Rental_Rate]). A calculation will be used to charge a 25 pence for each mile travelled, which is ([Miles_Travelled] * 0.25). This value is then added to the other two values, and this is how the overall rental price is reached. Exponentially calculated overdue fine + Standard rental charge + 25p per mile travelled = Total Price
Code will be incorporated into particular fields in this form, but I shall outline these further on in the code design section of my Design.
Query Design
1. qryAppOldCustomer
Query type: Append Query
Purpose: to transfer old customer records into an archive file.
Tables used: tblCustomer
Criteria: <Date () – (365*5) in Date of last Rental field.
2. qryFindParticularClass
Query Type: Parameter Query
Purpose: To view a list of all of the vehicles of a specified class within the system
Tables used: tblVehicles
Criteria: [Please enter a class of vehicle] in Class field.
3. qryFindParticularManufacturerVehicles
Query Type: Parameter Query
Purpose: To view the list of vehicles in the system supplied by a particular manufacturer.
Tables used: tblManufacturer
Criteria: [Please enter a manufacturer code] in manufacturer code field
4. qryOverdues
Query type: Select Query
Purpose: To identify overdue rentals, so that an appropriate action can be taken, such as telephoning the customer with the overdue item.
Tables used: tblRentals
Criteria: + Number of Days > Date() in Date of Rental Field.
5. qryClassRentalDiscount
Query type: Parameter Update Query
Purpose: To give a 10% discount on all vehicles, say in the holiday seasons, without having to individually lower the price of every single vehicle held on the database. The class of vehicles to be updated is entered and then discount applied
Tables used: tblVehicles
Criteria: Update To “*0.9” in Daily Rental Rate field, when criteria in Class field is [Please enter the class of vehicles to be discounted].
6. qryStaffContactDetails
Query type: Dual parameter Query
Purpose: To display contact details of a staff member. This is useful if someone is late or off sick and the business needs to contact them.
Tables used: tblStaff
Criteria: [Please enter Forename] in Forename field and [Please enter Surname] in Surname field.
7. qryCustomerContactDetails
Query type: Dual parameter Query
Purpose: To display the contact details of a customer. This is useful if a rental is long overdue and the customer needs to be reminded via a telephone call or letter.
Tables used: tblCustomer
Criteria: [Please enter Forename] in Forename field and [Please enter Surname] in Surname field.
8. qryCurrentRentals
Query type: Select Query
Purpose: To display a list of all vehicles currently on rental for tracking purposes.
Tables used: tblRentals, tblVehicles
Criteria: A list of all the vehicles currently in the rental table will be listed
9. qryAppExStaff
Query type: Append Query
Purpose: to transfer old staff records into an archive file.
Tables used: tblStaff
Criteria: <Date () in Employment terminated field.
10. qryStaffDetails
Query type: Parameter Query
Purpose: to locate and view specific staff member details.
Tables used: tblStaff
Criteria: [Please enter Forename] and [Please enter Surname] in Forename and Surname fields respectively.
11. qryRentalsMadeToday
Query Type: Select Query
Purpose: To view all of the made today.
Tables used: tblRentals
Criteria: = Date() in Date of Rental field.
12. qryManufacturerDetails
Query Type: Parameter Query
Purpose: To locate and view a particular manufacturer’s details
Tables used: tblManufacturer
Criteria: [Please enter a manufacturer] in Manufacturer name field.
13. qryUpdateDateOfLastRental
Query Type: Update Query
Purpose: To update date of Customer’s last rental on their details
Tables used: tblRentals, tblCustomer.
14. qryGeneral Discount
Query Type: Parameter Update Query
Purpose: To give a user-specified discount to all vehicles.
Tables Used: tblVehicle
Criteria: [Please enter a discount value] in Daily Rental Rate Field. “*0.9” for 10% discount, “”*0.8” for 20% discount, and so on.
15. qryVehicleColour
Query Type: Parameter Query
Purpose: Enables the user to search for all vehicles of a particular colour.
Tables Used: tblVehicle
Criteria: [Please enter a vehicle colour] in the Colour field.
16. qryOverdueRentalContactDetails
Query Type: Select Query
Purpose: Enables the user to display a list of the contact details of all of the customers with overdue vehicles.
Tables Used: tblRentals, tblCustomer.
Criteria: + Number of Days > Date() in Date of Rental Field. Contact details of rentals which meet these criteria will be displayed.
17. qryManuufacturerContact
QueryType: Parameter Query
Purpose: Enables user to display to display a list of contact details for a particular manufacturer.
Tables used: tblManufacturer
Criteria: [Pleaser enter a manufacturer]
18. qryDelOldCustomer
Query Type: Delete Query
Purpose: Automatically deletes customer records a day after ten years have passed
Tables used: tblCustomerArchive
Criteria: < Date () – (365*10) in Date of last Rental field.
19. qryDelExStaff
Query Type: Delete Query
Purpose: Automatically deletes old staff records after ten years.
Tables used: tblStaffArchive
Criteria: < Date () – (365*10) in Employment terminated field.
20. qryAppOldReturns
Query Type: Append Query
Purpose: To automatically transfer old return records into an archive file.
Tables used: tblReturns
Criteria: <Date () – (365) in Date Returned field.
21. qryDelOldReturns
Query Type: Delete Query
Purpose: Automatically deletes return records older than five years.
Tables used: tblReturnsArchive
Criteria: < Date () – (365*5) in Date Returned field.
22. qryReturnsMadeToday
Query Type: Select Query
Purpose: enables user to view a list of all the returns made today.
Tables used: tblReturns
Criteria: = Date () in Date Returned field.
23. qryAppOldRentals
Query Type: Append Query
Purpose: Automatically transfers old rental records into an archive file.
Tables used: tblRentals
Criteria: = <Date () – (365) in Date of Rental file.
24. qryDelOldRentals
Query Type: Delete Query
Purpose: Automatically deletes rental records older than five years.
Tables used: tblRentalsArchive
Criteria: < Date () – (365*5) in Date of Rental field
25. qryMailMerge
Query Type: Select (Parameter)
Purpose: To display a list of all customers who haven’t rented a vehicle in a long time. A mail merge letter will then be sent to all of these customers.
Tables used: tblCustomer
Criteria: < =Date() – 100.
26. qryCurrentRentalCustomerDetails
Query Type: Select
Purpose: To display a list of all the details of all of the customers who currently have a vehicle rented
Tables used: tblRental, tblCustomer
Criteria: All customer details displayed whose customer ID is in a record in the rentals table.
Report Design
1. rptFindParticularClass
This report is based upon the query, qryFindParticularClass, and its purpose is to present the information produced from the query in a structured format that can be printed. It displays the details of all of the vehicles in the class that the user has specified. It can be used for the decision making process.
2. rptFindParticularManufacturerVehicles
This report is based upon the query, qryFindParticularManufacturerVehicles, and its purpose is to present the information produced from the query in a structured format that can be printed. It displays the details of all of the vehicles of the manufacturer that the user has specified. It can be used for the decision making process.
3. rptOverdues
This report is based upon the query, qryOverdues, and its purpose is to present the information produced from the query in a structured format that can be printed. It displays the details of all of the overdue vehicles. It can be used for the decision making process.
4. rptCurrentRentals
This report is based upon the query, qryCurrentRentals, and its purpose is to present the information produced from the query in a structured format that can be printed. It displays the details of all of the currently rented vehicles. It can be used to aid the decision making process.
5. rptStaffDetails
This report is based upon the query, qryStaffDetails, and its purpose is to present the information produced from the query in a structured format that can be printed. It displays the details of every staff member. It can be used to aid decision making processes.
6. rptRentalsMadeToday
This report is based upon the query, qryRentalsMadeToday, and its purpose is to present the information produced from the query in a structured format that can be printed. It displays a list of all of the rentals made that day. It can be used to aid decision making processes.
7. rptManufacturerDetails
This report is based upon the query, qryManufacturerDetails, and its purpose is to present the information produced from the query in a structured format that can be printed. It displays the details of every manufacturer on record. It can be used to aid decision making processes.
8. rptOverdueRentalContactDetails
This report is based upon the query, qryOverdueRentalContactDetails, and its purpose is to present the information produced from the query in a structured format that can be printed. It displays the contact details of all customers with an overdue item. It can be used to aid decision making processes.
9. rptReturnsMadeToday
This report is based upon the query, qryReturnsMadeToday, and its purpose is to present the information produced from the query in a structured format that can be printed. It displays a list of all items that were returned that day. It can be used to aid decision making process.
Report Code
This piece of code will be attached to all of the reports so that they will automatically close if the report yields no data. This is to save the user a little time having to close the report themselves.
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No data found. Closing report."
Cancel = True
End Sub
Menu Designs
Images will be inserted in the top corners of all the menus to enhance their aesthetic appeal. The backgrounds will be a light shade of blue and the font used will be plain old Times New Roman with a font size of 12 for familiarity. No exotic fonts or attention-diverting backgrounds; just a nice, friendly, unintimidating, unambiguous menu that serves its purpose. This same menu design will be used for all of the other menus in the database to create a sense of consistency and familiarity with the user operating the system. This menu design is similar in principle to the form designs. This is so a degree of uniformity exists between these two elements
Main Menu
This is the main menu of the entire database. It’s like the hub of the system from which all other points can be accessed. This will open automatically when the database itself starts up, via the attachment of the macro mcrAutoExec.
Form Menu
This is the menu from which all the forms can be accessed, used by the user as the means of entering new data into the system. This will maximize upon opening, via the
attachment of the macro mcrMaximise.
Queries Menu
This is the menu from which all of the queries can be accessed, which are used by the user to extract useful information from the system. This will maximize upon opening, via the attachment of the macro mcrMaximise. Due to space constraints, I cannot outline all of the macros involved in this form on this page. Imagine an appropriate macro attached to each button.
Reports Menu
This is the menu from which all of the reports can be accessed, which is useful when a user wants to view, present, or produce a hard copy of the data extracted from a query. This will maximize upon opening, via the attachment of the macro mcrMaximise. Once again, due to space constraints, I will not outline the macros involved with each button in the menu. This will be dealt with in the Macro Design section though.
Tables Menu
This is the menu from which all of the tables in the database can be accessed. This will maximize upon opening, via the attachment of the macro mcrMaximise.
Archive Menu
This is the menu from which all of the archive files can be accessed by the user. This will maximize upon opening, via the attachment of the macro mcrMaximise.
Macro Designs
1. AutoExec
Action: Echo
Action: Hourglass
Action: RunCommand
Command: WindowsHide
Action: Open Form
Form Name: frmMainMenu
Command: DocMaximise
This macro is attached to the main menu of the system and means that it will appear onscreen as soon as the database is opened. The macro also maximizes the menu so that it occupies the whole area of the screen.
2. mcrAbout
Action: MsgBox
Message: “This system was created by Marc Bentley for Fred’s Car Rentals…”
This macro is attached to the “About” button present on the Main Menu of the system. When this button is clicked, this macro will bring up a message regarding the system, such as the date it was created, the organization it was made for and who it was created by.
3. mcrAddCustomer
Action: Go To Record
Object Type: Form
Object Name: frmCustomer
Record: New
This macro will be attached to the add new customer record button on the Customer Form. When clicked, a blank record will appear, ready for the data entry clerk to input the required data. There is a variation on this macro for each form, so there is also a mcrAddStaff, mcrAddVehicle, mcrAddManufacturer, mcrAddRental and mcrAddReturn.
4-8. mcrAddStaff, mcrAddVehicle, mcrAddManufacturer, mcrAddRental and mcrAddReturn.
These macros are essentially identical to macro 3, except that the “Object Name” property in the above macro is frmStaff, frmVehicle, frmManufacturer, frmRental and frmReturn respectively for macros 4-8. They carry out the same function as macro 3 (adding a new record), but only they perform this function in their respective forms.
9. mcrNextCustomer
Action: Go To Record
Object Type: Form
Object Name: frmCustomer
Record: Next
This macro will be attached to the “next customer” record button on the Customer Form. When clicked, the next record on the form will appear. There is a variation on this macro for each form, so there is also a mcrNextStaff, mcrNextVehicle, mcrNextManufacturer, mcrNextRental and mcrNextReturn.
10-14. mcrNextStaff, mcrNextVehicle, mcrNextManufacturer, mcrNextRental and mcrNextReturn.
These macros are essentially identical to macro 9, except that the “Object Name” property in the above macro is frmStaff, frmVehicle, frmManufacturer, frmRental and frmReturn respectively for macros 10-14. They carry out the same function as macro 9 (moving to the next record when the button is clicked), but only they perform this function in their respective forms.
15. mcrPreviousCustomer
Action: Go To Record
Object Type: Form
Object Name: frmCustomer
Record: Previous
This macro will be attached to the “previous customer” button on the Customer Form. When clicked, the previous record on the form will appear. There is a variation on this macro for each form, so there is also a mcrPreviousStaff, mcrPreviousVehicle, mcrPreviousManufacturer, mcrPreviousRental and mcrPreviousReturn.
16-20. mcrPreviousStaff, mcrPreviousVehicle, mcrPreviousManufacturer, mcrPreviousRental and mcrPreviousReturn.
These macros are essentially identical to macro 15, except that the “Object Name” property in the above macro is frmStaff, frmVehicle, frmManufacturer, frmRental and frmReturn respectively for macros 16-20. They carry out the same function as macro 15 (moving to the previous record), but only they perform this function in their respective forms.
21. mcrPrint
Action: PrintOut
This macro will be attached to the print button present on every form. When clicked, the record on display at that time is sent to a connected printer so that a hard copy of that particular record is produced.
22. mcrCloseCustomerForm
Action: Close
Object Type: Form
Object Name: frmCustomer
Save: Prompt
Action: Open Form
Form Name: frmMainMenu
Command: DocMaximise
This macro will be attached to the Close button on the customer form. When clicked, the open customer form window will close. Before closing, a prompt will appear asking the user whether they would like to save any work they may have done on the form before its subsequent closing. This way, if the user accidentally clicks the close button and has not saved the data is not lost. Also, when the customer form closes, the Main menu will automatically open up maximized on the screen. A variation on this macro will be present on all of the other forms. Variations on this macro include mcrCloseStaffForm, mcrCloseVehicleForm, mcrCloseManufacturerForm, mcrCloseRentalsForm and mcrCloseReturnsForm.
23-27. mcrCloseStaffForm, mcrCloseVehicleForm, mcrCloseManufacturerForm, mcrCloseRentalsForm and mcrCloseReturnsForm.
These macros are essentially identical to macro 22, except that the “Object Name” property in the above macro is frmStaff, frmVehicle, frmManufacturer, frmRental and frmReturn respectively for macros 23-27. They carry out the same function as macro 22 (Closing the form, and opening a maximized Main Menu), but only they perform this function in their respective forms.
28. mcrSaveCustomerForm
Action: Save
Object Type: Form
Object Name: frmCustomer
This macro will be attached to the “Save” button on the customer form. When clicked, any records added into the data input form will be saved. A variation on this macro will be present on all other forms. Variations on this macro include mcrSaveStaffForm, mcrSaveVehicleForm, mcrSaveManufacturerForm, mcrSaveRentalsForm and mcrSaveReturnsForm.
29-33. mcrSaveStaffForm, mcrSaveVehicleForm, mcrSaveManufacturerForm, mcrSaveRentalsForm and mcrSaveReturnsForm.
These macros are essentially the same as macro 28, except that the “Object Name” property in the above macro is frmStaff, frmVehicle, frmManufacturer, frmRental and frmReturn respectively for macros 29-33. They carry out the same function as macro 28 (saving any records that have been added to the data input form), but only they perform this function in their respective forms
34. mcrOpenFormsMenu
Action: OpenForm
Form Name: frmFormMenu
Command: DocMaximise
Action: Close
Object Name: frmMainMenu
This macro will be attached to the “Forms” Button on the main menu. When clicked, it will open up the Forms menu, and the menu will maximize upon opening. The main menu is also closed upon the opening of the form to conserve memory and avoid window congestion. A similar macro is used for the buttons “Tables” “Queries”, “Reports” and “Archive buttons, also present on the Main menu. Variations on this macro include mcrOpenTablesMenu, mcrOpenQueriesMenu, mcrOpenReportsMenu and mcrOpenArchiveMenu.
35-38. mcrOpenTablesMenu, mcrOpenQueriesMenu, mcrOpenReportsMenu and mcrOpenArchiveMenu.
These macros work in a similar way to macro 34, except that the “Form Name” property in the above macro is frmStaff, frmVehicle, frmManufacturer, frmRental and frmReturn respectively for macros 35-38. They carry out the same function as macro 34 (opening the form maximized and closing the main menu), but only they open their respective forms.
39. mcrOpenCustomerForm
Action: OpenForm
Form Name: frmCustomer
Command: DocMaximise
This macro will be attached to the “Customer Form” button on the Form menu. When clicked, the customer form will open maxmised. A similar macro is used for the buttons “Staff Form”, “Vehicle Form”, “Manufacturer Form”, “Rentals Form” and “Returns Form”.
40-44. mcrOpenStaffForm, mcrOpenVehicleForm, mcrOpenManufacturerForm, mcrOpenRentalsForm, and mcrOpenReturnsForm.
These macros work in a similar manner to macro 39, except that the “Form Name” property in the above macro is frmStaff, frmVehicle, frmManufacturer, frmRental and frmReturn respectively for macros 40-44. They carry out the same function as macro 39 (opening a form maximised), but only each macro is responsible for opening its respective form.
45. mcrOpenqryAppOldCustomer
Action: OpenQuery
Query Name: qryAppOldCustomer
View: Datasheet
Data Mode: Edit
This macro will be attached to a button in the Queries menu. When the button is clicked, the macro will make the appropriate query run. There is a similar macro for all of the other 24 queries.
46-69. mcrOpenqry…for all of the other 24 queries.
These work similarly to macro 45, except that the “Query Name” property in the above macro is different for all of the other 24 queries. They carry out the same function as macro 45 (running a query), but only each macro is responsible for running its respective query.
70. mcrOpenrptParticularClass
Action: OpenReport
Report Name: rptParticularClass
View: Print
Window Mode: Normal
This macro will be attached to a button in the Report menu. When the button is clicked, the macro will cause the appropriate report to run. There is a similar macro for all of the other 8 reports.
71-78. mcrOpenrpt…for the other 8 reports
These work similarly to macro 70, except that the “Report Name” property in the above macro is different for all of the other 8 reports. They carry out the same function as macro 70 (opening a report), but only each macro is responsible for running its respective report.
79. mcrOpentblCustomer
Action: OpenTable
Table Name: tblCustomer
View: Datasheet
Data Mode: Edit
This macro will be attached to a button in the Table menu. When the button is clicked, the macro will cause the Customer table to run. There are variations on this macro such as mcrtblStaff, mcrtblVehicles, mcrtblManufacturer, mcrtblRentals and mcrtblReturns.
80-84. mcrOpentblStaff, mcrOpentblVehicles, mcrOpentblManufacturer, mcrOpentblRentals and mcrOpentblReturns.
These macros are similar to macro 79, except that the “Table Name” property in the above macro is mcrOpentblStaff, mcrOpentblVehicles, mcrOpentblManufacturer, mcrOpentblRentals and mcrOpentblReturns respectively for macros 80-84. They carry out the same function as macro 79 (opening a table), but only each macro is responsible for opening its respective table.
85. mcrCloseDatabase
Action: Quit
Options: Prompt
This macro is attached to the “Exit Database” button on the Main Menu, and it closes the system and exits Microsoft Access when clicked. Before it closes, a prompt appears asking the user if they would like to save all the changes to the database made in that session before they exit.
Code Design
1. There will be particular fields on my forms in which a capital letter must be displayed at the start of the text in the field. For instance, a customer or staff member’s forename and surname really ought to be displayed with a capital letter at the beginning. Thus, I will design a piece of code that will convert the first letter entered in such fields to a capital letter, even though the user may have typed this letter in lower case. This piece of code will be incorporated into the “On Exit” property of the necessary fields.
On the “On Exit” property of the field
Set the first letter to proper case
End the procedure
With this piece of code, the first letter entered into a field in which the code is incorporated will be automatically converted to a capital letter if it is not already entered as such by the user. This will be included in the following fields: Forename, Surname, Town and Country fields in the Customer form; Forename, Surname, Town, Country, Job Title and Job description fields in the Staff form; Class Colour and Model fields in the Vehicle Form; and Manufacturer Name, Manufacturer Contact and Manufacturer description fields in the Manufacturer Form.
2. There will be particular fields on my forms in which the entire string of text is to be displayed in capital letters. For instance, Postcodes and Identification Numbers really ought to be displayed entirely in uppercase. Thus, I will design a piece of code that will convert the entire sting of text entered into such fields into uppercase, even though the user may have typed some of the characters in lowercase. This piece of code will be incorporated into the “On Exit” property of the necessary fields.
On the “On Exit” property of the field
Set the whole field to uppercase
End the procedure
With this piece of code, the entire sting of characters entered into a field in which the code is incorporated will be automatically converted to upper case if not already entered as such by the user. This will be included in the following fields: Customer ID and postcode fields on the Customer form; Staff ID and Postcode on the Staff Form; Vehicle ID and Registration Number on the Vehicle form; Manufacturer ID on the Manufacturer form; Rental ID on the Rental form; and Return ID on the Returns form.
3. There will be a combo box incorporated into both Customer and Staff forms, that will enable the user to select a particular Customer or Staff record to be displayed by bringing up a drop-down list of all of the staff and customers surnames. It will be the first customer and staff member in the list whose record will be displayed upon selecting the Surname from the drop-down list in their respective forms. In order to produce such a result, there will be a piece of code written to the “On Click” property of a button placed beside the combo box named “Find Next”.
On the “On Click” property of the button
Set Focus on the Surname field
Find the record with that surname
End the procedure
This piece of code attached to the “Find Next” button on both the Customer and Staff forms ensures that the focus is on the Surname and that the form will be automatically set to display the first record of the Surname.
4. In the event of more than one customer or staff member sharing the same Surname, the user is able to click on the “Find Next” button (this button will display the caption “Next”) accompanying the combo box and the next record of that particular Surname will be displayed onscreen. When the user cycles through all of the records of that Surname by clicking this button, the button will display the caption “No More”, when pressed, notifying the user that they have already cycled through all records of that particular surname and thus further presses of the button will not yield anymore such records. To achieve this, the code on the “On Click” property of the button will be set to:
On the “On Click” property of the button
Dim CR as String
Set CR as CustomerID (StaffID in the case of the Staff form)
Set Focus on the Surname field
Find the record with that Surname
Set Focus on the button
If that is the last CustomerID (StaffID in the case of the Staff form) with that Surname set the caption on the button to “No More”
End If statement
End the procedure
This code enables the user to both locate the next Staff or Customer record of a particular Surname, and be notified that there are no more records of that Surname if they attempt to look for more by clicking on the “Find Next” button.
5. As a consequence of the previous piece of code, whenever the user goes to select a different Surname from the combo box after exhausting all of the records for a previous surname, then the “Find Next” button will erroneously display “No More” whenever a new surname is selected. To address this issue, I shall have to design a piece of code to reset the caption on the button to “Next” when a new Surname is selected:
On the “Lost Focus” property of the button
Set the caption to “Next”
End the procedure
6. This piece of code will be attached to all of the reports so that they will automatically close if the report yields no data. This is to save the user a little time having to close the report themselves.
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No data found. Closing report."
Cancel = True
End Sub
7. This piece of code will be attached to the Customer and manufacturer form, and its purpose is to hide the subform if there are no records present in it. This will make the data input form a little less cluttered and appear a little tidier, as there is no need to have the sub-form present if there are no records contained within it.
Private Sub Form_Current()
With Me![SubformName].Form
.Visible = (.RecordsetClone.RecordCount > 0)
End With
End Sub
8. This piece of code will be attached to all of the forms. Its purpose is to disable “Page Up”, “Page Down” “Tab” and “Alt” keys when using the form. This is to reduce the chance that a slip of the hand could negatively affect the data held within the database. Each form’s Key Preview property will be set to true and the code will be attached to the OnKeyDown event.
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
'33 - PgUp; 34 - PgDown; 9 - Tab; 18=Alt
Select Case KeyCode
Case 33, 34, 9, 18
KeyCode = 0
Case Else
'Debug.Print KeyCode, Shift
End Select
End Sub
Security Design
I will create and assign user groups, and individual user accounts, to suit the role of the different staff members in the organization. But first, I’ll remind you of the different members of staff within the business:
- The manager (Fred)
- Two Assistant Managers
- A Vehicle Manager
- Three floor staff
Fred, the manager, will have complete administrative access over the system. In the interview conducted at the analysis stage, Fred revealed that he has had experience working with ICT systems in the past and that he would be capable of overseeing the system as an administrator. He will be able to view and change passwords in the system and have complete read/write access over every component of the database. With regards to password design, that will be his responsibility, however I will advise him to choose a password of at least six characters and encourage the integration of both letters and digits into it. I will also advise him to change the passwords every couple of months to be safe. He will be responsible for assigning passwords to his staff, yet I will create the user groups and individual user accounts
Both Assistant managers will have assistant administrative access over the system. A user group will be created for this purpose that will have read/write access over most of the system. However, they will not be able to change and view other user’s passwords like Fred can. They will not have any access whatsoever to the staff entity or forms as that is solely the managers concern. Also, they will have Read Only access to the Vehicle and Manufacturer entities and forms, as the editing rights for this component are the vehicle managers alone (excluding Fred obviously). Passwords will be distributed to the Assistant managers via a private meeting with Fred.
The vehicle manager will have a unique user account assigned to him with various access rights and restrictions. On one hand, his user account will not be able to access the customer, staff, rental or return entities as he need not concern himself with those details. He will not be able to change or view other users’ passwords and will not have access to the staff entity or form. He will, however, have read/write access to the Vehicle and Manufacturer entities and forms. Part of his job will be the maintenance of this component of the system. His password will be given to him in a private meeting with Fred.
The floor staff will have general user access to the system. They will not be permitted to change or view user’s passwords or access the staff, vehicle, and manufacturer entities and forms, as they need not concern their selves with such details. They will have read/write access to the customer, rentals and returns entities and forms. Their individual user account passwords will be given to them in a private meeting with Fred.
Mail Merge Design
Fred’s Car Rentals
25 Bridge Street
BT65 3HU
<<Current Date>>
<<Forename>> <<Surname>>
<<StreetAddress>>
<<Town>>
<<Postcode>>
Dear <<Forename>> <<Surname>>
On behalf of the staff of Fred’s Car Rentals I would like to wish you a Merry Christmas! ‘Tis truly the season to be jolly as, for the duration of yuletide, loyal customers such as yourself will be rewarded with a 10% discount on all vehicle rentals! There are certainly no cries of “Bah Humbug!” coming from the offices of Fred’s Car Rentals this winter! So wrap up well and get yourself down to Fred’s Car Rentals to take advantage of this goodwill befitting of ol’ Saint Nic himself!
I look forward to seeing you.
Yours sincerely,
Fred
Breakdown of Completed Tasks
Design Agreement with End-User
Now that the Design stage has been completed, I am presenting it to Fred, the manager of Fred’s Car Rentals, so that he can inspect it to see whether it meets the standard that he expects. If Fred does not agree that this section is completed to the standard that he expects, then my assignment will end here.
Having studied the analysis:
I agree that the Design section of the project is completed to my satisfaction and to the standard that I expect, with all of the features outlined in the analysis designed to my satisfaction. I want you to implement this system to Fred’s Car Rentals.
I am unsatisfied with the Design part of the project and I don’t feel that it has been completed properly. I do not want you to implement this system to Fred’s Car Rentals.
Signed: _______________________ Date: _____________
Manager of Fred’s Car Rentals
