- To review the tables in the SofiaCarRental database: On the File menu, click Connect Object Explorer.; In the Connect to Database Engine dialog box, type either localhost or the name of the local SQL Server instance, and then click Connect.; In the Object Explorer window, expand the node for the newly connected instance, then expand Databases, expand SofiaCarRental, expand Tables, and review.
- Entity relationship diagrams (ERD) are widely used in database design and systems analysis to represent systems or problem domains. This is a simple ER diagram example that shows the entities involved in a video rental system, which consists of customer, movie and producer. Use this ERD template to get started building your own.
- I am trying to learn SQL development using SQL Server 2008 R2 Database Management Studio. I was trying to model a car rental company, where clients can rent cars, and I have to know which client has rented which car on which date, and also the date the car was returned. I have tried to the following.
Car Rental Database (for Access 2010 and up) This is an example file of Car Rental Database. It is the basic of rental database, but you can add as many more fields or tables as you want. The Admin User can add new vehicles, new users, new customers, and other information.
Exceltemplates.org – Managing car rental reservation will be easier with the help of a special sheet of car rental. There are two main sheets that can be used in running the business of car rental reservation. The first sheet will be about the availability of rented cars while the other one is about the detail information on the customers who rent the cars. The two main sheets of car reservation will be great to be used every day in order to keep the reservations tidy and well organized and eventually bring the customers’ satisfaction. There is nothing better than customers who will show a huge smile after the service.
This car rental reservation is different from other spreadsheets you may find online. It has calendar layout with color markers. Those color markers represent the number of car availability at particular dates. This spreadsheet is fully editable. You can learn all functions in this spreadsheet to give you ideas on how to create similar spreadsheet to meet your specific needs.
How to Use Car Rental Reservation Spreadsheet
Since this spreadsheet is a simple spreadsheet, you might know how to use it once you open it. There are three worksheets. But, you may work on two worksheets only. Calendar worksheet contain dates, filled manually, for one year period. There are color markers where you can their values by replacing numbers in red, orange, yellow and green color boxes at the bottom of the calendar. This color marker is created using conditional formatting function where it get values from availability column in Year Schedule worksheet.
The availability column contains formulas. It sums “1” numbers across particular rows. Each car will have its own column. Type “1” if particular car is available and type other texts (like client name or any) or leave it blank. It is to prevent SUM function to sum numbers other than 1 in particular rows. There are color markers for your references.
The third worksheet is additional worksheet. It contains car information only. It has no formula links to other worksheets. Because this spreadsheet is a basic spreadsheet, you can modify and extend it to meet your needs. You can add more worksheets, extend car columns and add more important information regarding your car rental business. Also, you can make its layout to follow your business signatures.
To remind you, to change this calendar to another year, you need to type those dates manually. It should be easy. Copy calendar dates from other calendars and paste it as values in particular fields. Do not paste it directly since it will break the conditional formatting formulas.
Using Microsoft Excel to simplify your spreadsheet tasks is not as difficult as you think. I learned how to use it in less than 24 hours and rely on internet to find the solution if I got stuck. I am not an Excel expert, but here are all useful spreadsheets I have collected and created.
Download Sample Access File:
Car Rental Database (for Access 2010 and up)
This is an example file of Car Rental Database. It is the basic of rental database, but you can add as many more fields or tables as you want. The Admin User can add new vehicles, new users, new customers, and other information. The regular user can reserve a vehicle, customer, and rental. The rental process will start with finding the available vehicle then select the vehicle to reserve or rent.
Highlight functions or features in this sample file include:
#1 Login Form
This form is first loaded when the program is opened. It asks user to enter UserName and Password. If both UserName and Password are correct then it will open the MainMenu Form. You can set it to open any form as you want on this Login Form in your program.
#2 Main Menu Form
The Main Menu Form provides several buttons to open different forms as listed below. The details of each form will be explained below.
- Search Vehicle button
- Find Reservation button
- Find Customers button
- Search Rental button
- Admin button (for Admin only)
- Add New Vehicle button (for Admin only)
- Add New User button (for Admin only)
- Add New Customer button
#3 Search Available Vehicle Form
This form is used to search for the available cars to reserve or rent. The pickup date and return date is needed to find the available cars. The available cars are determined by the cars that have an Active status, are returned from renting, and not reserved by anyone during the pickup date and return date. If we want to reserve this car on that date, but it is already reserved on that date by someone, then that car is considered not available. This form provides the following buttons or functions:
- Search for the available vehicle with a criteria from the pickup date and return date
- Filter the result by the body type of vehicle like Sedan, Van/Minivan, truck, etc.
- Clear the criteria and result
- Show all records from the result of searching
- A button “Reserve” selects the vehicle and opens the Reservation Form to reserve a car
- A button “Rent” selects the vehicle and opens the Rent Form to rent a car
- A button “More Info” selects a vehicle and opens the Vehicle Info Form to view the information for that car
- Close form
#4 Vehicle Information Form
After clicking on the Search icon, the list of vehicles that are available to reserve or rent will display. The More Info button will open the detail information form for that vehicle. Each vehicle on the list has its own button and will display the detail for each selected vehicle as shown below.
#5 Reservation Form
After clicking on the Search icon, the list of vehicles that are available to reserve or rent will display. The Reserve button will open the Reservation form for the user to make a reservation.
How it Works
- The car information such as Vehicle#, Pickup date, Return date, rate per day will be transferred from the Search Form.
- The Undo icon is provided to cancel the reservation or undo any changes.
- After select customer then click Save icon , the information will auto update the Confirmation #, ReservedBy the logged-in user, and reserved date.
- The Confirmation number is the combination of the random letter of two letters in the front and one letter in the back of a ReservedID.
- The Is Closed box will stay open until the Rent icon is clicked to rent out. The user can manually check it if want to cancel the reservation.
- The Note section is provided for the user to put any message regarding this reservation including the reason of cancel.
- If the customer name is not on the list then the user can able to add a new customer after click Yes on a pop-up message.
#6 Rent Vehicle Form
After clicking on the Search icon, the list of vehicles that are available to reserve or rent will display. The Reserve button will open the Rent form for a user to rent out the vehicle.
How it Works
- The vehicle information such as Vehicle#, Pickup date, Return date, rate per day is transferred from the Search Form.
- The Undo icon is provided to cancel the rent.
- Delete icon is used to delete the rent record if created in error. Only the user with an Admin level has an access to delete the record.
- After select a customer then click Save icon , the information will auto update for the Tax rate, Sub-total, Total Rent, Enter date, and Update date.
- Some fields are disabled because they are calculated fields like Tax rate, Sub Total, Total Rent, Total Miles, Begin Mileage, Entered date, and Updated date.
- The Begin Mileage will be auto updated to the Current Mileage of the selected vehicle from the Vehicle table.
- If the Ending Mileage is entered, it will update as the Current Mileage for this vehicle on the Vehicle table.
- If the Ending Mileage is less than the Begin Mileage then an error message will pop-up after the Ending Mileage is entered.
- Update the Rent Status from “Pending” to “Completed” after entering the Ending Mileage.
- After updating the status to Complete or updating the Ending Mileage, the Available status on the Vehicle table will be changed to “Yes”
- Click Save icon also will update the Rent Status to “Completed” if the Ending Mileage is entered and the Rent Status still show “Pending”. This means the program will check if the user forgot to update the rent status.
- The Receipt icon opens the report to view or print the receipt. The example of receipt is displayed below.
Example of a Complete Form after vehicle is returned
Example of Receipt
#7 Find Reservation Form
This form can be access from the Main Menu Form and available for anyone to search for the reservation. The function on this form is listed below.
- Search a reserved car with the criteria from customer name, phone#, or confirmation number
- Clear the criteria/results and show all reservation records
- Provide options to view the result for only opened reserved, close, or all reserved records
- Provide a link to open the Reservation Form on the ReservedID field
- Close form button
#8 Find Customers Form
This form is consisted of two parts; customer information and the rent history for this customer. The user can select the customer name from the drop-down combo box to view/edit the information for that customer.
#9 Search Car Rental Form
This form is available to all users on the Main Menu Form. The user will use this form to search for the rental record. The function on this form is listed below.
- Search a rental record with the criteria keyword for customer name, phone#, rental ID, vehicle #, or pickup date; From and To. The criteria for searching can be a single field or a combination of those fields.
- Clear the criteria/results and show all rental records
- Provide a link to open the Rent Form on the RentalID field. This can be used to find the rental record and update the rental information when the customer returns the vehicle.
- Close form button
#10 Admin Menu Form
This datasheet form provides many tabs for different information to add new data or update the existing data such as add new user, new car, new model, etc. The user with the Admin level only will have an access to this form on the Main form.
#11 Add New Vehicle Form
The user with the Admin level only will have the access to this form on the Main form to add a new car or update the information.
#12 Add New User Menu
The user with only the Admin level will have an access to this form on the Main form to add a new user.
This form includes the function to prevent a duplicate entry of the user name. For example, John Vang is entered for the user name. The pop-up message will indicate the duplicated information. After click OK button it will re-direct to the existing record of this user.
#13 Add New Customer Form
This form can be accessed from the Main Menu form by anyone. There is no restriction for users to add a new customer. If a new customer comes in office and wants to rent a car, the user can start with this form to add information for new customer.
Car Rental Database Template Spreadsheet
This form also includes the function to prevent the duplicate entry of the customer name. For example, John Smith is entered for the customer name. The pop-up message will indicate the duplicated customer name, John Smith. After click OK button it will re-direct to the existing record of this customer as shown below.
Security Level
This Car Rental database also provides a Login Form for the user login. There are two different types of user login/security level; Admin and User. Per a tblUser table below, the UserLogin, Password, and UserSecurity Level are assigned to each user. I set password to 123 for all users on this sample file.
Regular User Login
Per example below, a user “Adam Smith” logged in with a username “asmith.” Adam Smith has a user security level as “User.” After Adam Smith logged in, the Add New Vehicle, Add New User, and Admin button on the Main Menu form will be disabled and displayed as black and white color as shown in the picture below.
Admin Login
Per example below, a user “John Vang” logged in with a username “jvang.” John Vang has a user security level as “Admin.” After John Vang logged in, all buttons on the Main Menu form will be enabled and displayed with color picture as shown below.
Car Rental Database Template Example
Download file contains:
Car Rental Database Template
12 tables, 4 queries, 24 Forms, 1 Reports, and 2 Modules