Combining Multiple Tables
I have the following tables:
There's a 1 to many relationship between clients and jobs, a 1 to many relationship between jobs and lines, and a 1 to many relationship between jobs and tasks. Shipments is populated through a script, and combines both lines and tasks (if they involve shipping).
I'd like to be able to generate a report for the day's shipments. It would also need to combine lines and tasks, but I don't want to base the report on the shipments table, because I'd like to combine multiple items going to the same address into one record, so they show up together on the report. I was considering creating a table based on due date and address, related to the shipments table, but it felt a little clunky; I was worried that the shipping address would update without updating the corresponding record in the related table, unless I added a ton of scripting. Just wondering if there's a more elegant solution out there?