Filling a report from to different SQLite tables.

Databases
1

I have a SQLite database with 8 different tables.
In the page header of a report, I like to have the fields from the Order table.
In the body I like to have the fields of the Part table.
For both I need a separate SQL statemen like:

SQL=”SELRECT * FROM Order WHERE Number=”+Str(Number)+” ;”
And for the other fields:
SQL=”SELRECT * FROM Part WHERE Number=”+Str(Partnumber)+” ;”

But I can run only one report.

Dim Order As RecordSet Order=DBase.SQLSelect(SQL) Dim ps As New PrinterSetup If ps.PageSetupDialog Then Dim g As Graphics g = OpenPrinterDialog(ps, Nil) If g <> Nil Then If rpt.Run(Order, ps) Then rpt.Document.Print(g) End If End If

2

Typically two tables like this are related in some way, so you can join them to get all the data combined into a single SQL query. A join looks something like this (not accurate since I don’t know your DB schema):

SELECT * FROM Order
INNER JOIN Part ON Order.Number = Part.OrderNumber
WHERE Order.Number = ?
AND Part.Number = ?
3

@Bob Keeney, @Ulrich Bogun, and others have also been using SQL Views to accomplish something very similar. Here’s a thread: https://forum.xojo.com/5264-sqlite-views

4 
SELECT a.*,b.*
   FROM ORDER a, PART b
WHERE a.number=b.number
    AND a.number=STR(number)

The select part needs to be altered to name the unique names (ie. NUMBER is ambiguous in this example)