[Solved] To make report with two tables

Discuss the database features

[Solved] To make report with two tables

Postby drp_61 » Thu Mar 23, 2017 12:34 pm

I am having hsql 2.3.4, using Villeroy's method of split database. Only two tables which are 1:n relationship connected. In report i want to include both the tables. How to do since all help says data type must be same when using union quarry. "http://www.mediafire.com/file/28ih77dk5xd90tr/TEST_REGISTER.odb". sincerely wait for the reply.
Last edited by Hagar Delest on Sun Mar 26, 2017 6:20 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.6 windows 10 enterprise, 64-bit and Libreoffice 6.1 windows 10 enterprise, 64 bit
drp_61
 
Posts: 61
Joined: Fri Feb 25, 2011 9:05 am

Re: To make report with two tables

Postby eremmel » Thu Mar 23, 2017 12:46 pm

For a report you should JOIN the parent with the child table and use grouping functionality in the report to list the children per parent. Compare to a form (parent) with sub-form (children)
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1035
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: To make report with two tables

Postby drp_61 » Sat Mar 25, 2017 7:03 am

I managed to write join quarry as - SELECT "Test_register"."ID", "Test_register"."Date", "Test_register"."Motor", "Test_register"."Pump", "Test_register"."Controller", "Test_register"."Tested_on", "Test_register"."Book_no", "Test_register"."Page_no", "Test_data2"."Vdc", "Test_data2"."Idc", "Test_data2"."W", "Test_data2"."Eff", "Test_data2"."Head", "Test_data2"."Discharge", "Test_data2"."Time", "Test_data2"."Freq", "Test_data2"."Rad" FROM "Test_register" RIGHT JOIN "Test_data2" ON "Test_register"."ID" = "Test_data2"."Test_register_ID" ORDER BY "Test_register"."ID" - which is running successfully, but report brings all the ID data in a page. I want to input ID number and based on that quarry to produce report. How to acheive this - not able to place where commend in right join qarry.
OpenOffice 4.1.6 windows 10 enterprise, 64-bit and Libreoffice 6.1 windows 10 enterprise, 64 bit
drp_61
 
Posts: 61
Joined: Fri Feb 25, 2011 9:05 am

Re: To make report with two tables

Postby robleyd » Sat Mar 25, 2017 8:05 am

Try placing the WHERE clause immediately before the ORDER BY
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2717
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: To make report with two tables

Postby chrisb » Sat Mar 25, 2017 8:19 am

drp_61,
i downloaded your database from Mediafire but unfortunately the folder called 'database' which contains all table/definition data is missing.

the easiest way to select a specific record/records is to use a parameter query. give the code below a go.
Code: Select all   Expand viewCollapse view
select *
from "Test_register" TR
join "Test_data2" TD
on TD."Test_register_ID" = TR.ID
where TR.ID = :PleaseInput_ID
open office 4.1.6 & LibreOffice 6.0.7.3 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 178
Joined: Mon Jun 07, 2010 4:16 pm

Re: To make report with two tables

Postby drp_61 » Sat Mar 25, 2017 8:54 am

Thanks. I have managed to solve my problem. Thanks once again.
OpenOffice 4.1.6 windows 10 enterprise, 64-bit and Libreoffice 6.1 windows 10 enterprise, 64 bit
drp_61
 
Posts: 61
Joined: Fri Feb 25, 2011 9:05 am


Return to Base

Who is online

Users browsing this forum: No registered users and 1 guest