>>>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.
It should have worked great by chance. So this should work the same way in any version :
>
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,a.id_no, a.name, b.account, b.name,;
a.ap_status,a.ap_code,c.id_no,c.id_desc ;
into cursor sql_ap readwrite
Cetin