Building the Database: Key Elements of a Car Rental Firm

I will be explaining below, how I made the database and several different key elements which make up the database of the Car Rental firm.

In order to create my tables and files for the car rental system I first had to create the database.:

After I opened Access I was presented with a blank screen and I selected 'Create a New File':

After that I selected 'blank database' and a save screen appeared so I named my database and saved it to my pendrive:

I then created the database with a suitable file name:

Then the main database window opened up which gives me the ability to create tables, forms, reports and queries:

Creating the Member's Table

I start off in the Main Database Menu and make sure it is in table and then double click the 'Create Table in Design View' which creates the table in design view where I can input all the data that I need:

I then put my field names in the field names column:

Once the data has been entered I need to alter the 'Data Type' as it is automatically set up by Access as 'Text'.

Most of the fields are Text with the exception of 'Member_id', 'Date of Birth' and 'Number of cars previously rented' so it is relatively easy to change the ones that need changing:

I then need to save the database my closing it and clicking yes when it asks me if I want to save:

Changing the Member's Table

When looking through the table, I found that the 'Telephone Number' field's data type was set to number:

I need to change this because when the data type is set to 'Number' it doesn't show the zero at the beginning of the number.

Get quality help now

Prof. Finch

Verified writer

Proficient in: Data

4.7 (346)

“ This writer never make an mistake for me always deliver long before due date. Am telling you man this writer is absolutely the best. ”

+84 relevant experts are online

Hire writer

I changed it to 'Text', as this allows the field to have the zero at the beginning.

Creating the Car's Table

The way that I made the car's table is basically the same as the Member's table except that the fields used were different.

I click on 'Create a Table in Design View' again and entered the field names:

I then set the data types with them all being Text except 'Member ID' which is set to AutoNumber and 'Year Made' which is set to Date/Time.

I then saved the table, naming in Tbl_Car just as I had done with the Customer table.

Changing the Car's Table

When looking through the table, I found that the 'Car reg number' field was set to 50 characters long:

I changed the 'Field Size' to 10 characters long, as no registration number is more than that and it creates space on my database.

Creating the Loans Table

The way loan's table was created in basically the same way as the Member's table and the Car's table except that the fields used were different.

I then set the data types and saved it with the name Tbl_Loans, as shown in the screen shot above.

Changing the Loans Table

I decided to add a new field to Tbl_Loans:

The new field 'Date Returned' allows the company to keep track of when the cars are returned and whether or not they were returned on time. This is important as they need to know which cars are available for renting and also which customers have used the car for longer than allowed.

Creating Relationships

Once the tables have been made they can be linked together by creating relationships between them. First the relationship window must be opened by clicking on the relationships button on the toolbar or going to Tools and then Relationships:

A blank window appears with the 'show table' window appearing in front of it:

We can then add all of the tables by pressing shift and highlighting all of the tables and pressing Add:

Then click on the Member ID in Member List and drag it over to the Member ID in Loan Database. A line will then appear as you let go of the mouse button and then the Edit Relationships window will appear.

I select the 'Enforce Referential Integrity' tick box and after I have created it a line then appears between the two tables, showing the relationship that I have just created; the link is known as a 'One-To-Many' relationship as one member can rent more than one Car at a time.

I can then create a relationship for the Car_id in the loans table. I click on the Car_id in the Car database and drag it across to the Car_id field in the Loans Database. The Edit Relationships window appears and I click Enforce Referential Integrity and Create and a line appears between the two tables.

The Relationships window then should end up looking like this:

I then close the window and save the links between the tables.

Creating a Data Entry Form for Tbl_Customer

In the main database window I clicked on the Forms tab and clicked on 'Create Form By Using Wizard' and the form wizard will open:

Making sure the table selected under Tables/Queries is Tbl_Customers I then click on the double arrow button which puts all of the fields into the Selected Fields box.

I then click next and select Columnar and then I click next again and select standard.

Then click next again and enter the name of the form as Frm_Customer and then click finish and the form should appear:

Creating a Data Entry Form for Tbl_Car

Making sure I was still in the Form tab in the main database window I clicked on 'Create Form By Using Wizard' and the main window opened.

I clicked the down arrow under Tables/Queries and selected the Cars Database table. I then put in all the fields using the double arrow button and they moved over to the Selected Fields box.

I then clicked next should and selected columnar and I then clicked next again and then I selected standard. I then clicked next again and changed the name to Frm_Car and I selected finish and the form appeared like this:

Creating a Data Entry Form for the Tbl_Loans

Creating the Form for the Loans table was almost the same as he other two tables. With the only difference being that I selected Tbl_Loans in the first step:

