Page 1 of 1

[Solved] To make report with two tables

PostPosted: Thu Mar 23, 2017 12:34 pm
by drp_61
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.

Re: To make report with two tables

PostPosted: Thu Mar 23, 2017 12:46 pm
by eremmel
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)

Re: To make report with two tables

PostPosted: Sat Mar 25, 2017 7:03 am
by drp_61
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.

Re: To make report with two tables

PostPosted: Sat Mar 25, 2017 8:05 am
by robleyd
Try placing the WHERE clause immediately before the ORDER BY

Re: To make report with two tables

PostPosted: Sat Mar 25, 2017 8:19 am
by chrisb
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

Re: To make report with two tables

PostPosted: Sat Mar 25, 2017 8:54 am
by drp_61
Thanks. I have managed to solve my problem. Thanks once again.