Using MS Access/Creating a database


Hello. I want to create a database for family owned small car hire company but I'm not sure where to start.We want to build a simple database of basic customer details.
Specifically, we want for each customer's name to take us to a separate window/table with all the car reservations of each customer e.g id, date, type of vehicle, amount paid.
I have created a basic table with customers name and id but how do I create a second table that links to it when I click customer's name?
I have watched some online tutorials but I'm still not sure how to do this.

I'm using Microsoft Access 2007 in windows 7 btw...

Hi Marina,

What you are about to create is a Mini CRM Software. CRM=Customer Relationship Management for a Car Rental Service.

You will need the following table[s] and not just one.

Master Tables:
1.Customers (To record New Customers and maintain their contact details)
-Create a CustomerCode field
-Customers Name and contact details
-Customers Passport details
-Customer License and registration details

2.Employees (To record info on New and Existing employees)who will be responsible for renting out vehicles to customers
3.Vehicles (To maintain a list of all the different vehicles you rent along with their photo)
- You also need to have fields such as Vehicle Tarrif Plan as per its Make/Model or kilometers driven (Which you will term as Cost of Renting)

Transaction table: (This is the table where all reservation information is recorded)
1.Car Reservations Transaction Table
-Reservation OR Date of Rental
-Time when rental (This field is required if you rent on hourly basis)
-Car registration no
-Customer Renting it (Add the CustomerCode) field here
-Advance paid by Customer
-Days or hrs required to be rented
-A Total amount field which should automatically calculate how much will be the rental cost if the vehicle is hired on hourly or daily basis

Query and Alerts: (This is required to Alert you if any Vehicle has not returned to you as per its rental time frame)

Reports: (All reports for you to track how you control your business)

Now to come to your Point on your question "we want for each customer's name to take us to a separate window/table with all the car reservations of each customer"

For this to happen make sure you have the below:
1.In Customer master table you must have a CustomerCode field which is a Primary key
2.This same field should be present in the Car Reservations table, but it will be a foreign key which allows you to create multiple transactions for the same customer. So just remove the restriction from "No Duplicates" to "Yes,Duplicates OK".

After the above you need to create a Continues Form based on a Query which list all Customer names.
On this form you will have to create a new button next to the customer name field and that buttons event should open ONLY THAT selected customers reservation record which you have currently selected.

-So create your new form based on the customer list query
-Save that form
-Next to the Customer name field using the AutoWizard feature from the Toolbox click on the create button icon.
-Choose the form you need to open when this button is clicked "At this stage choose the "Car Reservations Form" which you created earliers
-After that select the feature to Open this "Car Reservations form" with the value you select from the query list.
-Now is the main part where you link the "CustomerCode" has to be identified as the common field to be linked between the Customer List query and the Car Reservations form.
-Choose CustomerCode field from both the tables as the common field to be linked
-Save your exit from the wizard

Run the Customer List Form
- Click on the new button which you created and it should open all the reservations performed earlier for that customer

Hope this helps, I have designed a Car Rental and a Driver Rental CRM for such kind of Service industry hence I know the ins'and out's of these concepts.

Best regards
Manish Batola  

Using MS Access

All Answers

Answers by Expert:

Ask Experts


Manish Batola


ALL MICROSOFT ACCESS DESIGN AND Development QUESTIONS which occur in a Access Projects Lifecycle. Can offer constructive help on Designing Advanced MS-ACCESS Applications such CRM/FRM(Follow-up),HSE-Health,Safety & Environmental Applications,School Management Systems,Hotel Software, Hospital Software,Chemicals Indenting,Import/Export,MRP - Manufacturing Resource Planning, ERP - Enterprise Resource Planning, Automotive Management Systems, Business Intelligence for Oil & Gas Drilling Operations, CRM,KPI- Key Performance Indicators,HealthCare, Hospitality, Constructions, Loyalty Clubs, Call Centers, Travel & Tourism, Educational Institutions, Industrial Strength Invoicing and many more being added every year.


29+ yrs in the Software industry with 23yrs completely dedicated on more than 350 Various Microsoft Access Projects from different Industry Sector[s]. I'm using Access ever since it was born.

Worked in various multinationals catering from Aerospace, Oil & Gas etc.

-BSc - Computer Science -MSAP (Master in Software & Applications Programming) -BPRE (Business Process Re-engineering Engineer) -SQAE (Software Quality Assurance Engineer) -STIE (Software Testing & Inspection Engineer) -Project Engineer (Software Development)

Past/Present Clients
Catered to various clients in the following industries • Oil & Natural Gas (BPM/CRM/Access Development) • Sales / Marketing (CRM) • Automotive (Automobile Dealers) • Logistics & Indenters (Import/Export) • HealthCare / Hospitals • Hotels 3* & 5* • Restaurants • Construction Co’s (Project Billing) • Clubs (Concept Selling) • Shoe-Chains • Solicitors/Lawyers • Call Centers • Aerospace (Preventive Maintenance) • Travel & Tourism • Computer Vendors • Manufacturing • Music Shops • Education • Data Warehousing • Health,Safety & Environmental Applications • Mud Engineering • School Management Software

©2016 All rights reserved.