Query all tables (some blank)

Hello,

I’m making an invoice that has 5 different tables all related by an invoice_ID.

  1. Invoice
  2. Customer
  3. Labor
  4. Materials
  5. Rental Equipment

A typical report will have 1-3, but 4 and 5 can be optional.
Ex.

  • A. My first invoice has 1-3 filled
    out and 2 items on 4 and 1 item on 5
  • B. My second invoice has only 1-3
    filled out
  • C. My third invoice has
    1-4 filled out.
  • D. My fourth invoice
    has 1-3 filled out and 4 items on 4
    and 2 item on 5

I created a query that has all tables selected but only the rows with details accross all tables (A and D). How can I query all data from each table regardless of their contents?

My goal is to have (per invoice) all labor in a table, followed by all materials in a table, followed by all rental in a table.

Unfortunately, I can’t attach any files so I can’t give any example of what I’m looking for or what I have.

Gave Karma for attachment or image. Please attach.

Thank you very much!

My current example database:
Example_Database_Invoice.odb(/upfiles/1488696827520279.odb)
Here’s my vision:
Example.png

Welcome Bryverine. First, in the future please update your original answer, rather than provide an answer to this question. Also your links above both point to the .png file which looks good, but neither point to your .odb file.

@Bryverine please edit you answer with correct link to .odb & will then move to your question. If you see Edit along bottom line of question use that.

I’m so sorry, I think I figured it out!
@EasyTrieve and @Ratslinger.

Here is the correct path for the .obd
Example_Database_Invoice.odb

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

Great, I think that helps a lot! It’s my first time using a database so I really appreciate the help.

The database will be for someone else with even less experience with databases so I was looking to make this a plug-and-chug kinda thing. I’ll give it a try and see if this gets me what I needed. I’ll be sure to mark as answered if it does!

Taking a second look at this right now. One thing I forgot, for each of the three table records an identifier needs to be added in the SQL - “RecIdentifier” = “Labor” for labor records. This is then used in Report Builder for record grouping. Trying now to put together very quick & dirty example with “Materials” & “Rentals” (easiest part). Will post if completed.

See my answer for a sample and brief explanation.

Hopefully you realize this was a real Q & D job just to get you a proof of concept. This could certainly be better especially in the area of SQL although HDQLDB v1.8 has its’ limits. Don’t use Report Builder often and screwed up a couple of times but I think it represents what you are looking for.

@Ratslinger OH MAN this was a great help!
Next question (still part of the formatting report) is as follows: Can I display a cell dependent on the “RecordType”. I.e. display “rate” instead of “cost” for “Labor”.
And final question: I made a cell that multiples qty and cost for each record type. How can i total all of these cells (per record) into one final cell in the RecordType Footer?

Funny, I don’t remember seeing anything about formatting in your question - just query & grouping! You should get many answers from the LO docs - click here.

The group titles are just label fields. Without macro coding (not getting into that here) you may need to be creative - “Rate/Cost” or “Amount*” with reference to * in footer.

Totaling is another story. Had an answer I posted abt 18 mos. ago in another forum but it is closed & lost the info. There are some samples in the doc post I just mentioned in last comment. You can download samples used in book. Also see this post - click here. Again, I don’t use Report Builder except for a few occasional times.

Here is another sample & brief explanation of totals - see my answer in this post - click here.

@ratslinger ah, well I suppose the formatting was part of my goal of the report shown in my png attachment… so technically, no I guess I didn’t specifically ask that. :confused:

Thank you for your help this is exactly what I needed. I appreciate all your help!