Reports from more than one account?

[Lewis Balentine]

You can instructions for using SQLite to extract data (create reports) 
on my web page for GNUCash stuff:
http://www.keywild.com/gnucash/

I have added a section for joining tables and running JOB reports ... 
however at this point it only contains the SQLCode.
There is also a sample database named 'bogus-sql3.gnucash' that includes 
a few JOBS records.

The following SQLite3 code has been tested and verified:

-- list all fields for all jobs
select * from jobs;

-- list the job id of active jobs
select distinct owner_guid from jobs where active !=0;

-- list customer's id, company and contact name for customers with 
active jobs
Select customers.id as "Cust ID", customers.name as "Descr", 
customers.addr_name as "name"
from customers
where customers.guid in (select distinct owner_guid from jobs where 
active !=0);

-- implicityly join customers and jobs tables
Select customers.id, customers.name, customers.addr_name, jobs.id, 
jobs.name, jobs.reference
from customers, jobs where customers.guid = jobs.owner_guid
and customers.guid in (select distinct owner_guid from jobs where 
owner_type=2);

-- List all active customer job data ordered by the job number
Select
jobs.active, jobs.id as "Job No",
jobs.name as "Descr", jobs.reference "Ref PO",
customers.id as "Cust Id", customers.name as "Dscr", customers.addr_name 
as "Name"
from customers, jobs where customers.guid = jobs.owner_guid
and customers.guid in
(select distinct owner_guid from jobs where active !=0
  and owner_type=2
)
and jobs.active !=0
order by jobs.id;

-- List all active vendor job data ordered by the job number
Select
jobs.id as "Job No",
jobs.name as "Descr", jobs.reference "Ref PO",
vendors.id as "Vend Id", vendors.name as "Dscr", vendors.addr_name as 
"Name"
from vendors, jobs where vendors.guid = jobs.owner_guid
and vendors.guid in
(select distinct owner_guid from jobs where active !=0
  and owner_type=4
)
and jobs.active !=0
order by jobs.id;

enjoy ...



On 05/10/2016 08:07 PM, Aaron Laws wrote:
> On Tue, May 10, 2016 at 7:01 PM, Elmar Schmeisser <eschmeisser at nc.rr.com>
> wrote:
>
>> Ah - got it!  You have to START with ctrl-click, and then continue with
>> ctgrl-click.  If you start with a simple click and the progress to
>> ctrl-click, the first click disappears.  True?
>>
> That has not been mine experience: click then CTRL+click has the expected
> outcome of selecting more than one account (assuming the second click was
> on a different account than the first). If holding CTRL from the beginning
> works for you, though, I heartily recommend it. I'm using GNU/Linux and the
> X window system.
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.