René Fürst
By René Fürst Last updated on Shown in: Microsoft Access 2016

A limousine service thrives on drivers, vehicles, customers and trips being properly assigned at all times. In this lesson, you will build exactly this foundation in Microsoft Access. You will create four tables: Driver, Trip, Car and Customer. Along the way, you will learn how to set primary keys, choose suitable data types and name fields so that they are easy to understand later on.

Better in Access: Become a data hero - 4.1 Create our limousine database

The video is only loaded from YouTube when you play it – data (including your IP address) is then transmitted to YouTube/Google. More in the privacy policy

You can rebuild everything I show you in this tutorial yourself. Alternatively, you can download the finished Access file from the download and compare it with your result. However, I recommend that you work along step by step, because that is the only way to understand why each decision is made.

Key Takeaways

  • Every table needs a unique primary key, usually in the form of an AutoNumber.
  • The data type determines which values a field can store and whether you can use it for calculations.
  • Clear field naming, for example with prefixes like "Personal" or "Kunde", prevents confusion.
  • Foreign keys must have the same data type as the primary key of the linked table.
  • Sensitive data such as salary belongs in a separate table.

Step-by-Step Guide

1. Overview of the Table Structure

Before you click around in Access, you should know what you are actually building. The database consists of four tables. The Driver table contains all the information about your chauffeurs, the Trip table stores individual tours, the Car table describes the vehicle fleet and the Customer table holds the data of your clientele. By the way, you can also call the trip a tour, as long as you name it that way consistently. What is important is that you later connect the tables to each other via keys.

Create an Access database: tables for your limousine service

2. Create a New Blank Database

Start Access and select a new blank database. Assign a file name right away and confirm the entry. Access then creates an empty database with a first table. Whether you save the database in the Documents folder or on a drive of your choice makes no difference for the process. Just make sure you can find the storage location again later.

Creating an Access database: Tables for your limousine service

3. Create and Name the Driver Table

First, save the table under a clear name. For this I use Hungarian notation, that is, a prefix that identifies the object type. The table is therefore called tblFahrer. This notation ensures that you can see at a glance that it is a table, and that helps you especially in larger projects.

Creating an Access database: Tables for your limousine service

4. Define the Primary Key for the Drivers

When creating the table, Access automatically generates a field called ID with the data type AutoNumber. This field is ideal as a primary key because it assigns a consecutive unique number to each record. You can keep the ID or additionally create your own personnel number. In the tutorial, I rename the field to Personalnummer, keep the AutoNumber and set an incremental value as a property. This way it keeps counting automatically, and you have the personnel number directly as the primary key.

In addition, fill in a short note in the field description, for example "Personnel number of the driver". This is especially helpful with larger teams.

Creating an Access database: Tables for your limousine service

5. Save the Database as an Access File

Before you continue working, save the entire database in the latest Access format. To do this, use the menu item "Save Database As" and select the current file version. Access prompts you to close all objects. Confirm the prompt and choose a file name, such as Limousinen Datenbank. Afterwards, you re-enable the content and can continue working with the table.

Create an Access database: Tables for your limousine service

6. Create Fields for First Name and Last Name

Next, create the field for the first name and select the data type Short Text. For names, this option is usually completely sufficient. Long Text would only make sense if you want to store very extensive free text. Then create the last name, also as short text.

7. Clearly name fields with a prefix

Since the database will later also contain customer data with first and last names, more precise naming is worthwhile. Instead of just "Vorname", you write "PersonalVorname" or "Personal_Vorname". This way you immediately recognize that the field refers to the driver. The same applies to street, postal code, and city. This takes you a moment, but later prevents mix-ups when creating queries and forms.

8. Set the postal code as a number

In Germany and Austria, the postal code usually consists of digits, so you create the field as a number. Make sure that you do not enter anything in the field property for the default value. Otherwise, a default value of zero would automatically enter a zero for every new record if you forget the postal code.

9. Store the telephone number as text

You should create the telephone number as short text. This way you can store spaces, slashes, and plus signs without any problems. With numbers, these characters would be lost or displayed incorrectly.

10. Deliberately leave out the salary

The salary does not belong in the driver table. Not everyone who works with the database should see it. Therefore, you will later move salary information into a separate, protected table. Then save the table and open the datasheet view. Now you can already enter the first driver records.

11. Create the Fahrt table in design view

Now create a second table using the table design. Name it tblFahrt. By default, Access creates an ID again. For the trips, however, you want to use your own trip number as a unique identifier. You can leave the ID as an AutoNumber, but it will then not become the business identifier of the trip.

You create the trip number as a number. Text would also be possible, but a number is sufficient for this purpose.

12. Add the foreign key Personalnummer

So that the trip knows which driver is carrying it out, you need a field Personalnummer in the table tblFahrt. This field is the foreign key. It is important that the data type exactly matches the primary key in tblFahrer. There, the Personalnummer is an AutoNumber of type Long Integer. So you also create the data type Number with the field size Long Integer in the foreign key. For better distinction, you can additionally name the field "FahrPersonalnummer".

13. Set date, time, and price

For date and time, you choose the data type Date/Time. The price is stored as a number. In addition, you set the property "Required" to Yes so that no price can remain empty. A price of zero should also be excluded, because this usually represents an error.

14. Add addresses, duration, license plate, and seats

You create the start address and the destination address as short texts. You store the duration of the trip as a number, without decimal places. The license plate of the vehicle used is also a short text, because it can contain letters. You create the number of booked seats as a number. If you additionally want to record remarks, choose long text for this field.

Then save the table as soon as all fields are present.

15. Create the Auto table with a premium flag

In this case, the table tblAuto does not need an ID in the classic sense, at least not necessarily. You first create the license plate as short text, then the number of seats, also as short text or as a number, depending on preference. The model of the car is stored as short text. The year of manufacture is a number for which you enter nothing as the default value for now.

A special case is the Premium property. For this you use the data type Yes/No. With this Boolean value you can control calculations later. If a vehicle is a premium car, the price of the trip can be adjusted automatically, for example doubled.

16. Choose a primary key for the car table

When saving, Access asks for a primary key. You could use the license plate as the primary key, since it is unique. However, a license plate can later be assigned to a different vehicle, and then problems arise. Therefore, an ID with AutoNumber is the safer choice. Create a field ID with the data type AutoNumber and mark it as the primary key. Afterwards, you can move it to the top position of the table via drag and drop so that the primary key appears first.

Then save the table and switch back to the view to check the result.

17. Create the Kunde table following the same pattern

The structure of the customer table largely corresponds to the driver table. Create a new table in design view and name it tblKunde. The first field is the KundenNummer with AutoNumber and primary key. Writing in CamelCase or PascalCase, that is, without spaces and with capital initial letters, helps you with later work on queries.

After that come the fields KundeVorname, KundeNachname, KundeStraße, KundePLZ as a number, and KundeOrt as short text. You store the phone number as short text, just like with the drivers. The date of birth is given the data type Date/Time. To mark a regular customer, you use Yes/No. You can set the default value to No, so that new customers are initially not regular customers.

18. Check the design and prepare for relationships

Save all four tables and switch to the view in each case to check your structures. Make sure that every primary key is unique and every foreign key has the matching data type. Only when this foundation is right can you create the relationships between the tables. That is exactly where the next lesson picks up.

Summary – Creating a Limousine Database in Access: Structuring Drivers, Trips, Cars, and Customers

In this guide, you have built a complete basic structure for a limousine service in Access. The tables Fahrer, Fahrt, Auto, and Kunde form the foundation for all further analyses. You have learned to define primary keys with AutoNumber, to create foreign keys with a matching data type, and to name fields with clear prefixes. Equally important is the separation of sensitive data such as salary. With this clean structure, you can create the relationships between the tables in the next step.

FAQ

Do I absolutely have to create the table Fahrer with an ID?No, you can also rename the field ID to Personalnummer; it just has to serve as a unique AutoNumber primary key.

Why is the license plate not recommended as the primary key of the car table?A license plate can later be assigned to a different vehicle, so an ID with AutoNumber is more reliable.

Why should the phone number be stored as text?That way, spaces, slashes, and plus signs are preserved, which would be lost in a number field.

What happens if the data type of the foreign key does not match the primary key?The link between the tables then does not work or cannot be created at all.

Why is the salary not stored in the driver table?Salary data is confidential, so you move it to a separate table so that it is not displayed to all users of the database.

René Fürst
Trainer About René Fürst Passionate IT consultant! I have been working in IT for over 16 years and have been confronted with a wide variety of tasks. All courses by René Fürst