>>Hello,
>>
>>I have tried every suggestion that has been given to me regarding the whole "Group By" subject. However, I still do not seem to be accomplishing what VFP 6/7 was able to accomplish in VFP 8. I am beginning to wonder if it does work with this new SQL statement type. Everytime I attempt this "Group By" in any sense of my code, it still gives me the information seperately inside the grid. I attempted to re-write the code over and over again, but every version comes out the same or worse. I attempted the MAX() code into the fields, but destroyed my output data into figures that did not match up or correct figures that were placed multiple times.
>>
>>
>>select id_desc,sum_ap_total ;
>>from sql_ap_pre ;
>>where ap_status = .pcStatus ;
>>group by id_desc, sum_ap_total ;
>>into cursor sql_ap readwrite
>>
>>
>>The id_desc and totals are shown as:
>>
>>AP-MISC 500.00
>>AP-OFFICESUPPLIES 500.00
>>AP-OFFICESUPPLIES 500.00
>>AP-OFFICESUPPLIES 2000.00
>>AP-COMPUTERS 5000.00
>>AP-COMPUTERS 3000.00
>>AP-COMPUTERS 7000.00
>>AP-COMPUTERS 1500.00
>>AP-OFFICESUPPLIES 50.00
>>AP-MISC 1000.00
>>
>>
>>where as I am trying to accomplish:
>>
>>AP-COMPUTERS 11500.00
>>AP-OFFICESUPPLIES 3050.00
>>AP-MISC 1500.00
>>
>>
>>I even narrowed this code down thinking that it was because I have more fields originally, but even when I now have only two fields, it does not come out right. Does anyone please have a solution for this in VFP 8 aside from using the 7 Engine to calculate correctly??
>>
>>Thank you so much!!
>
>This works in 6/7 ? What's the logic behind grouping with an SQL like this ? I haven't seen your previous problem so there might be a logic in fact which I'm missing. Probably you meant to :
>
>select distinct id_desc,sum_ap_total ;
>from sql_ap_pre ;
>where ap_status = .pcStatus ;
>into cursor sql_ap readwrite
>
>I don't see the point to use group by instead. Or you mean this ?
>
>select id_desc,sum(sum_ap_total) as sum_ap_total ;
>from sql_ap_pre ;
>where ap_status = .pcStatus ;
>group by id_desc ;
>order by id_desc ;
>into cursor sql_ap readwrite
>
>Cetin
Yes, this process work in VFP 6/7, but the Select-SQL statement was straightforward when the code worked with it. This was the original code that worked:
select a.id_no, a.name, b.account, b.name, sum(a.inv_total), a.ap_status,a.ap_code,c.id_no,c.id_desc ;
from vendors_inv a, vendor_accounts b,vendor_codes c;
where inv_status=lcoverduestatus and a.id_no=gnclientid and !empty(ap_code) and a.name=b.account and a.ap_code==c.id_no;
group by a.inv_code ;
into cursor sql_ap readwrite
This worked great! The system calculated the inv_total for each code and summed them together. Then in the end it grouped it by the code into one line item. Thus producing the results wished above.