You are going to have multiple problems getting results. Just of note, one of the tables mentioned in you question was “Invoice” but I believe it is actually “Job Description” in the sample provided.
What you want to end up with is three record types for each invoice. Each record would contain “Job Description”. “Customer”, and then “Labor”, “Materials” or “Rental” appended. With that as input to Report Builder, you would have a report header with customer/job information, a group section for Labor type records, a group section for Materials type records, a group section for Rental records and finally a report footer for Totals.
Records can be connected together using a SQL JOIN statement and the three groups brought together using SQL UNION statements.
Now for some of the problems.
You don’t specify, but if using the vanilla Base in LO, the database is HSQLDB v1.8 and it is difficult to deal with multiple JOINS and UNIONS. If using a split database (HSQLDB v2.x or higher) or probably most other external DB’s (MySQL, PostgreSQL etc.) you should be able to do this in one large query.
The next problem is consistency. Your fields are not consistent across the “Labor”, Materials" and “Rental” tables. Some tables use decimal fields and some use varchar.
In the SQL, the UNION statement will require “Same” field names in the records. You can accommodate this with “Select xxx AS yyy”. For the “Labor” records, again in the SQL, you will need to create some dummy fields because there is inequality in number of fields between the three tables - “Labor” vs “Materials” and “Rentals”.
It may sound like a lot but if you understand SQL somewhat, it shouldn’t be hard, just some time involvement. If you don’t understand SQL, it is pretty important to do so working with DB’s. There are many aids available such as this one - click here.
EDIT:
Just to give you a starting point, I created a partial invoice containing just “Materials” & “Rentals” records and all the design has limited fields placed only so you can see the result. You can run report SampleReport and then elaborate from there. The SQL for the query is the key for this. I did this quickly without much in the way of naming standardization so bear with it.
SQL- first created “Query_Materials” selecting all fields but adding RecordType field and changing Material ID to just ID. Did the same for Rentals resulting in “Query_Rentals”. Did a UNION of those as MaterialsRentalUnion.
“Query_Materials” & “Query_Rentals” are NOT necessary for this process. They are in fact the basis for the statements in MaterialsRentalUnion. Joined “Job Description” and “Customers” as JobCustJoin. Finally Joined “JobCustJoin” with "MaterialsRentalUnionresulting inJobCustMaterialsRentalquery` which is input to the report.
The unfinished part is to add in the “Labor” table which should be creating a record as Materials & Rental then adding it to the Union. Of course then to complete the necessary fields to be displayed on the report as well as the totals.
Sample - Invoice.odb
