Subject Re: sql code not working From Mervyn Bick <invalid@invalid.invalid> Date Sat, 16 Jun 2018 11:57:32 +0200 Newsgroups dbase.getting-started On 2018-06-16 9:50 AM, Mervyn Bick wrote: > On 2018-06-15 11:40 PM, Charlie wrote: >> OK I have figured this out to a certain extent but it doesn't really >> work in this circumstance. >> >> I have a grid with the names of the vendors and another grid with the >> data.  The data is different for different vendors.  I used a >> masterfield property to connect the two.  But since you can't index >> the virtual rowset I can't create an index using dbase to connect the >> two again.  Hard to explain, but I'm not sure this is going to work.... > > Ah.  In an earlier post you said no grids but I now see you meant no > grid for the totals. > > How does, instead of having the totals in entryfields, having the totals > in the vendors' grid next to their other details sound?  As you scroll > down the grid the data for each vendor's transactions will still appear > in the second grid as at present. I hadn't had my second cup of coffee when I wrote that. :-) Use this SQL statement in the query for the first grid.  This will show the vendor name and the totals in the first grid.    sql = 'select v.lname,sum(t.qty) as cnt, sum(t.qty*t.cost) as tcost, sum(t.qty*t.sell) as tsell from vendors v inner join taldata t on v.lname = t.lname group by v.lname  order by v.lname' If you want more fields from the vendors table in the grid add them between  v.lname, and sum(t.qty) with a comma between each.  You will also need to add each field to the group by clause before the order by clause.  You need a comma between each fieldname but not after the last fieldname. If the vendors file is not named vendors.dbf change vendors in the SQL statement to suit.  You can leave the v (which is used as an alias) as it is. If lname is the masterfield used to link the query for the data the data query feeding the second grid should work without change. If you want to show the grand totals add a new query with this as the QL statement.    sql = 'select sum(qty) as cnt, sum(qty*cost) as tcost, sum(qty*sell) as tsell from taldata This will give you a rowset with a single record with fields cnt,tcost and tsell.  You can then data link entryfields to display these values. If you only want records for a specific time period add this to the on clause for the first query and add a where clause to the grand total query.    ....on v.lname = t.lname and t.datefield > '01/01/2018' group by ... or    ....on v.lname = t.lname and t.datefield between '01/01/2018' and '06/30/2018' group by .....    ... from taldata where datefield > '01/01/2018 from taldata or    ... from taldata where datefield between '01/01/2018 and '06/30/2018' from taldata Mervyn.