Base report from linked tables

[jvdw]

In made a database which (simplified), contains a main table A. Other tables, say B, C, and D contain data and are related 1-∞ to A, by means of a simple foreign key.

The structure of B,C and D is not homogeneous and do contain different fields.
Users may choose to add 0, 1, or many records in table B. The same is true for C and D, and the number of entries in B, C and D need not be equal.

Now, I want to prepare a report based on a selection of records in table A. The report should sum

• heading

selected record in A

• detail

-all related records in B

-all related records in C

-all related records in D

As far as I am aware, Base reports are based on a single table or view. I made a table (using outer joins) that contains all information in tables A-D. This is of course a very ugly table, as is contains many empty cells. I have not found a way to make a nice report out of this table.

Could anyone help me with a solution or workaround?
Regards, jvdw

[Ratslinger]

Hello,

There is no real question here. All it states is how do I make a nice report. Beauty is in the eye of the beholder. How can anyone even begin to help when you don’t explain what the actual problem is? What about possibly some screen shots of what you have and what you actually want?

You must write a question with the understanding that a person trying to answer has only what is written as a basis. ‘I have an “ugly” table. How do I make a nice report?’ will get you zero answers.

[jvdw]

Sorry if I am not clear enough. This is not about beauty. I tried to explain what i have (4 tables) and what I want (the report structure outlined in the middle section of my post) The question is whether there is a principle to list in the ‘detail’ section first all items in table B, then all items in C, etc. The actual looks of the table are of later interest.

[peterwt]

You can base a Report on a Query if you have multiple Tables to include in the a Report.

[Ratslinger]

Hello,

Based upon your comment, The answer is simple. Printing of the report is based upon the input it receives. Therefore, if you want the report to print as:

Record Type A
    Record Type B
    Record Type B
    Record Type C
    Record Type C
    Record Type D
    Record Type D

Then the input (Query probably) needs to be in the proper sequence:

Record Type A with appended Record Type B
Record Type A with appended Record Type B
Record Type A with appended Record Type C
Record Type A with appended Record Type C
Record Type A with appended Record Type D
Record Type A with appended Record Type D

When you generate the input file, insure there is something to differentiate the record types. With this you can group on record type A and on subsequent types for B, C & D.

This can result in something like:

if you can imagine Record A is the customer, and B & C records the line items.

Edit:

From past experience, I see the question in your comment as only the first in a list. To alleviate that, here is a sample. It is based upon only three tables (not the four you have) and contains a sample report generated from them.

Sample - CreatingReport.odb

A few items you should note:

I only used three tables because of the amount of time needed for this type of reporting. You should be able to create yours based upon the sample.

I used a View instead of a Query (almost the same) because I forgot Report Builder doesn’t like certain types of SQL.

Not a great deal of testing was done. There could be other items which need correcting and/or attention.

More info on Report Builder is available in the LO docs - posted here.

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[jvdw]

Thanks for your quick reply. I’m still struggling though. If you say ‘appended by’, does that mean a query joining tables by an ordinary link? Can this be done if my tables BCD are very different, say B has four text fields, and D has three text fields and three numeric fields.
I struggle to fit all the data into one query
Thanks again,
jvdw

[Ratslinger]

@jvdw To answer your question, please see edit in answer.

[jvdw]

This is exactly what I need. Worked with if then statements 100s of times before, but have never applied them in reports before. I will implement this for my own application. Thank you Ratslinger!