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...
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.
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.