www.sensalgo.comCarRent
Access Application
Version 2.0
February
2000 to February 2004
The CarRent database is an application for Car vendors that sell their cars by leasing contracts. It accompanies the work with customers and the calculation of costs and installments.
The CarRent database provides the advantage to select the model type, version and equipment characteristics of a car. The customer himself can do this in an easy way. Further more the respective leasing installment is calculated by the application. This enables the possible customer to experience with the prices for different model types, versions and equipments. The selling personnel saves valuable time working on offers. The first time a possible customer contact his car dealer, he will be much better informed and comes with a special demand with only a few questions left.
This database tool makes it easy for the selling personnel to quickly answer questions per telephone concerning leasing installments and equipment characteristics, if a customer has no information from the company.
In the following the structure of the CarRent.mdb is explained.
| Tables | Short Description |
| ContactPersons | The table contains contact persons concerning the database maintenance. |
| Costs | The table contains the cost components for the leasing installment calculation. |
| CustomerList | The table contains the personal data of the registered customers. |
| LeasingTime | The table contains possible leasing times in months with according per cent values. |
| PricesMultipla | The table contains the prices for all possible equipment characteristics available for the FIAT Multipla Model. |
| PricesPunto | The table contains the prices for all possible equipment characteristics available for the FIAT Punto Model. |
| PricesSpider | The table contains the prices for all possible equipment characteristics available for the FIAT Spider Model. |
| RestValueMultiplaSpider | The table contains the rest values of the FIAT Spider after a defined period of time and kilometers driven. |
| RestValueSeicentoPuntoBravo/a80LanciaLybra | The table contains the rest values of the other FIAT models after a defined period of time and kilometers driven. |
| Inquiries | Short Description |
| CustomersToCallByPhone | The inquiry selects all datasets of the CustomerList table that contain a customer, who wants to be called by phone. |
| CustomersToWriteOrMailTo | The inquiry selects all datasets of the CustomerList table that contain a customer, who wants to be informed in writing about a special car model or all car models. |
| Statistics | The inquiry contains statistics about the CustomerList table such as the number of statements in the different fields and the according per cent value of all datasets in the CustomerList table. |
| Forms | Short Description |
| CustomerList (Form) | The form enables the prospective customer to leave his personal data to be informed or contacted later. |
| LeasingInstallment | The form queries the missing statements, such as the leasing time, and finally calculates and displays the leasing installment. |
| MultiplaSelection | The form enables the prospective customer to select the model version of the FIAT Multipla with its specific equipment characteristics. |
| StartPage | The startpage is the central point of navigation within the database. From there you can directly access mostly all forms. |
| Reports | Short Description |
| CustomersToCallByPhoneReport | This report display an overview of all customers of the CustomersToCallByPhone inquiry. All of them wanted to be phoned by the CarRent company. |
| CustomersToWriteOrMailToReport | This report display an overview of all customers of the CustomersToWriteOrMailTo inquiry. All of them wanted to be contacted by mail or post by the CarRent company. |
| StatisticsReport | The report displays statistics about the CustomerList table such as the number of statements in the different fields and the according per cent value of all datasets in the CustomerList table. This report depends on the Statistics inquiry. |
| Sides | Short Description |
| CustomerRegistration | This HTML page is a standard Access data access page to make the functionality of the CustomerList form available to the internet. |
| Modules | Short Description |
| modMain | modMain is an internal module for declaration and initialization of the global variables, needed in the database. It is started automatically by the form StartPage. |
| Relations | Short Description |
General
Information
Here
you can find the data basis of all database functionality.
The table "ContactPersons" contains the names of the database administrators. This information is needed for database support purposes only. Further more it is intended to contain the contact persons of the CarRent company for special questions concerning the leasing contract for a special car. Simply click on an e-mail entry in the table, and write your problem in the opening e-mail application.
The table "Costs" contains all internal cost types and values, as they appear for all three car types FIAT Multipla, FIAT Punto and FIAT Seicento and all their subtypes with the same amount. Further more the table contains the demanded surplus for each vehicle and commissions, as they are needed for the calculation of the leasing installment for the prospective customers. Surplus, commissions and the stated factors are sensible data for the calculation.
The table "CustomerList" is the customer master list, which contains those prospective customers of the CarRent company, who want to be called or want to receive an e-mail with information about the three motor vehicle types. The admission of the desires is held scarcely to make it possible for the CarRent company to send the prospective customer all advertising materials, since the prospective customer cannot concretize its desire too strongly. In the case of purposeful questions the prospective customer however has the possibility to send an e-mail immediately to the CarRent company. The admission of the CarRent prospective customers takes place by the data access page CustomerRegistration that will be provided in the Internet.
The table "LeasingTime" contains internal factors that are called P1 and P2. They are needed for the calculation of the leasing installment depending on the contract running time.
The table "PricesMultipla" contains the prices for the FIAT Multipla in the same way as they are displayed in the form MultiplaSelection. The first line of the table contains our so-called "Null vehicle", its name "Please select" in the form field of the form MultiplaSelection as the first decision criterion is preselected. It is responsible for the fact, that the user sees no prices and no preselected vehicle, before his first selection, and therefore is animated to a conscious selection.
In this table all equipment characteristics of the individual model types are specified, as they are to be counted together for the total list price. Further more the table contains the monthly vehicle tax and insurance premiums for the different engines.
For they are depending upon the type of model, some equipment characteristics are already in the basic price, while other equipment characteristics are however not at all available, the table contains the "AM..." fields. These fields contain the constants -1, 0 and 1 and specify for each model version the following:
| -1 | not selectable | The control box in the form MultiplaSelection is blocked, the associated equipment characteristic cannot be selected. |
| 0 | selectable |
The control box in the form MultiplaSelection is not blocked, the associated equipment characteristic can be selected. The selection increases the basic price. |
| 1 | not selectable | The control box in the form MultiplaSelection is preselected, the associated equipment characteristic cannot be deselected by the user. The price of the associated equipment characteristic is included in the basic price. |
The specifically named columns of the PricesMultipla table correspond in its order with the "AM..." fields at the end of the table. The effect of the above constants on the form "MultiplaSelection" are programmed with Visual Basic in the background of the database.
The table "PricesPunto" is implemented in the same way as the table "PricesMultipla" that is described above. Since our database treats however only exemplarily the Fiat Multipla, this table is insignificant for the further function.
The table "PricesSpider" is implemented in the same way as the table "PricesMultipla" that is described above. Since our database treats however only exemplarily the Fiat Multipla, this table is insignificant for the further function.
and
RestValueSeicentoPuntoBravo/a80LanciaLybra
The table "RestValueSeicentoPuntoBravo/a80LanciaLybra" contains the residual value factors, which are to be set on the total list price. These factors are different depending upon the run achievement in kilometers and the age of the car in months and lead to the rest standard value of the car.
The residual value is also important for the computation of the leasing installment and represents besides likewise a sensitive date. The selection of the correct residual value factor is important for the form "LeasingInstallment", where the leasing installments are to compute, and is likewise programmed in Visual Basic in the background of the form.
The residual value table for the Multipla is likewise valid for the Spider of Alfa Romeo, what explains the naming of the table "RestValueMultplaSpider". This behaves likewise for the residual value table "RestValueSeicentoPunto Bravo/a80 LanciaLybra".
The
inquiries are placed directly under the tables in the objects column of the
database. There are three inquiries:
The first inquiry specified is "CustomersToCallByPhone". This inquiry does filter out those people (CarRent's prospective customers) from the table "CustomerList", who want to be called by phone. By this, it is later possible to report these customers (see CustomersToCallByPhoneReport) in a print out and to give this information to the CarRent employees, to enable them to phone every entry of the list in a single batch operation.
The second inquiry is named "CustomersToWriteOrMailTo". Also this inquiry provides a filter to select only the prospective customers with the desire to get information by post or e-mail. So a report (see CustomersToWriteOrMailToReport) can be printed out and given to the CarRent employees to make their work more easy.
The last inquiry carries the name "Statistics" and represents a filter for the statistics of the table "CustomerList", which are later likewise printable as a report (see "StatisticsReport"). In this inquiry values are not only selected but already computed, e.g. per cent values of the availability of attributes in the whole of statements.
After the inquiries the following objects are the forms, we partly mentioned before.
The form "CustomerList" serves the admission of potential customers. Here the desire for a telephone recall or for getting documentation to the three FIAT models can also be expressed. These data become stored in the table "CustomerList".
The form "MultiplaSelection" is for the selection of a version of the FIAT Multipla. Here the prospective customer first has to decide, which type of model (engine) he would like to have. After that he sees the basic price with additional information and the available equipment characteristics. Every available equipment characteristic the prospective customer selects here, increases the total price, which is indicated at the bottom of the form. The total price is the basis for the computation of the leasing installment.
The form "LeasingInstallment" serves for the computation of the leasing installment. Here first the total list price of the selected vehicle is indicated, as it was arranged in the form "MultiplaSelection" before. In the text fields "Months", "Total kilometers" and "Payment on account" the prospective customer has to select the entries, that correspond to his needs and requirements. After that the monthly leasing installment is calculated automatically by the form. The computation is made by one formula programmed in Visual Basic. The default values in this form is the most common selection made by CarRent customers: 36 months with a run achievement of 45.000 km and a payment on account of DM 2,490. You can also print out the overview of this form by clicking the corresponding button.
The "StartPage" is the central point of navigation within the database. From here you can directly access mostly all forms. The form "StartPage" makes it possible to select different motor vehicle types, in our case the selection of the Multipla. The other two car models, the FIAT Seicento and the FIAT Punto, are not yet imlemented, for the database organization serves only for the purpose of demonstration.
The "StartPage" form will start the Access application on the form level, if the database CarRent.mdb is opened. The "StartPage" starts the procedure Main() in the module modMain in the background. Thereby the global variables of the application are defined and initialized.
After the forms now the reports follow in the object sequence, with which we likewise already partly dealt.
The report "CustomersToCallByPhoneReport" makes it possible to print out those CarRent prospective customers from the table "CustomerList", who want to be called by phone. These print outs can be distributed to the CarRent coworkers for establishment of contact with the customer.
CustomersToWriteOrMailToReport
The report "CustomersToWriteOrMailToReport" serves the possibility to print out those CarRent prospective customers from the table "CustomerList", who want to get information by post or to receive information by e-mail. Also these print outs can be distributed to the CarRent coworkers for establishment of contact with the customer.
The report "StatisticsReport" offers the possibility to receive an impression over the CarRent prospective customers. Here you see for example their interest distribution on the offered FIAT models or their medium age etc., in most cases both absolutely and in per cent.
The object sides offers an additional possibility for interaction with prospective customers by special internet sides, which are present in the .htm format. These sides can be called directly as www. documents by the customers with an internet browser. They are connected in the background with our database.
For the admission of the customer data and the information desires of the customers such a side is present under the name "CustomerRegistration". It was produced by the means of the Access assistant out of the form "CustomerList". By editing this side in the source format you have the possibility of appropriate changes and extensions. Unfortunately beyond that to realize the vehicle selection and the leasing installment computation also with such data access sides cannot be accomplished, since there are too little adaptable parameters in creating these sides by Access for such special tasks. In particular no formulas can be deposited for these sides in the draft mode by Access. This requires further manual programming.
Under modules you can find a
module named modMain. In this the declaration of the variables is done. These
variables are needed later for the Visual Basic code of the other forms. modMain
is called with its procedure Main() by the form "StartPage".
The majority of the code however is located locally in the modules of the forms "MultiplaSelection" and "LeasingInstallment". In both forms in the general part you can find functions, which are then used by the different event procedures. Both with functions and with variables speaking names were used, in order to facilitate the understanding. Also the comments serve that. The crucial leasing installment formula is located in three lines after the corresponding comment in the form "LeasingInstallment" in the general part under the function "BerecheRate()".
Relations between the tables, which apply to the entire database, do not exist, since appropriate data inquiries from other tables were realized more effectively with Visual Basic.