Hello Everyone,
We provide transportations, mostly for people. Most of them are recurring on 3x days a week, 5x days a week, 6x days week etc... Some are fixed trips occur only on specific date. So I have set up the below tables.
tblSchedule - This is the main schedule table.
TripID (AutoNumber)
CustomerID (Long)
StartDate
EndDate (Entered a future date, will update once a customer closed)
From (location)
To (Location)
Price
PaymentAs (Monthly or Per trip. So the entered Price is for this type)
Status (Open, Closed, Cancelled)
tblReservation - We generate schedules for the next week (Only for a week) on weekends and store them in this table.
ReservationID (AutoNumber)
TripID
TripDate
VehicleID (Driver details also in this ID)
Status (Completed, Scheduled. Default Scheduled. Removes record if customer cancelled for a specific date.)
So the question is; how can I track their payments? Customers will have fixed trip(s) or recurring trips ongoing for months/years. If I select/enter a month, how can I see who has paid/pending for their trip(s)? Some may pay partial or advance payments.
I tried to create the below table.
tblPayments
PaymentID (AutoNumber)
TripID (FK)
PayMonth (Used 1st day of the payment month: 8/1/2023, 9/1/2023, 10/1/2023 etc...we use mdy format)
AmountReceived
DateReceived
Is this a good approach? If so how can I add all Open TripID to this table? If not could you please advice me?
Thank you for your time.
We provide transportations, mostly for people. Most of them are recurring on 3x days a week, 5x days a week, 6x days week etc... Some are fixed trips occur only on specific date. So I have set up the below tables.
tblSchedule - This is the main schedule table.
TripID (AutoNumber)
CustomerID (Long)
StartDate
EndDate (Entered a future date, will update once a customer closed)
From (location)
To (Location)
Price
PaymentAs (Monthly or Per trip. So the entered Price is for this type)
Status (Open, Closed, Cancelled)
tblReservation - We generate schedules for the next week (Only for a week) on weekends and store them in this table.
ReservationID (AutoNumber)
TripID
TripDate
VehicleID (Driver details also in this ID)
Status (Completed, Scheduled. Default Scheduled. Removes record if customer cancelled for a specific date.)
So the question is; how can I track their payments? Customers will have fixed trip(s) or recurring trips ongoing for months/years. If I select/enter a month, how can I see who has paid/pending for their trip(s)? Some may pay partial or advance payments.
I tried to create the below table.
tblPayments
PaymentID (AutoNumber)
TripID (FK)
PayMonth (Used 1st day of the payment month: 8/1/2023, 9/1/2023, 10/1/2023 etc...we use mdy format)
AmountReceived
DateReceived
Is this a good approach? If so how can I add all Open TripID to this table? If not could you please advice me?
Thank you for your time.
Last edited: