[Solved] To make report with two tables

[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/28ih77dk5 ... GISTER.odb". sincerely wait for the reply.

[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)

[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.

[robleyd]

Try placing the WHERE clause immediately before the ORDER 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

select *
from "Test_register" TR
join "Test_data2" TD
on TD."Test_register_ID" = TR.ID
where TR.ID = :PleaseInput_ID

[drp_61]

Thanks. I have managed to solve my problem. Thanks once again.