The finished form looked like this:

Customising the Loans Form

Then I have to change the design of the Loans form to match the design of the form in the design section and to make the forms look professional and match the company's logo.

Firstly I made sure that the form had no record selectors or Navigation buttons as I was going to create my own:

I then changed the background of the form to blue:

I then changed the font to Trebuchet MS, Size 12, colour white. I also made the background of the text box blue to match the background of the form and made all the text boxes the same size:

I then added a title to make it more appealing:

Next, I added the Navigation buttons:

I then added the logo of the company that I am working for and the form was complete:

Changing the Design for the Customer and Car Form

I made exactly the same design changes for the customer and car form as I did for the Loans form.

They ended up looking like this:

I edited the forms to make them match the forms in the design, look professional and match the company's logo.

Creating a Report

Firstly I went to the main database window and clicked on the reports tab and then 'Create A Report Using the Wizard'. Then the first window which opens is where you have to decide which fields you will put into the report from the table you are using.

I chose to create a report of the Customer database:

I then clicked on the double arrow button to move all of the available fields over to the Selected Fields:

I then clicked next and I came to the grouping levels screen, I didn't change anything and clicked next again. I then came to the sorting order stage. I set the order as Member surname first, followed by member forename:

I then you changed the orientation to Landscape and the layout to tabular:

I then selected the layout that I wanted my report to have. I chose Casual and then clicked next:

I then changed the name of the report to Rpt_Customers and clicked on Finish:

The Report then opened. However, because some of the fields are made to a large scale some of the information was cut off the end:

Editing a Report

Editing a report is much the same as editing a form. The basic idea is the same except you only have to change the sizes of the boxes for the data and the field names because many of the names or other pieces of information do not fit onto the screen and so it makes it hard to see what the data is which may be important to read. Also it can put some fields out of line with the titles so you can read off the wrong item of information.

I opened the Report and then clicked on the Design button which opens the Report into design view:

By clicking on the Page Headers and Details and then making them smaller it means that I am able to fit all of the data onto the page. By making the boxes with smaller data in smaller it means that I am able to make the boxes which have data missing larger.

Once I had resized the boxes for the reports and clicked on the button which brings up the report as it looks with the data so that you can see what it looks like:

I then made the background blue and the text Font Trebuchet MS, Size 12, Colour white. I also made the background of the text boxes blue to match the whole background:

I then changed the title to make it more appealing:

This is what it ends up looking like:

I edited the other reports in exactly the same way and they ended up looking like this:

I edited the reports for much the same reasons as I edited the forms; to match the original design, to look professional and to match the company's logo.

Creating a Mail Merge

One feature of the database software that made it a good solution for the company was a mail merge letter which would enable one standard letter to be personalised automatically for each customer who has been chosen to receive it.

To begin the mail merge, I opened a blank document in the Word Processing application, then from the 'Tools' menu, selected 'Letters and Mailings' and then 'Mail Merge...'. This opened a new interface on the right hand side of the screen which would allow me to personalise and change the information which I would be using from the database within my mail merge.

On this toolbar, I selected the document type that I wanted, which would in this case be a letter. I then clicked 'Next' at the bottom to start my letter.

From here, I chose to import information from a database and then selected the location of my car database, on my USB drive. A window then opened, as shown below, asking me which table within the database I wanted to use. I chose the table Tbl_Customer which would import data about all of my customers so that I could send a letter to all previous customers.

I then received a preview of the information in that table, showing me all of the data about the customers found there:

I then had to choose the format of the greeting line:

Next, I had to insert the address block. This involved matching fields from my database to fields in the mail merge:

I also had to choose the format I wanted my address block to be like:

I can then preview the letters for all recipients:

Linking the Database with another Piece of Software

As well as using a Word Processor to create the mail merge, I can link the database with another piece of software. I chose to create a table to show when the cars were made. For this I used Spreadsheet Software.

Firstly I had to import data from my database:

I went to data, import external data, then import data.

I clicked on import data and this box appeared on the screen:

This shows all the destinations that my data could be in.

I went to the place where my database was and then opened it:

A box showing all my tables comes up.

I chose the table that I wanted to use (in this case Tbl_Car) and then opened it:

This shows all the information that I have in my database.

Using this I can select the information that I want for my graph:

I then clicked on the chart wizard button and chose a column graph:

I clicked next.

After that, this is shown on the screen:

I clicked on the series tab, this box then opens:

I then clicked on the category X axis and chose the X axis category (in this case car name) and I then clicked next.

It brings this screen up:

From here I can enter the chart title and the labels for the X and Y axes.

I then clicked finish; the graph appears on the spreadsheet: