Use Join Table to insert related data into new layout?
Wondering if anyone can help me or point me in the right direction for a wee problem I am having.
I'm building a database for our car hire company. I have customer_details linked to a booking table via a renter_driver join table
The renter_driver join table has it's own primary key, two foreign keys to link to the other tables and a 4th entry that defines the role of the customer in the booking. A customer can be one of five roles:
1 Renter/Driver = a person that pays for the rental and is the main driver
2 Renter = a person or company that pays for the rental but does not drive.
3 Driver = this customer doesn't pay for the rental, someone else does, but they are the main driver
4 2nd Driver = an additional driver
5 3rd Driver = an additional driver
I have a portal set up on the booking layout that links to the join table and allows me to select the customer info from the customer_details table and assign their role as required. What I want to do is to use these categories to pull through and print the name, address, licence number etc. from the customer_details table on particular sections on a rental agreement, which the remainder of the information is from the booking table.
i.e. Renter/Driver info would go to an invoice address, payment info and driver details, Renter info would only refer to invoice address and payment info, Driver would only appear in the driver details section, 2nd & 3rd Drivers would appear on a separate layout as required.
Hopefully all of this makes sense!
I have tried to perform calculations but I can't seem to pull the info through, is a script a better tools for the job?
If anyone has any suggestions, solutions to pointers that would be fantastic!
Thanks for reading,