Others

How to Design a Microsoft Access Form for Multiple Pickups and Drop-Offs

Chanuka GeekiyanageChanuka Geekiyanage Nov 2, 2026 869 views

Question details

The user needs to create a transportation database form that allows data entry for an unlimited number of pickup and drop-off locations per trip to use in reports, cost calculations, and invoices.

How to Design a Microsoft Access Form for Multiple Pickups and Drop-Offs
Product
Microsoft Access
Device & OS
not provided
Scenario
Designing a scalable database form to track trip logistics without arbitrarily limiting the number of stops a user can enter.
Observed behavior
The goal is to implement a relational data structure that dynamically handles multiple sub-items (stops) for a single parent item (trip) rather than using static arrays.
Before you start

Before modifying your database structure, ensure you have backed up your Microsoft Access file and mapped out the exact fields required for your trips and individual stops.

Solution 1Recommended

Implement a Relational Model with a Child Table and Subform

Use a parent table for the main trip details and a related child table for the stops, displayed dynamically through a subform.

In a relational database like Microsoft Access, you do not use dynamic arrays or linked lists to handle multiple items. Instead, the standard approach is to use a one-to-many relationship. This allows your form, reports, and invoices to process any number of related records effortlessly.

1
Create the main trips table

Design a parent table (e.g., tblTrips) with a Primary Key (TripID) to store overall trip information such as the date, assigned driver, and vehicle details.

2
Create the child stops table

Design a child table (e.g., tblStops) with its own AutoNumber Primary Key (StopID) and a Foreign Key (TripID) to link it to the main trip. Add necessary fields like StopType (Pickup or Drop-off), Address, and Time.

3
Establish the table relationship

Navigate to the Database Tools tab, select Relationships, and drag the TripID from tblTrips to tblStops to create a one-to-many relationship. Ensure 'Enforce Referential Integrity' is checked.

4
Build the data entry form

Use the Form Wizard to create a main form based on tblTrips, and insert a Subform based on tblStops. This setup will display the trip details at the top and provide an infinite scrollable list at the bottom to add as many pickups and drop-offs as needed.

Implement a Relational Model with a Child Table and Subform
Scalable Design: This subform method behaves exactly like adding detailed line items to an invoice, providing a flexible and scalable user experience.
Free Microsoft Office alternative

Manage Trip Data and Invoices Easily with WPS Office

If building complex relational databases in Microsoft Access feels overwhelming, you can easily track trips, multiple drop-offs, and generate invoices using powerful spreadsheet templates. WPS Office provides a free, highly compatible alternative to Microsoft Office, featuring robust tools for all your logistics needs without the steep learning curve.

  1. 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and launch WPS Spreadsheet.
  2. 2. Set up a trip tracking template: Create a new workbook with columns dedicated to Trip ID, Driver, Stop Type (Pickup/Drop-off), and Location Address.
  3. 3. Automate calculations: Utilize built-in formulas and PivotTables to summarize costs, group stops by trip, and generate printable invoices easily.
Fully compatible with Microsoft Excel formats (.xlsx, .xls) for seamless trip log management and data migration.Free and lightweight suite encompassing Spreadsheet, Writer, Presentation, and PDF tools.Built-in spreadsheet functions (VLOOKUP, SUMIFS) for calculating trip costs and mileage without complex database design.Familiar tabbed interface requiring zero learning curve for existing Office users.
microsoft office alternative - wps office

Frequently Asked Questions

Can Microsoft Access automatically calculate total mileage for multiple addresses like Google Maps?

Microsoft Access does not have native, out-of-the-box mapping tools to calculate driving mileage automatically. However, you can write VBA (Visual Basic for Applications) code to connect to the Google Maps API or Bing Maps API, which can fetch distance data based on the addresses entered in your form and store the results in your table.

Do I need to use programming arrays to store unlimited stop locations in Access?

No. In relational database design, you do not use arrays or linked lists to store multiple items. You achieve this by creating a separate child table for the locations and linking it to the parent trip table via a foreign key.

How do I easily distinguish between pickups and drop-offs in the same subform?

You can add a 'StopType' text field to your child table (tblStops). On the subform, format this field as a Combo Box (dropdown menu) that provides two predefined choices: 'Pickup' and 'Drop-off'. This ensures consistent data entry.